Invalid use of Null. Please help

[Mansoor Ahmad]

Can sombody tell me where I am doing wrong in the following code.

Private Sub viewresults2(rptPrint As String)

Dim stWhere As String
Dim myInt As String
Dim myInd As String
myInd = CbCustomer
Select Case Me!frmRelativequeries1
Case 1
myInt = Forms![New form Sample]!Cbpartno
stWhere = "[CUSTOMER] = '" & myInd & "' And [PART NO] = '" & myInt & "'"
Case 2
myInt = Forms![New form Sample]!Cbrejcode
stWhere = "[CUSTOMER] = '" & myInd & "' And [REJ CODE] = '" & myInt & "'"
Case 3
myInt = Forms![New form Sample]!Cbliabcat
stWhere = "[CUSTOMER] = '" & myInd & "' And [CAT] = '" & myInt & "'"
Case 4
myInt = Forms![New form Sample]!Cbcustomerdefcode
stWhere = "[CUSTOMER] = '" & myInd & "' And [DEFECT CODE/COMPLAINT CODE] = '" & myInt & "'"
Case Else
stWhere = ""
End Select
DoCmd.OpenForm (rptPrint), acNormal, , stWhere
End Sub

If the customer combobox is left blank. It prompts as

'Invalid use of Null'. It works fine if I put some data in customer combobox.

Thanks

 

[GumbyD]

Your code needs to check to see if there is value in that combo box because you are using in all of the cases except the last one. When it hits the cases calling for a value from that field it wants a value not a null. If you want the customer field to be selected before the case statement is analysed then add an if statement that checks to make sure it is not null

Dim stWhere As String
Dim myInt As String
Dim myInd As String

myInd = CbCustomer

if not isnull([Customer] then
Select Case Me!frmRelativequeries1
Case 1
myInt = Forms![New form Sample]!Cbpartno ...

Else
msgbox "Please select a customer to proceed", vbokonly
End if

If you can leave the customer field blank than you need to rethink your case statement.

GumbyD

 

[Mansoor Ahmad]

GumbyD

Thank you very much for your reply. This works exactly the way I wanted.

Thanks for your help.