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:
Source (lookup: “Arranged by My Org”, “Released by My Org”, “Not Arranged by My Org”)
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.
Open your table. Go to the menu Table, click on Before Change and follow the following steps:
- Select If and paste the following code: [Source]=”Arranged by My Org” Or [Source]=”Released by My Org” And IsNull([MainMessage])
- 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.
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
Cancel = True
Save and try the code by inserting data in the form.