2 Required Fields to Display 1 Message

[CBG2112]

Hi. I have the following code below where after user makes a selection the code will check if the other necessary fields are filled in or not. Then, a message will be displayed if any or both required fields are not filled. I am able to do this with only one necessary field but unable to duplicate when there are two mandatory fields needed for a given value. I'm new to access so any help will be much appreciated.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strAField As String
Dim strBField As String

strAField = ""
strBField = ""

If Nz(Me.FStatusID) = 3 And Nz(Me.BoxLoc) = "" And Nz(Me.DestDate) = "" Then
strAField = "BoxLoc"
strBField = "DestDate"
ElseIf Nz(Me.FStatusID) = 5 And Nz(Me.BoxLoc) = "" Then
strAField = "BoxLoc"
ElseIf Nz(Me.FStatusID) = 6 And Nz(Me.BoxLoc) = "" And Nz(Me.DestDate) = "" Then
strAField = "BoxLoc"
strBField = "DestDate"
ElseIf Nz(Me.FStatusID) = 7 And Nz(Me.BoxLoc) = "" Then
strBField = "DestDate"

End If

If strAField <> "" And strBField <> "" Then
MsgBox "Please complete required " & strAField & "."
MsgBox "Please complete required " & strBField & "."
Cancel = True

End If

End Sub

 

[wazz]

the basic outline would be:

If strAField & "" = "" Then
    MsgBox "Please complete strAField."
    Cancel = True
    Exit Sub
End If
If strBField & "" = "" Then
    MsgBox "Please complete strBField."
    Cancel = True
    Exit Sub
End If

 

[CBG2112]

After updating the module, I am still unable to do what I want for the form. When the user enters a value, I would like to validate if the mandatory one or two fields have been completed for each value. If not completed, a messagebox should appear indicating which fields are missing. For choices with one necessary field, the module works fine. But for cases where two fields are necessary, the form saves the record even though only field has been completed. I would like a messagebox to appear that the 1 or both fields are missing. Thanks.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strWhatField As String
Dim strWhichField As String

strWhatField = ""


If Nz(Me.FINRAStatusID) = 3 And Nz(Me.Box_Location) = "" And Nz(Me.Destruction_Date) = "" Then
strWhatField = "Box Location"
MsgBox "Please complete required " & strWhatField & "."
Cancel = True
ElseIf Nz(Me.FINRAStatusID) = 5 And Nz(Me.Box_Location) = "" Then
strWhatField = "Box Location"
MsgBox "Please complete required " & strWhatField & "."
Cancel = True
ElseIf Nz(Me.FINRAStatusID) = 6 And Nz(Me.Box_Location) = "" And Nz(Me.Destruction_Date) = "" Then
strWhatField = "Box Location"
strWhichField = "Destruction Date"
MsgBox "Please complete required " & strWhatField & "."
Cancel = True
ElseIf Nz(Me.FINRAStatusID) = 7 And Nz(Me.Destruction_Date) = "" Then
strWhatField = "Destruction Date"
MsgBox "Please complete required " & strWhatField & "."
Cancel = True
End If

'If strWhatField <> "" And strWhichField <> "" Then
'MsgBox "Please complete required " & strWhatField & "."
'Cancel = True
'ElseIf strWhichField <> "" Then
'MsgBox "Please complete required " & strWhichField & "."
'Cancel = True
'ElseIf strWhatField <> "" Then
'MsgBox "Please complete required " & strWhatField & "."
'Cancel = True
'End If

End Sub

 

[CBG2112]

I've updated the module to look like below. However, it still does not do what I want it to do. When the user enters a value, it will prompt user if either the mandatory field or mandatory fields have been completed. I'm only able to accomplish this for one field. When a chosen value has two required fields, the form still saves a record even though only one field has been filled. A messagebox should appear indicating the missing field or fields. How do I go about in solving this? Thanks.
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strWhatField As String
Dim strWhichField As String

strWhatField = ""
strWhichField = ""


If Nz(Me.FINRAStatusID) = 3 And Nz(Me.Box_Location) = "" And Nz(Me.Destruction_Date) = "" Then
strWhatField = "Box Location"
strWhichField = "Destruction Date"
MsgBox "Please complete required " & strWhatField strWhichfield & "."
Cancel = True
ElseIf Nz(Me.FINRAStatusID) = 5 And Nz(Me.Box_Location) = "" Then
strWhatField = "Box Location"
MsgBox "Please complete required " & strWhatField & "."
Cancel = True
ElseIf Nz(Me.FINRAStatusID) = 6 And Nz(Me.Box_Location) = "" And Nz(Me.Destruction_Date) = "" Then
strWhatField = "Box Location"
strWhichField = "Destruction Date"
MsgBox "Please complete required " & strWhichField strWhatField & "."
Cancel = True
ElseIf Nz(Me.FINRAStatusID) = 7 And Nz(Me.Destruction_Date) = "" Then
strWhatField = "Destruction Date"
MsgBox "Please complete required " & strWhichField & "."
Cancel = True
End If

 

[Scooterbug]

Try using OR instead of AND. Your criteria is only being met if both of the require fields are blank. If one is blank and the other isn't then the criteria isn't being met.

ie:
If Nz(Me.FINRAStatusID) = 3 And Nz(Me.Box_Location) = "" OR Nz(Me.Destruction_Date) = "" Then
strWhatField = "Box Location"
strWhichField = "Destruction Date"
MsgBox "Please complete required " & strWhatField strWhichfield & "."
Cancel = True