Hi Everyone,
I am writing cmd button event for delete in my data entry form before deleting the records i am storing the information in delete table and updating it with delete date in order to do that i have divided the sql in three portions (INSERT INTO, DELETE, UPDATE).
Please review the below code, I think i am missing some delimiters in the code which generate the errors of syntax & Too few parameters.
Many thanks in advance for your help and cooperation.
I am writing cmd button event for delete in my data entry form before deleting the records i am storing the information in delete table and updating it with delete date in order to do that i have divided the sql in three portions (INSERT INTO, DELETE, UPDATE).
Please review the below code, I think i am missing some delimiters in the code which generate the errors of syntax & Too few parameters.
Code:
Private Sub CmdDeleteEntry_Click()
Dim VBAnsw As String
If Me.cboEntryNo > 0 Then
VBAnsw = MsgBox("You are about to delete the entry and its underlined transactions" & vbCrLf _
& "If you click yes, you won't be able to undo this delete operation. " & vbCrLf & vbCrLf _
& "Are you sure you want to delete this records?", vbYesNo, "Warning")
If VBAnsw = vbYes Then
'Append the Entry details before deleting.
CurrentDb.Execute " INSERT INTO tblEntries_Deleted EntryNo, EntryDate, EntryType, EntryHeading" & _
" SELECT tblEntries.EntryNo, tblEntries.EntryDate, tblEntries.EntryType, tblEntries.EntryHeading" & _
" FROM tblEntries " & _
" WHERE EntryNo=" & [Forms]![frmCashBankEntry]![cboEntryNo]
'Append the transactions underlined the entry
CurrentDb.Execute " INSERT INTO tblTransactions_Deleted TransactionID, TransactionDate, EntryNo, TransactionType, AccountNo, TransactionMemo, Category, [Currency], CurDebit, CurCredit, ExchangeRate, Posted, PostingDate, PostedBy, Void, VoidBy, VoidDate " & _
" SELECT tblTransactions.TransactionID, tblTransactions.TransactionDate, tblTransactions.EntryNo, tblTransactions.TransactionType, tblTransactions.AccountNo, tblTransactions.TransactionMemo, tblTransactions.Category, tblTransactions.Currency, tblTransactions.CurDebit, tblTransactions.CurCredit, tblTransactions.ExchangeRate, tblTransactions.Posted, tblTransactions.PostingDate, tblTransactions.PostedBy, tblTransactions.Void, tblTransactions.VoidBy, tblTransactions.VoidDate " & _
" FROM tblTransactions " & _
" WHERE EntryNo =" & [Forms]![frmCashBankEntry]![cboEntryNo]
'Delete Entry from tblEntries (Main table)
CurrentDb.Execute " DELETE tblEntries.EntryNo, tblEntries.EntryDate, tblEntries.EntryType, tblEntries.EntryHeading " & _
" FROM tblEntries " & _
" WHERE EntryNo= " & [Forms]![frmCashBankEntry]![cboEntryNo]
'Delete Transactions from tblTransactions which is associated with above entry number
CurrentDb.Execute " DELETE tblTransactions.TransactionID, tblTransactions.TransactionDate, tblTransactions.EntryNo, tblTransactions.TransactionType, tblTransactions.AccountNo, tblTransactions.TransactionMemo, tblTransactions.Category, tblTransactions.Currency, tblTransactions.CurDebit, tblTransactions.CurCredit, tblTransactions.ExchangeRate, tblTransactions.Posted, tblTransactions.PostingDate, tblTransactions.PostedBy, tblTransactions.Void, tblTransactions.VoidBy, tblTransactions.VoidDate " & _
" FROM tblTransactions " & _
" WHERE EntryNo =" & [Forms]![frmCashBankEntry]![cboEntryNo]
'Update the deleted tables with dates & user
CurrentDb.Execute " UPDATE tblTransactions_Deleted LEFT JOIN tblEntries_Deleted ON tblTransactions_Deleted.EntryNo = tblEntries_Deleted.EntryNo SET tblTransactions_Deleted.DateDeleted = Now(), tblEntries_Deleted.DateDeleted = Now() " & _
" WHERE EntryNo =" & [Forms]![frmCashBankEntry]![cboEntryNo]
End If
End If
Debug.Print VBAnsw
End Sub
Many thanks in advance for your help and cooperation.