Mutually Exclusive Check Boxes in a Microsoft Access Continuous Form

A very common UI requests I hear from Microsoft Access developers goes something like this:

I have a continuous form with a check box, and only one record should be allowed to have it checked at a time.

Think :

  • Primary Phone
  • Default Address
  • Main Contact
  • Preferred Email
  • etc.

In a perfect world, we’d just use option buttons. But continuous forms don’t work that way where each record is rendered independently, so Access gives us many check boxes, not one logical group.

So how do we enforce mutual exclusivity?

Let’s walk through this step by step, starting with a straightforward After Update solution and progressively refining it into something cleaner, reusable, and faster. You pick the approach that suits your needs.
 

Version 1 – The Direct After Update Approach

The most common solution is to put logic directly in the check box’s After Update event and the code would go something like:

Private Sub IsPrimary_AfterUpdate()
    On Error GoTo Error_Handler
    Dim oRs                   As DAO.Recordset
    Dim lCurrentRecId         As Long

    If Me.IsPrimary = True Then
        ' Store current record's ID to preserve it after requery
        lCurrentRecId = Me.PhoneId

        ' Open recordset for the form's RecordSource
        Set oRs = Me.RecordsetClone
        oRs.MoveFirst

        ' Loop and uncheck all other records
        Do While Not oRs.EOF
            If oRs!PhoneId <> lCurrentRecId Then
                oRs.Edit
                oRs!IsPrimary = False
                oRs.Update
            End If
            oRs.MoveNext
        Loop

        '*** Could use an update query ***

        ' Requery the form without moving records
        Me.Recordset.Requery
    End If

Error_Handler_Exit:
    On Error Resume Next
    oRs.Close
    Set oRs = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: IsPrimary_AfterUpdate" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Now, there is nothing wrong with this approach, but it becomes tedious when you need to apply the same logic to 3 forms and 7 fields, …

We can do better!
 

Version 2 – Moving the Logic into a Reusable Procedure

The next step is obvious: centralize the logic.

Instead of rewriting this code everywhere, we create a module-level procedure that any form can call. The procedure might look like:

'---------------------------------------------------------------------------------------
' Procedure : CheckBox_SetExclusive
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Enforce mutually exclusive check boxes on a continuous form
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Microsoft Office 1X.0 Access database engine Object Library (ACE for 2007+)
'             OR Microsoft DAO 3.6 Object Library  (prior to 2007)
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' oFrm      : Form/SubForm that hold the check boxes
' sPKField  : Name of the form's PK field
' lPKValue  : Value of the PK field for the currently selected check box
' sChkField : Name of the check box field
' bExclusivityCondition : if the exclusivity is a True value, or inversed False value
'
' Usage Examples:
' ~~~~~~
' Only allow a single check box to be select
' Call CheckBox_SetExclusive(Me, "PhoneId",Me.PhoneId, "IsPrimary")
' Call CheckBox_SetExclusive(Me, "PhoneId",Me.PhoneId, "IsPrimary", True)
'
' Only allow a single check box NOT to be selected
' Call CheckBox_SetExclusive(Me, "PhoneId", Me.PhoneId, "IsPrimary", False)
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2026-02-24              Initial Public Release
'---------------------------------------------------------------------------------------
Public Sub CheckBox_SetExclusive(ByVal oFrm As Access.Form, _
                                 ByVal sPKField As String, _
                                 ByVal lPKValue As Long, _
                                 ByVal sChkField As String, _
                                 Optional ByVal bExclusivityCondition As Boolean = True)
    On Error GoTo Error_Handler
    Dim oRs                   As DAO.Recordset

    If oFrm(sChkField) = bExclusivityCondition Then
        ' Open recordset for the form's RecordSource
        Set oRs = oFrm.RecordsetClone
        oRs.MoveFirst

        ' Loop and uncheck all other records
        Do While Not oRs.EOF
            If oRs.Fields(sPKField).Value <> lPKValue Then
                oRs.Edit
                oRs.Fields(sChkField).Value = Not bExclusivityCondition
                oRs.Update
            End If
            oRs.MoveNext
        Loop

        ' Requery the form while remaining on the same record
        oFrm.Recordset.Requery
    End If

Error_Handler_Exit:
    On Error Resume Next
    oRs.Close
    Set oRs = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: CheckBox_SetExclusive" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

and now we call implement it on any check box with a single line of code!

' Only allow one record to be checked
Call CheckBox_SetExclusive(Me, "PhoneId", Me.PhoneId, "IsPrimary")

OR

' Only allow one record NOT to be checked - reverse logic
Call CheckBox_SetExclusive(Me, "PhoneId", Me.PhoneId, "IsPrimary", False)

 

Version 3 – Loop Only Through Relevant Records

Why process records that are already False?

We can reduce the workload by first filtering the cloned recordset before looping. Thus, we transform our previous procedure slightly into:

'---------------------------------------------------------------------------------------
' Procedure : CheckBox_SetExclusive
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Enforce mutually exclusive check boxes on a continuous form
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Microsoft Office 1X.0 Access database engine Object Library (ACE for 2007+)
'             OR Microsoft DAO 3.6 Object Library  (prior to 2007)
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' oFrm      : Form/SubForm that hold the check boxes
' sPKField  : Name of the form's PK field
' lPKValue  : Value of the PK field for the currently selected check box
' sChkField : Name of the check box field
' bExclusivityCondition : if the exclusivity is a True value, or inversed False value
'
' Usage Examples:
' ~~~~~~
' Only allow a single check box to be select
' Call CheckBox_SetExclusive(Me, "PhoneId",Me.PhoneId, "IsPrimary")
' Call CheckBox_SetExclusive(Me, "PhoneId",Me.PhoneId, "IsPrimary", True)
'
' Only allow a single check box NOT to be selected
' Call CheckBox_SetExclusive(Me, "PhoneId", Me.PhoneId, "IsPrimary", False)
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2026-02-24              Initial Public Release
'---------------------------------------------------------------------------------------
Public Sub CheckBox_SetExclusive(ByVal oFrm As Access.Form, _
                                 ByVal sPKField As String, _
                                 ByVal lPKValue As Long, _
                                 ByVal sChkField As String, _
                                 Optional ByVal bExclusivityCondition As Boolean = True)
    On Error GoTo Error_Handler
    Dim oRs                   As DAO.Recordset

    If oFrm(sChkField) = bExclusivityCondition Then
        ' Open recordset for the form's RecordSource
        Set oRs = oFrm.RecordsetClone

        ' Apply a filter so we only process the required records
        oRs.Filter = "[" & sChkField & "] = " & bExclusivityCondition & _
                     " AND [" & sPKField & "] <> " & lPKValue

        ' Loop and uncheck all other records
        If Not (oRs.BOF And oRs.EOF) Then
            oRs.MoveFirst
            Do While Not oRs.EOF
                oRs.Edit
                oRs.Fields(sChkField).Value = Not bExclusivityCondition
                oRs.Update
                oRs.MoveNext
            Loop
        End If

        ' Requery the form while remaining on the same record
        oFrm.Recordset.Requery
    End If

Error_Handler_Exit:
    On Error Resume Next
    oRs.Close
    Set oRs = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: CheckBox_SetExclusive" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

and we implement it in the exact same manner as the previous procedure by simply doing:

' Only allow one record to be checked
Call CheckBox_SetExclusive(Me, "PhoneId", Me.PhoneId, "IsPrimary")

OR

' Only allow one record NOT to be checked - reverse logic
Call CheckBox_SetExclusive(Me, "PhoneId", Me.PhoneId, "IsPrimary", False)

 

Version 4 – Using An UPDATE Query

This may not always be applicable for all cases, but because I develop forms so they are bound to a single table … I thought to myself

Why loop through records at all?!

This is where the most efficient solution surfaces, simply use a single Update query to update all the records in one go.

'---------------------------------------------------------------------------------------
' Procedure : CheckBox_SetExclusive
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Enforce mutually exclusive check boxes on a continuous form
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Microsoft Office 1X.0 Access database engine Object Library (ACE for 2007+)
'             OR Microsoft DAO 3.6 Object Library  (prior to 2007)
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' oFrm      : Form/SubForm that hold the check boxes
' sPKField  : Name of the form's PK field
' lPKValue  : Value of the PK field for the currently selected check box
' sChkField : Name of the check box field
' bExclusivityCondition : if the exclusivity is a True value, or inversed False value
'
' Usage Examples:
' ~~~~~~
' Only allow a single check box to be select
' Call CheckBox_SetExclusive(Me, "PhoneId",Me.PhoneId, "IsPrimary")
' Call CheckBox_SetExclusive(Me, "PhoneId",Me.PhoneId, "IsPrimary", True)
'
' Only allow a single check box NOT to be selected
' Call CheckBox_SetExclusive(Me, "PhoneId", Me.PhoneId, "IsPrimary", False)
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2026-02-24              Initial Public Release
'---------------------------------------------------------------------------------------
Public Sub CheckBox_SetExclusive(ByVal oFrm As Access.Form, _
                                 ByVal sPKField As String, _
                                 ByVal lPKValue As Long, _
                                 ByVal sChkField As String, _
                                 Optional ByVal bExclusivityCondition As Boolean = True)
    On Error GoTo Error_Handler
    Dim sSQL                  As String

    If oFrm(sChkField) = bExclusivityCondition Then
        ' Use an update query instead of looping through each record 1 by 1, more efficient
        ' ***** Maybe be an issue depending on how the record source is defined *****
        sSQL = "UPDATE [" & oFrm.RecordSource & "] " & _
               "SET [" & sChkField & "] = " & IIf(bExclusivityCondition, "False", "True") & " " & _
               "WHERE [" & sChkField & "] = " & bExclusivityCondition & " " & _
               "AND [" & sPKField & "] <> " & lPKValue
        CurrentDb.Execute sSQL, dbFailOnError

        ' Requery the form while remaining on the same record
        oFrm.Recordset.Requery
    End If

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: CheckBox_SetExclusive" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Now, this won’t work in all setup, I’m thinking of forms with complex RecordSources for instance, but the way I develop (1 table – 1 Form), it works just fine in 99% of the time.
 

Final Thoughts

Mutually exclusive check boxes in a continuous form are one of those “Access quirks” that every developer eventually encounters.

The progression looks like this:

  • Inline After Update code: quick and dirty
  • Reusable module-level procedure: cleaner and scalable
  • Filtered record sets: faster and smarter (probably the sweet spot)
  • UPDATE queries: fastest, but situational

Knowing all of these approaches lets you pick the right one for the job and that’s what separates a casual Access user from a professional Access developer.

Happy coding!