How to validate that at least one checkbox is selected

[kroeger]

Hi, I am trying to validate that the user selects at least one checkbox out of three on the form in Access 2010. They can select one, two or even all three if they wish, but at least one must be checked. I am using the following code attached to the click event of a command button. It was fine but seems to have stopped working. The control names are generic to illustrate my code. The form is unbound:

if (chk1 + chk2 + chk3) = 0 then
Msgbox "Please select at least one checkbox"
exit sub
end if

Any help appreciated.

 

[bob fitz]

Try this code in the forms Before UpDate event:

If Me.chk1 = 0 And Me.chk2 = 0 And Me.chk3 = 0 then
  Msgbox "Please select at least one checkbox"
  Cancel = True
End if

 

[kroeger]

hi, sorry for the delay, the site keeps logging me out. I have tried that code and it still does not work. I have the other textboxes on the form validating in the same box of vba code, will this be causing the problems? as an example this precedes the checkbox validation and similar code follows it as well:

If Len(txtFirst & vbNullString) = 0 Then

MsgBox "Please enter first name "
Exit Sub

End If

If Len(txtLast & vbNullString) = 0 Then

MsgBox "Please enter last name "
Exit Sub

End If

If (chkAssociate + chkBusiness + chkFriend) = 0 Then

MsgBox "Please select at least one contact type!"
Exit Sub

End If

If Len(txtEmail & vbNullString) > 0 Then

If Len(txtEmail & vbNullString) < 3 Then
MsgBox "Please enter a valid email address!"
Exit Sub
End If
End If

 

[bob fitz]

Is the code you posted in post #3 also in the Forms Before update event with the code I suggested in post #2

 

[kroeger]

I tried your code in the before update event with the rest of the code in the click event of the command button. I then tried moving all of the code to the before update event and it did nothing. I also have three SQL insert statements in the click event. should I move these as well?

 

[kroeger]

As an aside, I have the same code attached to the before update event of a bound form which works correctly, using the cancel = true instead of the exit sub each time. I wonder if the problem is because the form is unbound?

 

[bob fitz]

I've just been reading through this post again and have just realized that in post #1 you say the form is unbound. Why are you using an unbound form.

 

[kroeger]

Hi, the form is to enter new contacts. I am using three tables and the reason I went with an unbound form is that I could not get the foreign keys written to the other two tables on update without inserting them manually. The only way I could get it to work automatically was to use a bound form with a sub form. I do not like the layout of the sub form as I just wanted a basic form with text boxes for the inputs. I am probably highlighting my lack of knowledge here. I am open to a better way of doing this.

 

[bob fitz]

What is your objection to sub forms

 

[kroeger]

no objection just limited knowledge of them and formatting them. I will have a go at using them and see how I get on. Thanks.

 

[bob fitz]

Using sub forms is the usual method.
Using bound forms will make development of the db much simpler.
Form validation should be done in the forms Before UpDate event which can be cancelled if the validation fails, but this event does not fire if the form is unbound. (an example of how things start to complicate when not using bound forms) Let us know if you need any assistance.

 

[kroeger]

Thanks again. Thats useful to know.

 

[spikepl]

If you have a problem with an expression, then the thing to do is to check the components of the expression, one by one. You cannot construct a jumbojet by putting all the componenets together and hope that the things flies - you test each component on its own prior to assembling them.

 

[kroeger]

Hi, I have created a form with sub form and added navigation controls as well as save and delete buttons etc. It all works fine. The only issue I have now is that the subforms are in datasheet view and I cannot seem to change that. Also could you please advise on how to reference controls on a sub form. When I want to validate the three checkboxes in one of my subforms would I still refer to them in the vba code as Me.chk1 in the before update event if the checkbox is called chk1?

 

[bob fitz]

I repeat: The validation should be done in the FORMS Before Update event. Also, the "Save" button in not needed on a bound form. Access will try to save any record (new or edited) automatically.

 

[kroeger]

Hi Bob, that is where I want to validate the checkboxes, but do not know how to properly access them. If I have a main form then sub form then a check box called chk1 on the first sub form would the syntax be like this in the before update event?:

if (Me!Subform1.Form!chk1 = 0) then
msgbox "You need to select the check box"
cancel = true
end if

I have tried this and it displays the message box, but the check box seems to be locked after this and I am unable to select it!!!!

 

[kroeger]

Sorry for bumping this but I would really appreciate any advice

 

[bob fitz]

No. The validation code should be in the sub forms Before UpDate event because that's the form being validated, so the reference to the control would be:

If (Me.chk1 = 0) Then
  msgbox "You need to select the check box"
  cancel = true
End If

Sorry for taking so long to reply

 

[kroeger]

thanks, I did not think of the sub-forms event.

 

[kroeger]

Hi again. I have just put the validation statement in the sub-form before update event and it totally ignores it. I click the insert record button, enter details without checking a check box, then click the next button and nothing happens in regards to validation. This is really frustrating!!!

Private Sub Form_BeforeUpdate(Cancel As Integer)

If (Me.chkAssociate + Me.chkBusiness + Me.chkFriend) = 0 Then

MsgBox "Please select at least one contact type"

Cancel = True
End If

End Sub