[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 Go to 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

[ScottGem]

First, its VBA code not VB. Visual Basic For Applications is a superset of the Visual Basic language that contains commands and extensions to control compliant applications.

The main problem with your code is that you are creating a SQL statement and assigning it to a string variable. You then try to assign that string variable to an Integer variable. You seem to think you are assigning the results of the SQL stmt. But you cannot directly assign the results of a SQL statement to a variable.

Now I'm not at all clear why you are doing all this. You have the user enter a Receipt No, I assume that's the TransNumID, either way it uniquely identifies the transaction. When they do, it brings up the details of the transaction. One of those details should be a Boolean field that indicates if the transaction has been voided or not. So all the user needs to do is check off that control. No code is necessary at all.

In fact, I would use a Combo to enter the Receipt No and filter that combo so voided or refunded receipts don't show.

By the way. If you want to access the results of a query use the Domain Aggregate functions like Dlookup, Dsum, Dcount, etc..

[mrsgwen90]

OK I'm fairly new to programming so I'm not sure how to do what you are suggesting. The bottom line is all I am trying to do is to have some kind of way to let the user know that the receipt has already been voided or refunded.

[ScottGem]

Like I said you don't need ANY programming. You say they enter the Receipt number and it brings up the transaction. Does that part work? If so, aren't there fields in the record that you check to indicate if its Void or Refund?

Then all you need is to display the value in that field. If the user sees its already checked, then they know its been done. Do you need anymore than that? If so, why?

[mrsgwen90]

When the record comes up it already has a payment type of Regular. None of those fields can be edited. Once they user click "Void Ticket" it will then write "Void" to the table. I'm trying to keep the users from voiding the same receipt. And the report needs to show all the voids, regulars and refunds.

[ScottGem]

Ok, you need to explain HOW a transaction is marked as Regular, Void or Refund.

[mrsgwen90]

OK, on form where the initial transaction is done. There is a field called "payment type" and it has "Regular" in the text boxed prefilled. Because all of the transactions is a just a regular transaction. Now if the user decideds this transaction needs to be voided or refunded, I have created other forms to handle this. On the form called Refunds, that form is opened, a Query box comes up and ask for the receipt no. that they want to refund. Once the user enters the number, that particular transaction comes up. Then there is 2 command buttons, one that says "Refund Ticket Sale" and the other says "Close".
When the user hits the "Refund Ticket Sale" button I need it to be able to tell them if that particular receipt no has already been refunded.
I hope all of that makes sense.

[ScottGem]

Nope, because you didn't answer my question. Is the Payment Type field set to Refund or Void or something else? Or is there another field that indicates a refund or void?

[mrsgwen90]

Behind the scenes in the code. The payment type is hard coded to "Void" or "Refund" depending on what form they are using.

[ScottGem]

Ok, so you have a field named Payment Type. That field is set to either Regular, Void or Refund. By default, its set to Regular. Depending on the form they use, your code sets it to either Void or Refund. Is that correct?

So when the record is retrieved, the control bound to that field displays one of those three values, correct? So why would a user try to refund or void a transaction ALREADY marked as such.

However, if you want to really idiot-proof this, then the code is simple:

If Me.txtPaymentType = "Refund" Then
MsgBox "This transaction has already been refunded", vbOKOnly
Else
Me.txtPaymentType = "Refund"
End If