How can I use Select Case Statements with multiple variables?

[Daryl Austin]

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.

'*************************
'General declarations section
Dim Model As String
Option Compare Database
'***************************
Private Sub Check202_Click()
Dim Cost1 As Variant
If Check202 = 0 Then
    Me.R610 = Null
Else
    Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW068'")
    Me.R610 = Cost1
    Model = R610
End If
End Sub
 
Private Sub Check204_Click()
Dim Cost1 As Variant
If Check204 = 0 Then
    Me.R630 = Null
Else
    Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW001'")
    Me.R630 = Cost1
    Model = R630
End If
End Sub
 
Private Sub Check206_Click()
Dim Cost1 As Variant
If Check206 = 0 Then
    Me.R670 = Null
Else
    Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW198'")
    Me.R670 = Cost1
    Model = R670
End If
End Sub
 
Private Sub Check208_Click()
Dim Cost1 As Variant
If Check208 = 0 Then
    Me.R690 = Null
Else
    Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW265'")
    Me.R690 = Cost1
    Model = R690
End If
End Sub
 
Private Sub Check210_Click()
Dim Cost1 As Variant
If Check210 = 0 Then
    Me.R620 = Null
Else
    Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW130'")
    Me.R620 = Cost1
    Model = R620
End If
End Sub
 
 
'**************************THIS IS WHERE MY PROBLEM IS********************//
Private Sub Check227_Click()
Dim Cost1 As Variant
Dim Item As Variant
'check227 is shallow pit design
If Check227 = 0 Then
    Item = 0
Else
    Select Case Model
        Case R610
            Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW072'")
            Item = Cost1
            Me.shallowpitR610 = Cost1
        Case R620
            Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW135'")
            Item = Cost1
            Me.shallowpitR620 = Cost1
        Case R630
            Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW006'")
            Item = Cost1
            Me.shallowpitR630 = Cost1
        Case R670
            Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW202'")
            Item = Cost1
            Me.shallowpitR670 = Cost1
        Case R690
            Cost1 = DLookup("itemcost", "qryCostIndexR600", "[itemcode] = 'RW270'")
            Item = Cost1
            Me.shallowpitR690 = Cost1
    End Select
End If
End Sub

[Seth Schrock]

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.

[Daryl Austin]

Seth
It hightlights the Case Statement. Specifically Case R620.

[Killer42]

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

Public Constant ModelNotSpecified As String = "Who care, you can put pretty much any value here"

and then use ModelNotSpecified in place of Null.

[Killer42]

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?

[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?

[NeoPa]

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 :

Select Case True
Case Me.R610
  ...
Case Me.R620
  ...
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.

[Daryl Austin]

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.

[Daryl Austin]

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.

[NeoPa]

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 :-)

[Killer42]

I doubt there's a programmer alive who doesn't find their earlier work embarrassing.

I think it's called learning. :-)