How to validate multiple checkboxes in Access?

[IntoTheVoid]

Hi, for some reason the following VBA code is not working. I have three check boxes and at least one of them must be selected:

If (chk1 + chk2 + chk3) = 0 Then

MsgBox "Please select at least one check box!"
Exit Sub

End If

Any ideas appreciated, this is driving me up the wall as it works on another form in the Before Update event using Cancel = True.

the above code is in the click event of a command button on an unbound form.

 

[spydermixx]

Been a while, but I think it's:

If (chk1 = 0 And chk2 = 0 And chk3 =0) Then

 

[IntoTheVoid]

Just tried it and it seems to work. Thanks a lot, saved me loads of time

 

[dazzerd]

Just tried it and it seems to work. Thanks a lot, saved me loads of time

Just to be really anal but you should probably qualify the property properly so use .value after the control names.

I spend a lot od my time debugging access databases and it is a pet hate of mine.

Another tip you could actually stick it all on one line:

So If check1.value = 0 and check2.value = 0 and check3.value = 0 then msgbox "Stuff" :exit sub

Code like validating controls and stuff can look a lot cleaner when you put the expression in one statement imo. Other people hate it!

You could also just hold the checkboxes in an option group and then you just test the value of the option group for Null.

 

[IntoTheVoid]

hi thanks for the tips. I have now decided to use a bound form with a sub form. The sub form has the three check boxes now. Do you know how to refer to these on a subform? I know that the main form would be: Me.chk1 ....... but I can see no way to reference a sub form control.

 

[dazzerd]

Hmm it should just be me."subFormName".check1

so if you subform name is child0 which it defaults too then use

me.child0.check1.value

 

[IntoTheVoid]

hi I will give that a go. thanks.

 

[jsmoke]

Been ages but I think you will need something like, checkbox1.value or checkbox1.selected. And use logical 'And' not the binary operator +.

 

[IntoTheVoid]

hi , well I seem to be accessing the control as the message box is displaying when I do not have a check box selected. However after dismissing the messagebox the check box seems to be locked and will not allow me to click it!!!!!! All of the allow edits etc options are set to yes and the control is unlocked. Any ideas?

 

[dazzerd]

hi , well I seem to be accessing the control as the message box is displaying when I do not have a check box selected. However after dismissing the messagebox the check box seems to be locked and will not allow me to click it!!!!!! All of the allow edits etc options are set to yes and the control is unlocked. Any ideas?

Has the sub form become disabled or locked?

Pretty hard to tell what is going on without a copy ot the db to play around with?

 

[IntoTheVoid]

Hi, here is a stripped out copy of the database. Would appreciate any help:

https://www.dropbox.com/s/6cvf2f0jr155ry8/contacts.accdb

 

[dazzerd]

Sorry just took a look. Seems to be working OK for me. Under what circumstances are you actually seeing this locking behaviour?

Also just noticed this is macro driven, I can't see your validation code at all?

 

[IntoTheVoid]

sorry must have uploaded the version without the vba in. The code was in the Before Update event of the main form:

If Me!frmContactType.Forms!chkAssociate= 0 And Me!frmContactType.Forms!chkBusiness = 0 And Me!frmContactType.Forms!chkFriend = 0 Then

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

End If

 

[dazzerd]

OK I'll try and take another look this weekend.

 

[IntoTheVoid]

Thanks. I have sorted it now. I am using the Nz function to do the comparison for null value now i.e
if Nz (Me!frmContactType.Forms!chkAssociate, 0) = 0 then
msgbox "Please select a contact type"
cancel = true
end if

Thanks anyway.