• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

Tricky Save Routine

Started by Fredrick Ughimi, April 28, 2019, 05:15:54 PM

Previous topic - Next topic

Fredrick Ughimi

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.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

#1
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.

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

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.

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

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.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Hello Cj,

Attached JournalEntry.jpgis my form.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

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.

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

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
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

#7
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"