MS Access Before Change or Before Update Function: How to Make Another Field Required?


In MS Access 2010, if you want to make a field ‘required’ or compulsory based on a data inserted in another field, you can do it in different ways. Through the following example, I will show you how to do it using data macros and Visual Basic for Applications (VBA).

The example has two tables with the following fields:

ID (autonumber)
Source (lookup: “Arranged by My Org”, “Released by My Org”, “Not Arranged by My Org”)
MainMessage (text)

We want users to insert main message(s) in the field MainMessage if they select either “Arranged by My Org” or “Released by My Org” in the field Source.

Data Macro

Open your table. Go to the menu Table, click on Before Change and follow the following steps:

  1. Select If and paste the following code: [Source]=”Arranged by My Org” Or [Source]=”Released by My Org” And IsNull([MainMessage])
  2. Select RaiseError, type 1 in Error Number, Please insert main message(s) in the field Main Message in Error Description, and save.

Open FormDataMacro and try the code.

VBA

Open FormVBA in design view, select form properties, click on event, under Before Update select Event Procedure, click on the ellipsis (…) to open the VBA code area, and paste the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
strMsg = “Please insert main message(s) in the field Main Message”
If (Me![Source] = “Arranged by My Org”) Or (Me![Source] = “Released by My Org”) Then
If IsNull(Me![MainMessage]) Then
MsgBox strMsg
Cancel = True
End If
End If
End Sub

Save and try the code by inserting data in the form.

Click here to download the sample database

Advertisements

2 thoughts on “MS Access Before Change or Before Update Function: How to Make Another Field Required?

  1. This is the right weblog for anyone who needs to find out about this topic. You realize so much its virtually laborious to argue with you (not that I actually would want…HaHa). You positively put a new spin on a subject thats been written about for years. Nice stuff, just great!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s