By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

Trying to use IF Else statements on an Access form with VB code

P: 37
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 the transaction as well as Refund the 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdRefund_Click()
  2. On Error GoTo Err_cmdRefund_Click
  3. 'this is a AddRec button, caption was changed to read Void
  4.  
  5. Dim Answer As Integer
  6. Dim Result
  7. Dim sqlstmt As String
  8.  
  9. sqlstmt = "Select Count( * ) from tbl_transactions where PaymentType = Void And VoidRefundID = Me.TransNumID"
  10.  
  11. Answer = sqlstmt
  12.  
  13.  If Answer > 0 Then
  14.             MsgBox "This Receipt No. has already been voided."
  15.             Exit Sub
  16.         Else
  17.  
  18.     Result = MsgBox("Are you sure you want to Refund Receipt No " + Str$(Me.TransNumID) + "?", vbYesNo, "Refund Receipt")
  19.  
  20.   If Result = VbMsgBoxResult.vbNo Then
  21.     DoCmd.Close
  22.     Exit Sub
  23.     End If
  24.  End If
Oct 18 '07 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,366
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 the transaction as well as Refund the 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdRefund_Click()
  2. On Error GoTo Err_cmdRefund_Click
  3. 'this is a AddRec button, caption was changed to read Void
  4.  
  5. Dim Answer As Integer
  6. Dim Result
  7. Dim sqlstmt As String
  8.  
  9. sqlstmt = "Select Count( * ) from tbl_transactions where PaymentType = Void And VoidRefundID = Me.TransNumID"
  10.  
  11. Answer = sqlstmt
  12.  
  13.  If Answer > 0 Then
  14.             MsgBox "This Receipt No. has already been voided."
  15.             Exit Sub
  16.         Else
  17.  
  18.     Result = MsgBox("Are you sure you want to Refund Receipt No " + Str$(Me.TransNumID) + "?", vbYesNo, "Refund Receipt")
  19.  
  20.   If Result = VbMsgBoxResult.vbNo Then
  21.     DoCmd.Close
  22.     Exit Sub
  23.     End If
  24.  End If
Just setting a variable to a SQL string doesn't do anything. You have to open and set a recordset.

String values must be delimited with single quotes.

The Me reference is out of scope of the SQL engine.
Oct 18 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.