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

How can I use Select Case Statements with multiple variables?

P: 24
I need some help in understanding why I keep getting an error message called "invalid use of Null" when I run this code.

Brief summary: I have 5 check boxes for 5 different machine models (R610,R620, R630,R670, R690). The checkboxes serve a different purpose in the subroutines below but once selected I want to assign a Module level variable call "Model" to them respectively.

I have another checkbox {check227, see below] that designated whether the configuration is a shallow pit design. Each particular model has a different cost associated with shallow pit so I wanted to use a Select Case statement based upon the Model. If the checkbox is not checked then I just want to End the subroutine or do nothing. If it is checked, then based upon the Model do a Select Case.

I am really stretching my primitive programming skills here, but I'm pretty sure Select Case is faster than my typing out dozens of If-Then-Else statements.

Can anyone offer some advice? Thanks.


Expand|Select|Wrap|Line Numbers
  1. '*************************
  2. 'General declarations section
  3. Dim Model As String
  4. Option Compare Database
  5. '***************************
  6. Private Sub Check202_Click()
  7. Dim Cost1 As Variant
  8. If Check202 = 0 Then
  9.     Me.R610 = Null
  10. Else
  11.     Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW068'")
  12.     Me.R610 = Cost1
  13.     Model = R610
  14. End If
  15. End Sub
  16.  
  17. Private Sub Check204_Click()
  18. Dim Cost1 As Variant
  19. If Check204 = 0 Then
  20.     Me.R630 = Null
  21. Else
  22.     Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW001'")
  23.     Me.R630 = Cost1
  24.     Model = R630
  25. End If
  26. End Sub
  27.  
  28. Private Sub Check206_Click()
  29. Dim Cost1 As Variant
  30. If Check206 = 0 Then
  31.     Me.R670 = Null
  32. Else
  33.     Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW198'")
  34.     Me.R670 = Cost1
  35.     Model = R670
  36. End If
  37. End Sub
  38.  
  39. Private Sub Check208_Click()
  40. Dim Cost1 As Variant
  41. If Check208 = 0 Then
  42.     Me.R690 = Null
  43. Else
  44.     Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW265'")
  45.     Me.R690 = Cost1
  46.     Model = R690
  47. End If
  48. End Sub
  49.  
  50. Private Sub Check210_Click()
  51. Dim Cost1 As Variant
  52. If Check210 = 0 Then
  53.     Me.R620 = Null
  54. Else
  55.     Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW130'")
  56.     Me.R620 = Cost1
  57.     Model = R620
  58. End If
  59. End Sub
  60.  
  61.  
  62. '**************************THIS IS WHERE MY PROBLEM IS********************//
  63. Private Sub Check227_Click()
  64. Dim Cost1 As Variant
  65. Dim Item As Variant
  66. 'check227 is shallow pit design
  67. If Check227 = 0 Then
  68.     Item = 0
  69. Else
  70.     Select Case Model
  71.         Case R610
  72.             Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW072'")
  73.             Item = Cost1
  74.             Me.shallowpitR610 = Cost1
  75.         Case R620
  76.             Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW135'")
  77.             Item = Cost1
  78.             Me.shallowpitR620 = Cost1
  79.         Case R630
  80.             Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW006'")
  81.             Item = Cost1
  82.             Me.shallowpitR630 = Cost1
  83.         Case R670
  84.             Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW202'")
  85.             Item = Cost1
  86.             Me.shallowpitR670 = Cost1
  87.         Case R690
  88.             Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW270'")
  89.             Item = Cost1
  90.             Me.shallowpitR690 = Cost1
  91.     End Select
  92. End If
  93. End Sub
Dec 27 '11 #1

✓ answered by Killer42

Oh! Just thought of one more thing.

Perhaps you meant to code:
Model = "R610"
rather than
Model = R610

And likewise, in the Select Case statement:
Case "R620"
rather than
Case R620
Does this sound reasonable?

Share this Question
Share on Google+
10 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
When it gives the error message, what line does it highlight?

Please use code tags when posting code. The button looks like the pound sign next to the undo button.
Dec 27 '11 #2

P: 24
Seth
It hightlights the Case Statement. Specifically Case R620.
Dec 27 '11 #3

Expert 5K+
P: 8,434
Looks as though R620 is Null, and VBA doesn't like hitting a Null there.

Could you use a different value, such as an empty string ("") or some value which simply won't appear in the data? For example you could define something like
Expand|Select|Wrap|Line Numbers
  1. Public Constant ModelNotSpecified As String = "Who care, you can put pretty much any value here"
and then use ModelNotSpecified in place of Null.
Dec 28 '11 #4

Expert 5K+
P: 8,434
P.S. I'm a bit confused about something. Sticking with a single model as an example, does R620 refer to the same thing as Me.R620? If so, I'd recommend coding it consistently one way or the other. If not, this seems a risky way of coding (how does VBA know which one you mean?).

If they are the same thing, then it looks to me as though you're looking up the cost and assigning it to R620. Wouldn't this cause the Select Case to produce a potentially incorrect result if two models ever cost the same?
Dec 28 '11 #5

Expert 5K+
P: 8,434
Oh! Just thought of one more thing.

Perhaps you meant to code:
Model = "R610"
rather than
Model = R610

And likewise, in the Select Case statement:
Case "R620"
rather than
Case R620
Does this sound reasonable?
Dec 28 '11 #6

NeoPa
Expert Mod 15k+
P: 31,709
Logically your code is all over the place. It's so far out there that it's not possible to be confident even to know what you're trying to do. Comparing a [Model] string with various CheckBoxes cannot make sense. Either you want to compare the string in [Model] with string literals (which would be of the form Case "R610" etc) or you simply want to check the value in each CheckBox control - which would be of the form :
Expand|Select|Wrap|Line Numbers
  1. Select Case True
  2. Case Me.R610
  3.   ...
  4. Case Me.R620
  5.   ...
  6. Etc
Without really knowing what you're after it's hard to know which of these approaches would suit you, but I guess one of them aught to.

PS. Please check out [code] Tags Must be Used.
Dec 28 '11 #7

P: 24
Killer42
Thanks for help. I changed the Model variable from a string to a variant and assigned it a number based upon the model selected (ie R610.. R690). I then used it in my Select Case statement and it seemed to work. I also cleaned up a few other things along the way to fix the code.
Dec 28 '11 #8

P: 24
NeoPa
Sorry my code is so bad. Understand that my day job is running Sales for a Company for the entire Eastern US. That said, I'm trying to create some better tools for managing the process and I really don't get any support from my management for what I'm trying to accomplish. Basically, I'm on my own trying to learn vba programming and access at night when I have some time. I'm getting better and I've now invested in 4 different books on this stuff. I am also sure that as I go I'll find that some of my original code could have been written much easier using different methods. Unfortunately right now I don't know what those methods are yet.
Dec 28 '11 #9

NeoPa
Expert Mod 15k+
P: 31,709
Not a problem Daryl. In truth, my comments were mainly to get you to understand that most of the difficulties involved with helping people is their own inability to express their problems clearly enough. As an inexperienced developer yourself, this just adds to your already steep learning curve I know, but regardless of your understanding of the subject, an understanding of the importance of communication in the process will get you help more quickly and directly.

If you bear that important point in mind you will find learning the subject easier, as help is always more closely at hand.

Good luck with your learning and also a little word of congratulation for taking the task on in the first place. I expect you'll come to enjoy it in time. Most of us do :-)
Dec 28 '11 #10

Expert 5K+
P: 8,434
I doubt there's a programmer alive who doesn't find their earlier work embarrassing.

I think it's called learning. :-)
Dec 29 '11 #11

Post your reply

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