Search code examples
vbams-accessms-access-2016

Logical Operator "AND" not returning expected true

I have a form (Frm_JobTicket) with an unbound field (Finish_Description) on it. This value is supposed to be collected from potentially 3 other fields, also on that same form. They are:

Cmbo_FinishNumber_JobTicket

Wrap_Color1

Foil_Color1

Below is the code which is not returning the expected true

Private Sub Cmbo_FinishNumber_JobTicket_AfterUpdate()

    'Declare Variables
    Dim frm As Access.Form
    Set frm = Forms!Frm_JobTicket

    'Lookup Finish Description based off of the Finish Number entered in Paint Info area of Job Ticket
    frm("Finish_Description") = DLookup("Paint_Finish_Description", "Tbl_PaintFinish", "Finish_Number ='" & frm("Cmbo_FinishNumber_JobTicket") & "'")
        
        If IsNull(frm("Finish_Description")) Then
            GoTo AllFinishOptions
        Else
            Exit Sub
        End If

AllFinishOptions:
        If IsNull(frm("Cmbo_FinishNumber_JobTicket")) Then
            frm("Finish_Description") = frm("Wrap_Color1")
            MsgBox "Wrap Color", vbOKOnly
        ElseIf IsNull(frm("Wrap_Color1")) And IsNull(frm("Cmbo_FinishNumber_JobTicket")) Then
            frm("Finish_Description") = frm("Foil_Color1")
            MsgBox "Foil Color", vbOKOnly
        End If
    
End Sub

When running through records on the form in which both Cmbo_FinishNumber_JobTicket and Wrap_Color1 are null values it still is outputting the first MsgBox "Wrap Color" instead of the second "Foil Color" like I would expect. It doesn't seem to ever pass to the ElseIf part of the statement. I don't want to use an OR because it is intended to be a progressive list. If Finish Number is null then Wrap Color gets inserted. If Wrap Color is null then Foil Color gets inserted.

Finish Number and Wrap Color null

Any suggestions on either fixing this or a workaround are much appreciated, thank you!

Solution

  • Switch your conditions around, you'll always hit the first part of the If statement (and consequently not reach the ElseIf part) when you need both to be true for the one and only one to be true for the other:

    If IsNull(frm("Wrap_Color1")) And IsNull(frm("Cmbo_FinishNumber_JobTicket")) Then
            frm("Finish_Description") = frm("Foil_Color1")
            MsgBox "Foil Color", vbOKOnly
ElseIf IsNull(frm("Cmbo_FinishNumber_JobTicket")) Then
            frm("Finish_Description") = frm("Wrap_Color1")
            MsgBox "Wrap Color", vbOKOnly
End If