Required feild in form - Access informs and then closes the form

[RobertIngles]

I have set the required property to "Yes" in the table design.

If the field is null and user clicks the close form button, Access msg pops up and informs the user that the field is required but then shows a msg that the record cannot be saved at this time because the field is null and closes the form.

I want to have the user returned to the form field to correct the mistake by populating the require field.

Can this be done from the table design or do I have to code it. I tried to set the required property in the table to "No" and placed the following code in the BeforeUpdate property of the form but it did not work. The form closed saving the form without all the required information:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.LocationID = Null Then
MsgBox "LocationID Cannot be Null", vbOKCancel

Me.[BookingDate] = Now()
End If
End Sub

Thanks for your help!

 

[BTSTeam]

So your code checks to see whether LocationID is blank, gives the user a warning, puts todays date in [BookingDate] if Location ID is blank and then carries on with the Update action which it cannot complete because a field is blank.

I think you need to be able to jump out of the Sub and abort the Update with something like this in between your MsgBox line and the line where you set [BookingDate]:

LocationID.SetFocus
Exit Sub

Also, there is no need for the vbOKCancel option at the end of the MsgBox line because you aren't taking any action depending on which option the user chooses.