Variable with null value

[Mansoor Ahmad]

Dear All

On my form there is an option group named specialreports. Then there are combo boxes linked to each option.

Private Sub OptSpecialreports_BeforeUpdate(Cancel As Integer)
Me.CmbCustomer1 = Null
Me.CombPallno = Null
Me.Cmbmodel = Null
Me.txtDatefrom = Null
Me.txtTo = Null
Me.CmbCustomer2 = Null
End Sub

Private Sub PrintIt(rptName As String)
Dim stWhere As String
Dim myInt As String

Select Case Me!OptSpecialreports
Case 1
myInt = Forms![New form Sample]!CmbCustomer1
stWhere = "[CUSTOMER] = '" & myInt & "'"
Case 2
myInt = Forms![New form Sample]!CombPallno
stWhere = "[PALL No] = '" & myInt & "'"
Case 3
myInt = Forms![New form Sample]!Cmbmodel
stWhere = "[MODEL CODE] = '" & myInt & "'"
Case 4
myInt = Forms![New form Sample]!CmbCustomer2
stWhere = "([RUN DATE/CONCERN ISSUE DATE]Between [txtDatefrom] And [txtTo]) And ([CUSTOMER]= '" & myInt & "')"
Case Else
stWhere = ""
End Select

DoCmd.OpenReport (rptName), acViewPreview, , stWhere

End Sub

If I leave a combo box empty, it prompts as an error
'Invalid use of Null'

What I want is if a combo box is left blank it would display a msgbox asking to select data from combo box first.

I do understand that as [myInt] has been defined as a variable and apparently a variable cannot have a null value. But how can I inform the user to select or insert some data first before proceed and not let auto error message display.

Looking forward to suggestions.

 

[Mile-O]

I do understand that as [myInt] has been defined as a variable and apparently a variable cannot have a null value.

Different data-types have different "default" values: numbers and dates are typically 0, booleans are False, objects are Nothing, and strings are Null strings (or the constant vbNullstring: "" )

You can check your comboboxes before you run the sub with the IsNull() function

i.e.

If IsNull(Me.MyCombo) Then
    MsgBox "Please fill out the combo"
    Exit Sub
End If

 

[Mansoor Ahmad]

Thank you for reply.

As you would have seen in the code above, each combo box is set to a null value before update.

If I use this code

If IsNull(Me.MyCombo) Then

for each combo box then if I select one option, other combo boxex will still be having null value. Wouldn't it still give msgbox to enter value in other combos, unless all combo boxes are populated.? I may be wrong.

 

[Mansoor Ahmad]

quote include your suggested full code. My mistake in pasting