trying to test an If Else statement using Access as Front End and VBA code

[mrsgwen90]

I have a form that is created in Access but uses some VB code. Here is my problem..... I have one form that the users do money transactions on. Then I have created a form that will allow them to Void a transaction as well as Refund a transaction. On the Void/Refund forms it will query the user for the Receipt No. once that information is pulled in, there is a command button that says "Void Ticket Sale" on the Refund form it says "Refund Ticket Sale". What I'm trying to do is, once the user enters the receipt no and hit that Void/Refund Ticket Sale button - if it has already been voided/refunded, a message box will come up and say "This Ticket has already been voided/refunded". If it has not, the "Void Ticket Sale" command will carry on. Here is a snippet of my code, I'm not sure what I'm doing wrong.

Private Sub cmdRefund_Click()
On Error GoTo Err_cmdRefund_Click
'this is a AddRec button, caption was changed to read Void

Dim Answer As Integer
Dim Result
Dim sqlstmt As String

sqlstmt = "Select Count( * ) from tbl_transactions where PaymentType = Void And VoidRefundID = Me.TransNumID"

Answer = sqlstmt

If Answer > 0 Then
MsgBox "This Receipt No. has already been voided."
Exit Sub
Else

Result = MsgBox("Are you sure you want to Refund Receipt No " + Str$(Me.TransNumID) + "?", vbYesNo, "Refund Receipt")

If Result = VbMsgBoxResult.vbNo Then
DoCmd.Close
Exit Sub
End If
End If

[Mark Gambo]

First of all please use the [Highlight] Tags with your code. If you are using the Text box "TransNumID" Value then you need to separate it from the string otherwise the value that you will be searching for will be "TransNumID", which I am sure isn't in your database.

sqlstmt = "Select Count( * ) " & _
       "from tbl_transactions where PaymentType = Void And " & _
       "VoidRefundID = " &  Me.TransNumID & ";"