SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: Fredrick Ughimi on April 28, 2019, 05:15:54 pm

Title: Tricky Save Routine
Post by: Fredrick Ughimi on April 28, 2019, 05:15:54 pm
Hello,

I have a tricky situation trying to save values from a textbox and MLG on a FORM into a table at the same time.

On the FORM we have 3 values from Textboxes and 4 values from the MLG. On MLG there are two or three
rows to be saved at a time.

Has anyone done this? Any pointer would be appreciated.
Title: Re: Tricky Save Routine
Post by: Fredrick Ughimi on April 28, 2019, 10:26:24 pm
This saves but giving undesirable results. Three rows instead of two. Repeating the second row:

rows = 2
cols  =7
 
RowStart=1    'select first row from mlg
RowEnd=2     'select last  row from mlg
ColStart=1    'need entire record
ColEnd=cols
ReDim FromGrid(RowStart To RowEnd,ColStart To ColEnd) As String 'dim array
MLG_GETEx hJournalGrid,FromGrid()                                      'fill array
For r = RowStart To RowEnd
    For c = colStart To colEnd
        sb.Add slbuildbindDat(FromGrid(r,c),"T") 'add bind string into stringbuilder
    Next
Next
slExeBind slBuildInsertOrUpdate("tblJournalEntry", "?" & $Nul & "?" & $Nul & "?" & $Nul & "?" & $Nul & _
"?" & $Nul & "?" & $Nul & "?"), _
slBuildBindDat(SQLiteDate(m_sDate), "T") & _
slBuildBindDat(m_sReference, "T") & _ 
slBuildBindDat(m_sNarration, "T") & _
sb.String,"V" + Format$(cols)
? "Record Saved!", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title

I might have to break table into two.

Title: Re: Tricky Save Routine
Post by: Fredrick Ughimi on April 30, 2019, 06:22:57 pm
Hello CJ,

Thanks for the heads up and great code as always. Since you did not use MLG and Textbox in your codes, it is a bit difficult to follow and adapt.

Regards.

Title: Re: Tricky Save Routine
Post by: Fredrick Ughimi on May 01, 2019, 10:39:07 am
Thanks CJ. The thing is the values entered in the textboxes (Date, Narration, Reference) is usually a record, while the values entered on the grid (Account, Description, Credit, Debit) are usually two records or more per transaction. That is doing the double entry in Journal Entry of Accounting.

I would post a picture of my form in my next post whenever electricity comes on.

Best regards.
Title: Re: Tricky Save Routine
Post by: Fredrick Ughimi on May 01, 2019, 05:25:14 pm
Hello Cj,

Attached JournalEntry.jpgis my form.
Title: Re: Tricky Save Routine
Post by: Fredrick Ughimi on May 02, 2019, 05:37:25 am
Hello Cj,

QuoteI don't know what column/row from the grid to put into the table columns.

All columns of the grid and all rows containing records because the rows could contain up to 3 records sometimes, but most of the times 2 records.

Kind regards.

Title: Re: Tricky Save Routine
Post by: Fredrick Ughimi on May 02, 2019, 09:53:55 am
Thank you, Cj.

Your last post gave me an idea that works. Using the same table twice. Not the most elegant code, but it works.
It does what I want.

m_sDate = VD_GetText (nCbHndl, %ID_FRMJOURNALENTRY_DPKDATE)
             
m_sReference = VD_GetText (nCbHndl, %ID_FRMJOURNALENTRY_TXTREFERENCE)
If Trim$(m_sReference) = "" Then
      MsgBox "Please, enter Reference", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title
      Control Set Focus nCbHndl, %ID_FRMJOURNALENTRY_TXTREFERENCE
      Exit Method
End If
               
m_sNarration = VD_GetText (nCbHndl, %ID_FRMJOURNALENTRY_TXTNARRATION)
If Trim$(m_sNarration) = "" Then
      MsgBox "Please, enter Narration", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title
      Control Set Focus nCbHndl, %ID_FRMJOURNALENTRY_TXTNARRATION
      Exit Method
End If
             
m_sAccount1 = MLG_Get(hJournalGrid, 1, 1)
m_sDescription1 = MLG_Get(hJournalGrid, 1, 2)
m_sDebit1 = MLG_Get(hJournalGrid, 1, 3)
m_sCredit1 = MLG_Get(hJournalGrid, 1, 4)   
             
m_sAccount2 = MLG_Get(hJournalGrid, 2, 1)
m_sDescription2 = MLG_Get(hJournalGrid, 2, 2)
m_sDebit2 = MLG_Get(hJournalGrid, 2, 3)
m_sCredit2 = MLG_Get(hJournalGrid, 2, 4)
             
slExe "Begin"
   slExe slBuildInsertOrUpdate("tblJournalEntry", SQLiteDate(m_sDate) & $Nul & m_sReference _
   & $Nul & m_sNarration & $Nul & m_sAccount1 & $Nul & m_sDescription1 & $Nul & m_sDebit1 _
   & $Nul & m_sCredit1)
slExe "End"
       
slExe "Begin"
   slExe slBuildInsertOrUpdate("tblJournalEntry", SQLiteDate(m_sDate) & $Nul & m_sReference _
   & $Nul & m_sNarration & $Nul & m_sAccount2 & $Nul & m_sDescription2 & $Nul & m_sDebit2 _
   & $Nul & m_sCredit2)
slExe "End"

MLG_PUT hJournalGrid,6,3,TotalCredit(hJournalGrid),0
MLG_PUT hJournalGrid,6,4,TotalDebit(hJournalGrid),0

? "Record Saved!", %MB_TASKMODAL Or %MB_ICONINFORMATION, VD_App.Title
Title: Re: Tricky Save Routine
Post by: cj on May 21, 2019, 02:34:48 pm
Quote from: undefinedslExe "Begin"
  slExe slBuildInsertOrUpdate("tblJournalEntry", SQLiteDate(m_sDate) & $Nul & m_sReference _
  & $Nul & m_sNarration & $Nul & m_sAccount1 & $Nul & m_sDescription1 & $Nul & m_sDebit1 _
  & $Nul & m_sCredit1)
slExe "End"
     
slExe "Begin"
  slExe slBuildInsertOrUpdate("tblJournalEntry", SQLiteDate(m_sDate) & $Nul & m_sReference _
  & $Nul & m_sNarration & $Nul & m_sAccount2 & $Nul & m_sDescription2 & $Nul & m_sDebit2 _
  & $Nul & m_sCredit2)
slExe "End"
slExe "Begin Exclusive"
 slExe slBuildInsertOrUpdate("tblJournalEntry",(CHR$(SQLiteDate(Date),0,Ref,0,Narr,0,Acct1,0,Desc1,0,Debit1,0,Credit1)))
 slExe slBuildInsertOrUpdate("tblJournalEntry",(CHR$(SQLiteDate(Date),0,Ref,0,Narr,0,Acct2,0,Desc2,0,Debit2,0,Credit2)))
slexe "End"