You can do this with the following setup and VBA-code. I assume you have some basic VBA knowledge. Otherwise, let me know and I will elaborate on this.
Furthermore, we focus on text and combo boxes while these field have a background color that we can set (highlight).
First, we address the required form fields by using the Tag-property. In the example below I mark the Description-field as required by setting the Tag-property in the Other-tab to the value “required”. The Tag-property can hold any text we want, so we use it here to our convenience.
I also marked the Unit-combo box as required.
Next, we build a VBA-function that checks the required fields of a form. You can place this function in the code section of the concerning form, but it’s more suitable to place it in a module because this makes it possible to call the function from any form you want.
If necessary, add a module (Insert > Module from the VBA-editor) and place this function in it:
Function ValidateRequired(frm As Form, sFormName As String) As Boolean
'Check if the required fields of a form are entered
'Declare
Dim ctl As Control, ctlFirst As Control, iCounter As Integer
'Init
ValidateRequired = True
iCounter = 0
'Loop through the form controls
For Each ctl In frm.Controls
'Check for required tag
If ctl.Tag = "required" Then
'Is the control empty?
If IsNull(ctl) Then
'Only set the background for text and combo boxes
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.BackColor = 10092543
iCounter = iCounter + 1 'count the number of required fields that are empty
End Select
'Set the outcome of this function to false, so the form update can be cancelled
ValidateRequired = False
'Keep the first control that's highlighted for setting focus on this control later on
If ctlFirst Is Nothing Then Set ctlFirst = ctl
Else
'(Re)set the background of a required field that's filled
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.BackColor = vbWhite
End Select
End If
End If
Next
'Set focus to the first highlighted field, if any, and signal an info message
If ValidateRequired = False Then
On Error Resume Next
ctlFirst.SetFocus
On Error GoTo 0
Select Case iCounter
Case 1
MsgBox "The highlighted field is required.", vbInformation, sFormName
Case Is > 1
MsgBox "The highlighted fields are required.", vbInformation, sFormName
End Select
End If
End Function
This function takes the form(object) as input and loops over all the form controls, including the text and combo boxes. If a control has a Tag-property with the value “required” and a type of text or combo box, the function check if it is filled.
If not, the background of the concerning field is set to a yellow color. If a required field is filled, the background is set to white. Furthermore, the function signals a message when one or more required fields are empty.
You can change the highlight-color and the message as you like. Or even comment out the message if you don’t want it displayed.
Last step is calling the validation-function from the form. In my example I call it from the Click-event of the Save-command button:
Private Sub cmdSave_Click()
'Validate required fields
If Not ValidateRequired(Me, "Product") Then Exit Sub
'Save the record
If Me.Dirty Then Me.Dirty = False 'Save
'Close form
DoCmd.Close acForm, Me.Name
End SubThe ValidateRequired-function takes two parameters: the form object and the name of the form as you want it displayed in the error-message. Here, we refer with the “Me” keyword to the form object. We call the function from an If-statement, so in case of an error, we exit the subroutine without saving and the form stays on the screen.
Now when you open the form in View mode and click Save without entering any data, you will get this screen:
The required fields are highlighted and the focus is set on the first missing field. After filling the required fields and clicking Save again, the record is entered in Access and the form is closed.
Hope this works for you.
Best wishes,
Tieme