How to change the validation rules for one field based on another

Ken's simple one-line table level validation rule is sufficient and effective with no VBA required other than, if desired, providing a user-friendly informational message -- rather than the Access Engine error message -- to a user who selects Group = False and then tries to enter more than one participant or vice versa.

Over the years, I've found that simple is better than elaborate when it comes to managing data requirements in Access (and other software development projects).

I've even coined a phrase to describe it: Code-wadding. That refers to the tendency to put wads of code into an application to accomplish tasks that could be managed with things like a table-level validation rule.

Hi James Martin

Thank you for posting on the Q&A Forum.

Based on the tag you selected, I understand you’re working in Microsoft Access. Please correct me if I’m wrong.

If you already have a data table and want to find records that violate the rule:

Create > SQL Query and paste this SQL (replace tblBooking with your actual table name):

SELECT *
FROM tblBooking
WHERE NOT (
    ([IsGroup]=False AND [Participants]=1)
    OR
    ([IsGroup]=True AND [Participants]>1 AND [Participants]<=12)
);

This query will return all rows that do not meet the rule.

Regarding new records, I am wondering whether you strictly need a "Validation Rule" (which only pops up error messages after a mistake is made), or whether you’d prefer the form to automatically handle the logic for you.

Given your requirements, Automation tends to provide a much better user experience. I’ve drafted a VBA-based approach that does the following:

• When “Group” is unchecked (Individual): The form auto-fills Participants = 1 and locks the field to prevent errors up front.
• When “Group” is checked: The field is unlocked and shows a dropdown limited to 2 through 12.

To do this, you may follow the steps below:

Step 1: Convert Participants to a Combo Box

Open the form in Design View > Right‑click the Participants textbox > Change To > Combo Box.

Open Property Sheet (F4) > in Data tab: Set Row Source Type = Value List / Set Limit To List = No

Step 2: Add automation code (VBA)

Select your IsGroup checkbox. In Property Sheet (F4) > Event tab > After Update > click […] > choose Code Builder.

Paste the code below. Note: Please ensure your field names in the code (IsGroup and Participants) match the actual Name property of your controls in Access.

Option Compare Database
' 1. When the Checkbox is clicked
Private Sub IsGroup_AfterUpdate()
    Call UpdateUI ' Call the main function below
End Sub
' 2. When navigating between records (to keep the state correct)
Private Sub Form_Current()
    Call UpdateUI
End Sub
' 3. Main Logic Function (Used by both events above)
Sub UpdateUI()
    ' CASE 1: INDIVIDUAL (Group is Unchecked)
    If Me.IsGroup = False Then
        Me.Participants.LimitToList = False ' Allow values outside the list
        Me.Participants.RowSource = ""      ' Clear dropdown list
        Me.Participants.Value = 1           ' Auto-fill 1
        Me.Participants.Locked = True       ' Lock the field
        Me.Participants.BackColor = RGB(220, 220, 220) ' Grey out to indicate locked state
    ' CASE 2: GROUP (Group is Checked)
    Else
        Me.Participants.Locked = False      ' Unlock field
        Me.Participants.BackColor = vbWhite ' Set color back to white
        ' Create dropdown list from 2 to 12
        Me.Participants.RowSource = "2;3;4;5;6;7;8;9;10;11;12"
        Me.Participants.LimitToList = True  ' Force user to select from list
        ' If the value is currently 1 (switched from Individual), clear it
        If Me.Participants.Value = 1 Then
            Me.Participants.Value = Null
            Me.Participants.SetFocus   ' Focus on the field
            Me.Participants.Dropdown   ' Auto-expand the list for convenience
        End If
    End If
End Sub
 

Step 3: Save and Test Switch to Form View: 

• Uncheck Group: The Participant field should turn grey and auto-set to 1. 
• Check Group: The field should unlock, turn white, and offer a dropdown from 2-12. 

If you want to enforce a validation rule to prevent incorrect edits directly in the table after data entry:

Open the tblBooking table in Design View > Open Property Sheet (F4) > Add:

• Validation Rule: ([IsGroup]=False And [Participants]=1) Or ([IsGroup]=True And [Participants]>1 And [Participants]<=12)
• Validation Text: Data Error: Individual bookings must have exactly 1 participant. Group bookings must have between 2 and 12 participants.

Let me know if this aligns with what you want. If you still run into issues, please provide a sample file in a private message so I can better assist you.

Looking forward to your reply. 

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.