Help with Access 2003 VBA If Else

[dprichard]

I have a client that has a field in their access form called SelectAuction. I need to do an if else on this field and was wondering if someone could see if I have this right. The first set is the original code and the second is my modified code. I need to say if SelectAuction ends in FR then make the buyer fees and seller fees 200 dollars. If not, check the process like you normally would. I am new to VBA and really appreciate any help you can give.

Original Code:

getterms
' Calculate Fees
Dim x As Variant

If Me![SaleStatus] > 1 Then
'calc Buyers fee
Me![BuyerFees] = DLookup("BuyerFees", "qryBuyerSpecialFees_SaleID")
If IsNull(Me![BuyerFees]) Then
Me![BuyerFees] = DLookup("BuyerFees", "qrybuyersfee_SaleID")
End If
'calc Sellers fee
Me![SellerFees] = DLookup("SellerFees", "QrySellerSpecialFees_SaleID")
If IsNull(Me![SellerFees]) Then
Me![SellerFees] = DLookup("SellerFees", "qrysellersfee_SaleID")
End If
'calc Automatic Transfer Fee
If Me![AutoMaticTransFeeDone] <> True Then
Me![AutoMaticTransFeeDone] = True
Dim AutoTransFee As Double
AutoTransFee = 0
AutoTransFee = Nz(DLookup("TransportFees", "qrySellerAutoTransferFee_SaleID"))

If AutoTransFee > 0 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrySellerAutoTransferFee_Append_SaleID"
DoCmd.SetWarnings True
End If
End If


End If

And here is my modified code:


getterms
' Calculate Fees
Dim x As Variant

IF Right(Me![SelectAuction], 2) = FR Then
Me![BuyerFees] = 200
Else
'calc Buyers fee
Me![BuyerFees] = DLookup("BuyerFees", "qryBuyerSpecialFees_SaleID")
If IsNull(Me![BuyerFees]) Then
Me![BuyerFees] = DLookup("BuyerFees", "qrybuyersfee_SaleID")
End If
End If

IF Right(Me![SelectAuction], 2) = FR Then
Me![SellerFees] = 200
Else
'calc Sellers fee
Me![SellerFees] = DLookup("SellerFees", "QrySellerSpecialFees_SaleID")
If IsNull(Me![SellerFees]) Then
Me![SellerFees] = DLookup("SellerFees", "qrysellersfee_SaleID")
End If
End If
'calc Automatic Transfer Fee
If Me![AutoMaticTransFeeDone] <> True Then
Me![AutoMaticTransFeeDone] = True
Dim AutoTransFee As Double
AutoTransFee = 0
AutoTransFee = Nz(DLookup("TransportFees", "qrySellerAutoTransferFee_SaleID"))

If AutoTransFee > 0 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrySellerAutoTransferFee_Append_SaleID"
DoCmd.SetWarnings True
End If
End If


End If

[CreganTur]

Is the code not working for you, or have you not tested it?

It looks right, but looks can be decieving. I'd suggest plugging it in and testing it. If it gives you an error, let us know what it is and we'll try to help.

BTW, welcome! Always great to see new faces here.

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif:

Fun Fact: when you're working with forms it's better practice to use a dot "." instead of a bang "!" for a few different reasons. Mainly using the bang will mess with intellisense- the tool that helps fill in things as you're typing. It can also cause some problems with compile checking.

This means that you could simplify Me![SellerFees] into Me.SellerFees. Try it out and you'll notice after you type "Me." and start to type in something after the dot, that intellisense will offer suggestions of existing objects and properties that can work with "Me."

[dprichard]

Thanks for the kind welcome. I saved it off and am having the client give it a try. I will let you know if it throws an error or anything.

Thanks again for the second pair of eyes.

[dprichard]

Sorry, just saw the bottom half of your post. What is the Me for? I was wondering that when I first opened the code and saw it everywhere. I am a PHP coder so I can kind of read what is going on, but still have a lot of questions.

[JimmyTheHand]

Sorry, just saw the bottom half of your post. What is the Me for? I was wondering that when I first opened the code and saw it everywhere. I am a PHP coder so I can kind of read what is going on, but still have a lot of questions.
"Me" is more or less like "This" in PHP.
It is used for short referencing of excel objects that have their own code module. E.g. userforms, worksheets, etc.
In other words, when you use "Me" on the code module of a worksheet, it refers to that particular worksheet. When you use it on the code module of a userform, it refers to the userform.

HTH

Jimmy

UPDATE:
Oh, I've just seen that we are in the Access Forum, not Excel...
What I wrote above still holds, even though in Access there are no worksheets.

[CreganTur]

Sorry, just saw the bottom half of your post. What is the Me for? I was wondering that when I first opened the code and saw it everywhere. I am a PHP coder so I can kind of read what is going on, but still have a lot of questions.

"Me." is something you're pretty much only going to see when you're working with Forms in Access. Me. is just a shorthand way of referring to the form that the code belongs to.

Me.UserName refers to some control on the Form that has the name "UserName".

Practical Example:

Build a form on your own, and populate it with 2 things: a textbox and a command button.

Name the textbox txtName (the textbox will say Unbound it in- this just means that it doesn't have a recordset defines, like a table or query. This is okay in this instance).

Now, click on the command button you created (in design view) and go to the events tab. Click on the build button (...) next to the On Click event. Select Code Window if neccessary.

Now, C&P the following code into the code window:

Dim strName As String '<<<declare variable strName as Type String

strName = Me.txtName '<<<set value of strName to be contents of textbox txtName on this form

MsgBox "Hello, " & strName & "Welcome to VBAX!"

Now, go to the form window and go to Form view. Now, enter your name in the textbox, and then click the command button. A message box should appear!

HTH:thumb

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: Beat me by one minute Jimmy

[dprichard]

Thank you all for your help. Okay, I had the client run it the first time and they got an error saying undefined variable with the FR highlighted so I put that in quotes "FR"

Then I got this

Got another compile error that said:

End IF without block IF the last End If was highlighted in black
End If
'calc Automatic Transfer Fee
If Me![AutoMaticTransFeeDone] <> True Then
Me![AutoMaticTransFeeDone] = True
Dim AutoTransFee As Double
AutoTransFee = 0
AutoTransFee = Nz(DLookup("TransportFees", "qrySellerAutoTransferFee_SaleID"))

If AutoTransFee > 0 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrySellerAutoTransferFee_Append_SaleID"
DoCmd.SetWarnings True
End If
End If


End If


Not sure what this means... Please help!!! Thank you again for your help!


So, my current code looks like this and it highlights the last end if

getterms
' Calculate Fees
Dim x As Variant

If Right(Me![SelectAuction], 2) = "FR" Then
Me![BuyerFees] = 200
Else
'calc Buyers fee
Me![BuyerFees] = DLookup("BuyerFees", "qryBuyerSpecialFees_SaleID")
If IsNull(Me![BuyerFees]) Then
Me![BuyerFees] = DLookup("BuyerFees", "qrybuyersfee_SaleID")
End If
End If

If Right(Me![SelectAuction], 2) = "FR" Then
Me![SellerFees] = 200
Else
'calc Sellers fee
Me![SellerFees] = DLookup("SellerFees", "QrySellerSpecialFees_SaleID")
If IsNull(Me![SellerFees]) Then
Me![SellerFees] = DLookup("SellerFees", "qrysellersfee_SaleID")
End If
End If
'calc Automatic Transfer Fee
If Me![AutoMaticTransFeeDone] <> True Then
Me![AutoMaticTransFeeDone] = True
Dim AutoTransFee As Double
AutoTransFee = 0
AutoTransFee = Nz(DLookup("TransportFees", "qrySellerAutoTransferFee_SaleID"))

If AutoTransFee > 0 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrySellerAutoTransferFee_Append_SaleID"
DoCmd.SetWarnings True
End If
End If


End If

[CreganTur]

It was the very last End If that did this to you- it was unneccessary. Try this:

' Calculate Fees
Dim x As Variant
Dim AutoTransFee As Double

If Right(Me.SelectAuction, 2) = "FR" Then
Me.BuyerFees = 200
Else
'calc Buyers fee
Me.BuyerFees = DLookup("BuyerFees", "qryBuyerSpecialFees_SaleID")
If IsNull(Me.BuyerFees) Then
Me.BuyerFees = DLookup("BuyerFees", "qrybuyersfee_SaleID")
End If
End If

If Right(Me.SelectAuction, 2) = "FR" Then
Me.SellerFees = 200
Else
'calc Sellers fee
Me.SellerFees = DLookup("SellerFees", "QrySellerSpecialFees_SaleID")
If IsNull(Me.SellerFees) Then
Me.SellerFees = DLookup("SellerFees", "qrysellersfee_SaleID")
End If
End If
'calc Automatic Transfer Fee
If Me.AutoMaticTransFeeDone <> True Then
Me.AutoMaticTransFeeDone = True
AutoTransFee = 0
AutoTransFee = Nz(DLookup("TransportFees", "qrySellerAutoTransferFee_SaleID"))
If AutoTransFee > 0 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrySellerAutoTransferFee_Append_SaleID"
DoCmd.SetWarnings True
End If
End If

[dprichard]

Okay, the client is telling me that it ran fine without the last end if and it works if the buyer doesn't have special fees, but if they have special fees it is overriding it and putting in the special fee. Do I need to do an else if <> or shouldn't it just skip it the way I have it now?

[dprichard]

Do I need to do something like this?


getterms
' Calculate Fees
Dim x As Variant

If Right(Me![SelectAuction], 2) = "FR" Then
Me![BuyerFees] = 200
Else If Right(Me![SelectAuction], 2) <> "FR" Then
'calc Buyers fee
Me![BuyerFees] = DLookup("BuyerFees", "qryBuyerSpecialFees_SaleID")
If IsNull(Me![BuyerFees]) Then
Me![BuyerFees] = DLookup("BuyerFees", "qrybuyersfee_SaleID")
End If
End If

If Right(Me![SelectAuction], 2) = "FR" Then
Me![SellerFees] = 200
Else If Right(Me![SelectAuction], 2) <> "FR" Then
'calc Sellers fee
Me![SellerFees] = DLookup("SellerFees", "QrySellerSpecialFees_SaleID")
If IsNull(Me![SellerFees]) Then
Me![SellerFees] = DLookup("SellerFees", "qrysellersfee_SaleID")
End If
End If
'calc Automatic Transfer Fee
If Me![AutoMaticTransFeeDone] <> True Then
Me![AutoMaticTransFeeDone] = True
Dim AutoTransFee As Double
AutoTransFee = 0
AutoTransFee = Nz(DLookup("TransportFees", "qrySellerAutoTransferFee_SaleID"))

If AutoTransFee > 0 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrySellerAutoTransferFee_Append_SaleID"
DoCmd.SetWarnings True
End If
End If

[dprichard]

Nope, that isn't working. I wonder why it isn't skipping the

Me![BuyerFees] = DLookup("BuyerFees", "qryBuyerSpecialFees_SaleID")

[CreganTur]

Nope, that isn't working. I wonder why it isn't skipping the

Me![BuyerFees] = DLookup("BuyerFees", "qryBuyerSpecialFees_SaleID")

Set a breakpoint somewhere in your code near the beginning, and then run that code again. You can step through code using F8, and while you're doing this you can check the values of all of your variables. Maybe something will pop out at you to show why it's not working.

Also, I made a couple small changes that might help.

getterms
' Calculate Fees
Dim x As Variant

If Right(Me.SelectAuction, 2) = "FR" Then
Me![BuyerFees] = 200
Else '<<< This will cover everything that doesn't meet the first If check
'calc Buyers fee
Me.BuyerFees = DLookup("BuyerFees", "qryBuyerSpecialFees_SaleID")
If IsNull(Me.BuyerFees) Then
Me.BuyerFees = DLookup("BuyerFees", "qrybuyersfee_SaleID")
End If
End If

If Right(Me.SelectAuction, 2) = "FR" Then
Me![SellerFees] = 200
Else '<<<Covers everything that doesn't meet the first If check
'calc Sellers fee
Me.SellerFees = DLookup("SellerFees", "QrySellerSpecialFees_SaleID")
If IsNull(Me.SellerFees) Then
Me.SellerFees = DLookup("SellerFees", "qrysellersfee_SaleID")
End If
End If
'calc Automatic Transfer Fee
If Me.AutoMaticTransFeeDone = False Then '<<<if it's not true, then it's false
Me.AutoMaticTransFeeDone = True
Dim AutoTransFee As Double
AutoTransFee = 0
AutoTransFee = Nz(DLookup("TransportFees", "qrySellerAutoTransferFee_SaleID"))

If AutoTransFee > 0 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrySellerAutoTransferFee_Append_SaleID"
DoCmd.SetWarnings True
End If
End If