 |
|
|
 |
New Member
|
|
Oct 18, 2007, 06:01 AM
|
|
How to test for a condition with an Access form and VB code
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
|
|
 |
Computer Expert and Renaissance Man
|
|
Oct 18, 2007, 06:50 AM
|
|
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..
|
|
 |
New Member
|
|
Oct 18, 2007, 06:57 AM
|
|
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.
|
|
 |
Computer Expert and Renaissance Man
|
|
Oct 18, 2007, 07:28 AM
|
|
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?
|
|
 |
New Member
|
|
Oct 18, 2007, 08:26 AM
|
|
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.
|
|
 |
Computer Expert and Renaissance Man
|
|
Oct 18, 2007, 08:40 AM
|
|
Ok, you need to explain HOW a transaction is marked as Regular, Void or Refund.
|
|
 |
New Member
|
|
Oct 18, 2007, 08:52 AM
|
|
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.
|
|
 |
Computer Expert and Renaissance Man
|
|
Oct 18, 2007, 08:56 AM
|
|
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?
|
|
 |
New Member
|
|
Oct 18, 2007, 09:07 AM
|
|
Behind the scenes in the code. The payment type is hard coded to "Void" or "Refund" depending on what form they are using.
|
|
 |
Computer Expert and Renaissance Man
|
|
Oct 18, 2007, 10:36 AM
|
|
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
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
Building Code for Bathtub Access Panel?
[ 5 Answers ]
Does there have to be an access panel "cover" -- the actual panel -- for the bathtub, or can that area -- pipes and all, the underneath of the bathtub, etc. -- all be left open? Is there typically a building code about that? Thank you.
Have a form in Access with some VB 6.0 code
[ 1 Answers ]
I have a form in Access. On the form I have a combo box called "cboPaymentMethod" and in the combo box there are 3 choices: cash, check, and free. I also have a text box called "CheckNum". Once the user picks "check" from the combo box they will tabl on through and when they get to the CheckNum...
View more questions
Search
|