VBA If Error code

Question

I've made part of my form "required" in the form table properties.

I am now getting a run time error, I want to use a code to stop processing the information present an error message allow the user to correct the issue then submit.

Can anyone help.

Answer 1

You might use code in the Before Update event of the form, like this (with the appropriate names substituted):

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.ThisField) Then
        Me.ThisField.SetFocus
        MsgBox "Please fill in this field!", vbExclamation
        Cancel = True
        Exit Sub
    End If
    If IsNull(Me.ThatField) Then
        Me.ThatField.SetFocus
        MsgBox "Please fill in that field!", vbExclamation
        Cancel = True
        Exit Sub
    End If
End Sub

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

If you want the insertion of a value into a column (field) to be mandatory define it as NOT NULL in the table definition by setting the column's Required property to True (Yes) in table design view, which I assume is what you've done.  If the column is of text data type you should also set its AllowZeroLength property to False (No).

In your form you can then either handle any error in the form's Error event procedure, or anticipate the potential error in the form's BeforeUpdate event procedure as described by Hans.  Note that to prevent both Null and a zero-length string you'd need to test for:

   If Len(Nz(FieldName,"")) = 0

or similar, rather than testing for Null.

You can additionally disallow a Null bound control in a form by controlling the order of data entry in the form by means of code in the form's module..  For an example of this you might like to take a look at SaveDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

---

Ken Sheridan, Stafford, England