asked on

limit the number of checkable boxes on a continuous Access form.

On my continuous form I have a checkbox called "select". This select field is tied to a question on the form. I have several questions in the table.
So in the detailed section it looks like:
question [ ] <= Checkbox "select"
question [ ]
question [ ]
question [ ]
question [ ]
question [ ]
I only want the user to be able to check "two" of the checkboxes on this continuous form. If the user tries to select a third checkbox I need a msgbox to pop up and state that "only two checkboxes can be selected at a time and 'not' check that third box. This will force the user to uncheck a different previously checked box before they they are permitted to check a different box. Wondering how I can do this?
Right now I placed an invisible textbox on the form and placed this code: =Count(IIf(Nz([Select],0)=-1,1,Null)) in the control source. This method doesn't really work for obvious reasons . I need to somehow place some code in the after update event of the checkbox to see if two checkboxes have already been checked on the continuous form and not allow the user to check into the third box until the user first unchecks one of the other boxes.

Any assistance with the code on this would be most appreciated!

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Rudolph

ASKER

Thank you Pat,

But I am getting this error....thoughts?

Mike Rudolph

ASKER

Nevermind...tweaked it and found the answer:

Private Sub Select_BeforeUpdate(Cancel As Integer)
  If Me.Select = True Then
      If DCount("*", "tblQuestions", "[Select] = True") >= 2 Then
        MsgBox "Two items already selected.  Please deselect one before selecting another.", vbOKOnly
        Cancel = True
        Me.Undo
        Exit Sub
    End If
End If
End Sub

Open in new window

Mike Rudolph

ASKER

Thanks again Pat! Your code led me to the right direction!

PatHartman

You're welcome.