errors when trying to open a form using case

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Private Sub Method_AfterUpdate()
I have a drop down list that lists the methods of payments, when you click
on one it should open the popup form (already coded) and all should be rosey.
Except its not, im getting the folllowing error message:

Error "94"

Invalide use of Null

this is the code that im using for the drop down list, ive used watches to
see the value of methodtype and setpped through the code so i can see that it
has the correct value but isnt doing what its supposed.

<!---

Dim methodtype As String

methodtype = Me![Method].Value



Select Case methodtype
Case Cash: DoCmd.OpenForm "Cash"
Case Credit_Card: DoCmd.OpenForm "Credit"
Case Cheque: DoCmd.OpenForm "cheque"
Case Direct_debit: DoCmd.OpenForm "DirectDebit"
Case Switch: DoCmd.OpenForm "Switch"
Case Else: MsgBox ("You must pick a Payment Method from the list")
End Select

End Sub
---!>

With regards (and much pleading for help)

Amit
 
I suspect that if the result should be Case Else, that you're passing a Null
to methodtype. Try:

methodtype = Nz(Me![Method].Value, "")

If that doesn't work, you'll need to tell us which line is giving the error.
 
Hi Wayne,

Thanks for the assistance so far, however its still not working, there are
no error messages but instead its always selecting the else from the
statement.

Ive changed the methodtype to a variant and replaced the lines, otherwise
its identicle to what it as before.
The watch statment is again showing that the variable is holding the correct
value (eg "Cheque") though its not matching against any of the case
arguments when i do a step through, infact its not running on any lines till
it gets to the end select statement and then the else message executes.

Thanks in advance again,

Amit
 
The Cases you're trying to match are strings. Try putting quotes around the
values.

Case "Cash"

Without the quotes VBA thinks that Case is a variable. It is then using the
value of the variable named Cash as the check, which doesn't match. To
prevent this sort of problem, add the line Option Explicit right below the
line at the top of each module that says Option Compare Database. To have
this as the default for all new modules, in the VBA editor go to
Tools|Options and on the Editor tab check the box that says "Require
Variable Declaration". This will require you to explicitly Dim all variables
you use and, therefore, will prevent problems such as this because the
compiler would have caught that Cash wasn't declared as a variable.
 
Thank you so much, it works and now my mind can wander to the next problem
(Union Query!). Id buy you a drink if i could but seeing as thats an
imposibility ill instead donate an hours pay ($20) to the tusnami appeal to
show my appreciation.

With kind regards

Amit Patel
 
You're welcome. Feel free to come back if you have problems with the Union
Query. I usually find it easiest to make the Union Query by making
individual queries in the design grid then copying and pasting their SQL
into the Union Query with the word(s) Union or Union All between them.
 
Back
Top