Multiple MsgBox's

[Taff]

Hi All,

I have a form with several fields on which can not be null. What I am trying to do is when the user clicks a Cmdbutton to exit the form a Message box to pop up saying "Field A can not be null". When the user clicks the Cmdbutton again if the next field along is null the same thing happens with that field name.

What I am after is something shorter than me having to type for example:-

If IsNull([VehicleReg]) Then
Dim Title1, Prompt1, iReturn1, Buttons1
Title1 = "Information"
Prompt1 = "Vehicle Registration can not be null!"
Buttons1 = vbOKOnly + vbInformation + vbDefaultButton1
iReturn1 = MsgBox(Prompt1, Buttons1, Title1)
End If
If IsNull([TrailerNo]) Then
Dim Title2, Prompt2, iReturn2, Buttons2
Title2 = "Information"
Prompt2 = "Trailer ID can not be null!"
Buttons2 = vbOKOnly + vbInformation + vbDefaultButton1
iReturn2 = MsgBox(Prompt2, Buttons2, Title2)
End If
If IsNull([Haulier]) Then
Dim Title3, Prompt3, iReturn3, Buttons3
Title3 = "Information"
Prompt3 = "Haulier ID can not be null!"
Buttons3 = vbOKOnly + vbInformation + vbDefaultButton1
iReturn3 = MsgBox(Prompt3, Buttons3, Title3)
End If

Also I would only like to display one message box per click of the Cmdbutton, so, for example if all three fields were null, only the msgbox for the first field would be displayed.

Not sure if there is a short way of doing this.




Ant.

 

[ghudson]

You need to use the forms BeforeUpdate event to test if the fields are Null and cancel the event if True so that they can not close the form [or move to another record] if there are any Nulls in your tested fields. This code should do what you want and in a more efficient manner...

Private Sub Form_BeforeInsert(Cancel As Integer)
    
    If IsNull([VehicleReg]) Then
        DoCmd.CancelEvent
        Me.[VehicleReg].SetFocus
        MsgBox "Vehicle Registration can not be null!", vbInformation + vbOKOnly, "Invalid Entry"
    ElseIf IsNull([TrailerNo]) Then
        DoCmd.CancelEvent
        Me.[TrailerNo].SetFocus
        MsgBox "Trailer ID can not be null!", vbInformation + vbOKOnly, "Invalid Entry"
    ElseIf IsNull([Haulier]) Then
        DoCmd.CancelEvent
        Me.[Haulier].SetFocus
        MsgBox "Haulier ID can not be null!", vbInformation + vbOKOnly, "Invalid Entry"
    Else
        'do nothing
    End If
    
End Sub