none
VBA If Error code RRS feed

  • 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.

    Sunday, February 23, 2020 2:52 PM

All replies

  • 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)

    Sunday, February 23, 2020 3:27 PM
  • 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

    Sunday, February 23, 2020 4:05 PM