Check for NULL Only Work Once

[scottbouley]

I have a form for adding parts used on a job to a work order. The user selects a part from a combo box and clicks add to open a form that allows them to change the default quantity and cost. Prior to opening the form, the code checks to make sure the user has selected something. The check works fine the first time. However, if a part is added successfully and the user fails to make a selection on the next part, the check is skipped.

I added a breakpoint to the If statement at the top after the first part was added to check the value of the listbox when adding the second part. The value of the cbo_Parts is NULL after the sub routine runs. Therefore, I'm assuming that the check is being skipped.

Private Sub cmd_Add_Part_Used_Click()

If ((IsNull(cbo_Parts) Or (cbo_Parts) = " ")) Then
    MsgBox "You must select a part first!", vbCritical, "Invalid Action"
    cbo_Parts.SetFocus
    Exit Sub
Else
    DoCmd.OpenForm "frm_Add_Part_Used", acNormal
    Forms![frm_Add_Part_Used]![WO_ID] = [WO_ID]
    Forms![frm_Add_Part_Used]![Part_ID] = [cbo_Parts].[Column](0)
    Forms![frm_Add_Part_Used]![Unit_Cost] = [cbo_Parts].[Column](3)
End If

End Sub

I'm sure I'm making a rookie mistake and really could use some help.

Thank you for taking the time to read this.

[Mihail]

Have you somewhere some code to restore the Null value in combo after the first selection ? I don't think so.

Option explicit

Dim FirstClickOnCombo as Boolean

Private sub cbo_Parts_Click()
    FirstClickOnCombo = True
End Sub

Private Sub cmd_Add_Part_Used_Click()
    If not FirstClickOnCombo  Then
        MsgBox "You must select a part first!", vbCritical, "Invalid Action"
        cbo_Parts.SetFocus
Exit Sub
    End If

    DoCmd.OpenForm "frm_Add_Part_Used", acNormal
    Forms![frm_Add_Part_Used]![WO_ID] = [WO_ID]
    Forms![frm_Add_Part_Used]![Part_ID] = [cbo_Parts].[Column](0)
    Forms![frm_Add_Part_Used]![Unit_Cost] = [cbo_Parts].[Column](3)
End Sub

'This is to impressed the user :)
Private sub cbo_Parts_GotFocus()
    cbo_Parts.Dropdown
End Sub

[scottbouley]

Have you somewhere some code to restore the Null value in combo after the first selection ? I don't think so.

Option explicit

Dim FirstClickOnCombo as Boolean

Private sub cbo_Parts_Click()
    FirstClickOnCombo = True
End Sub

Private Sub cmd_Add_Part_Used_Click()
    If not FirstClickOnCombo  Then
        MsgBox "You must select a part first!", vbCritical, "Invalid Action"
        cbo_Parts.SetFocus
Exit Sub
    End If

    DoCmd.OpenForm "frm_Add_Part_Used", acNormal
    Forms![frm_Add_Part_Used]![WO_ID] = [WO_ID]
    Forms![frm_Add_Part_Used]![Part_ID] = [cbo_Parts].[Column](0)
    Forms![frm_Add_Part_Used]![Unit_Cost] = [cbo_Parts].[Column](3)
End Sub

'This is to impressed the user :)
Private sub cbo_Parts_GotFocus()
    cbo_Parts.Dropdown
End Sub

I wasn't able to figure out how to impliment your recommendation but it helped me realize where I needed to reset the combo box.

Thank you very much!

The following code worked fine:

Private Sub cmd_Add_Part_Used_Click()

Me.cbo_Parts = Me.cbo_Parts.ItemData(-1)

If ((IsNull(cbo_Parts) Or (cbo_Parts) = " ")) Then
    MsgBox "You must select a part first!", vbCritical, "Invalid Action"
    cbo_Parts.SetFocus
    
    Exit Sub
Else
    DoCmd.OpenForm "frm_Add_Part_Used", acNormal
    Forms![frm_Add_Part_Used]![WO_ID] = [WO_ID]
    Forms![frm_Add_Part_Used]![Part_ID] = [cbo_Parts].[Column](0)
    Forms![frm_Add_Part_Used]![Unit_Cost] = [cbo_Parts].[Column](3)
End If

End Sub

Thanks again.

[Mihail]

Glad to help you. Any code is good if do the job.
Note please that my structure for If-Then-EndIf is, as far as I know, a little bit faster than yours (If-Then-Else-EndIf)