MS Access – Mandatory Fields, Mandatory Entries

Here is a common question: “How can I make a field mandatory to my users?”

Well, as always, there are different techniques that can be employed. I will breifly covert 2: using Table Field Properties and using Form Events.

 

Table Setup

The easiest method is to merely set the table’s Required field property to Yes.

That said, this approach does present limitations. Mainly the fact that the message returned to the user references the Field Name (see the example below) and not its’ Caption or Description or Form Control Name, so the message in fact ends up confusing most users! This is why, using form event can come in handy.

The field ‘YourFieldName’ cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field.

 

Form Event

Happily, we can add a lot more functionality and user-friendliness to our form by using its’ BeforeUpdate event to perform our validation. By inserting a little VBA code, for instance, something along the lines of:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.[YourControlName]) Then
        Cancel = True
        MsgBox "You must enter a value for 'YourControlNameOrYourDescription'. Please make a valid entry or press ESC to Cancel"
        'You could set the focus on the specific control if your wish, change the background color, ...
    End If
End Sub

As you can see, both are easy to implement, but the second method offers much more control and you can explain to your users in plain English to problem. Furthermore, you could also perform more advance data validation to not only ensure they have made an entry, but that it meet the require format…

6 responses on “MS Access – Mandatory Fields, Mandatory Entries”

  1. David Lafko

    Great example above my question is what is the code for set the focus on the specific control if your wish, change the background color and so on.. I am very new to access so please explain in rookie talk..

    Thanks

    1. Daniel Pineault Post author

      To setfocus on a control, you’d do something like:

      Me.ControlName.SetFocus

      To change the background color of a control, you’d do something along the lines of:

      Me.ControlName.BackColor = vbYellow

      I hope this helps a bit.

  2. KC

    I am not certain why, but this is not working for me. When I put it on the .beforeUpdate it does not run. It is my first field on the form. When the form opens, I place the cursor in it. Forcing the user to be in the field, but the before update is not running. What am I doing wrong? I tried it on the .exit; it worked. Except if someone is just in the form looking around, they MUST enter something in PName field. This does not work. Is there something different with 2010? Any suggestions?

    1. KC

      My bad! I just figured it out. I was on the beforeupdate of the field, not the form. Made a big difference. Thanks!

  3. chrishawn miles

    Hello
    There is a required text field in the Table Design but that only makes people put in a value the first time and not the second time or the third and so on.

    I’m not very fond of using VBA because I don’t if I make a mistake using it that it will then crash my database.
    Do you have any recommendations?

    1. Daniel Pineault Post author

      If you set the Allow Zero Length to False, the user will be required to actually have entered a value, and a blank entry will not be accepted.

      As for your second comment. If you include error handling in all your procedures (as I suggest should be a best practice for any programmer) then your application will not crash, but rather simply report the error to you allowing you the chance to rectify the situation.

      You could also use the control’s Before Update event to check what the new value is and act accordingly, refusing an empty or “” value from the user and forcing to input a valid entry before saving.

      Hope this helps.