VOID Command Button Event (1 Viewer)

[mba_110]

Hi everyone there,

I am coding the cmd button to void the entry with its related transactions in tbltransactions, below is the code.

Private Sub cmdVoidEntry_Click()
Dim VBAnsw As String
      
      If Me.cboEntryNo > 0 Then
'If combo box is not empty then
       VBAnsw = MsgBox("Do you really want to VOID the Entry?" & vbCrLf & vbCrLf _
            & "This changes will be Non-Reversible !!!", vbYesNo, "Warning")
'If user's answer is Yes then update the void check box and make debit and credit values zero or null
'for related transactions in tbltransactions where Entry no is equal to cboEntryNo
        If VBAnsw = vbYes Then
            Dim TransactionID As Integer
            Dim Debit As Integer
            Dim Credit As Integer
            
            TransactionID = DLookup("[EntryNo]", "tblTransactions", "[EntryNo]=" & Me.cboEntryNo & "")
            Debit = DLookup("[CurDebit]", "tblTransactions", "[EntryNo]=" & Me.cboEntryNo & "")
            Credit = DLookup("[CurCredit]", "tblTransactions", "[EntryNo]=" & Me.cboEntryNo & "")
            
            CurrentDb.Execute "UPDATE tblTransactions Set Void = True WHERE cboEntryNo = " & TransactionID And Debit.Value = Null And Credit.Value = Null
'Here need code to add text to related transactions memo field to "VOID" in the begining of memo line.
'If there is already text in memo field if not then this will be the first sentance "VOID".
            CurrentDb.Execute "UPDATE tbltransactions Set Memo = ([Memo]& "VOID" WHERE cboEntryNo = " & TransactionID

        Else
            Me.Undo
            
        End If
    End If
End Sub

There is error in last two lines of code, also please mention the procedure is right?

 

[theDBguy]

What does the error message say?

 

[MajP]

as a start you need to put some "" around the second part

"UPDATE tblTransactions Set Void = True WHERE cboEntryNo = " & TransactionID And Debit.Value = Null And Credit.Value = Null

to

"UPDATE tblTransactions Set Void = True WHERE cboEntryNo = " & TransactionID & [B]" [/B]And Debit.Value = Null And Credit.Value = Null[B]"[/B]

Make sure you have option explicit at top of all code modules

 

[Minty]

Memo is a reserved word.
VBAnsw will be an integer returned from the message box, not a string.

Instead of trying to directly execute your query strings, create them into a string variable debug.print it and then you will see your problem.

Dim sSQL as string

sSQL = "UPDATE tblTransactions Set Void = True WHERE cboEntryNo = " & TransactionID & " And Debit = Null And Credit = Null "
Debug.Pring sSQL

Your query doesn't make much at this point. Why not create the Update query in the query editor switch to SQL view and see how to form it?

 

[mba_110]

I don't know what is wrong here, all sql is correct but it still giving the error for

Run time error 3061
too few parameters expected 1.

Private Sub cmdVoidEntry_Click()
Dim VBAnsw As String
      
      If Me.cboEntryNo > 0 Then

       VBAnsw = MsgBox("Do you really want to VOID the Entry?" & vbCrLf & vbCrLf _
            & "This changes will be Non-Reversible !!!", vbYesNo, "Warning")

        If VBAnsw = vbYes Then

Dim void As Boolean
Dim curDebit As Integer
Dim curcredit As Integer
Dim TransactionMemo As String

CurrentDb.Execute " UPDATE tblTransactions SET tblTransactions.Void = True, tblTransactions.CurDebit = 0, tblTransactions.CurCredit = 0, tblTransactions.TransactionMemo = ""VOID"" " & _
" WHERE (((tblTransactions.EntryNo)=[Forms]![frmCashBankEntry]![cboEntryNo]));"

        Else
            Me.Undo
            
        End If
    End If
End Sub

 

[Dreamweaver]

If you are trying to unpost a GL transaction you should reverse what you did to post the original amounts that way the system balances


I.E the bebit amount becomes the credit and credit becomes the debit in a new entry using the same deatails.

 

[mba_110]

Un post and VOID these are two completely different things in accounting.

here i am concentrating on VOID only, when i do post button that time i will look for un-post.

 

[Dreamweaver]

I don't need to deal with VOID transaction in my system as all the funds have gone though my bank

https://www.investopedia.com/terms/v/void-transaction.asp

 

[Minty]

I don't know what is wrong here, all sql is correct but it still giving the error for

Run time error 3061
too few parameters expected 1.

Private Sub cmdVoidEntry_Click()
Dim VBAnsw As String
      
      If Me.cboEntryNo > 0 Then

       VBAnsw = MsgBox("Do you really want to VOID the Entry?" & vbCrLf & vbCrLf _
            & "This changes will be Non-Reversible !!!", vbYesNo, "Warning")

        If VBAnsw = vbYes Then

Dim void As Boolean
Dim curDebit As Integer
Dim curcredit As Integer
Dim TransactionMemo As String

CurrentDb.Execute " UPDATE tblTransactions SET tblTransactions.Void = True, tblTransactions.CurDebit = 0, tblTransactions.CurCredit = 0, tblTransactions.TransactionMemo = [COLOR="Red"]'VOID' [/COLOR]" & _
" WHERE (((tblTransactions.EntryNo)=[Forms]![frmCashBankEntry]![cboEntryNo]));"

        Else
            Me.Undo
            
        End If
    End If
End Sub

And the result of your debug was ?

 

[mba_110]

in immediate window of command button it is showing the value of combo box, what is error message i already mentioned to you.

 

[Minty]

Nope, I meant if you had done as I suggested and created your SQL string into a variable and debug.printed that.

sSql = " UPDATE tblTransactions SET tblTransactions.Void = True, tblTransactions.CurDebit = 0, tblTransactions.CurCredit = 0, tblTransactions.TransactionMemo = 'VOID' " & _
" WHERE (((tblTransactions.EntryNo)=[Forms]![frmCashBankEntry]![cboEntryNo]));"

Debug.print sSql

CurrentDb.Execute  sSql

 

[June7]

Cannot compare anything to Null. Cannot do: Debit = Null. Use IsNull() or IS NULL.

Why not do both updates in the same action??

CurrentDb.Execute "UPDATE tblTransactions Set Void = True, [Memo]= [Memo] & 'VOID' WHERE TransactionID=" & Me.cboEntryNo & " AND Debit IS NULL AND Credit IS Null"

And now I see field names and code have been revised. So try:

CurrentDb.Execute "UPDATE tblTransactions SET Void = True, CurDebit = 0, CurCredit = 0, TransactionMemo = 'VOID' & [TransactionMemo]" & _
" WHERE EntryNo=" & [Forms]![frmCashBankEntry]![cboEntryNo]

Are you sure you are "VOIDING" a record already committed to table?

 

[mba_110]

Thanks June7 it is working like a bullet.