How to validate that at least one checkbox is selected

[kroeger]

perhaps a screenshot may help:

https://www.dropbox.com/s/wn1n8e326uns22o/accessform.jpg

 

[bob fitz]

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

That's not what I posted. Try:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.chkAssociate = 0 Or Me.chkBusiness =0 Or Me.chkFriend = 0 Then

MsgBox "Please select at least one contact type"

Cancel = True
End If

 

[kroeger]

Sorry Bob, I have tried the code you posted and when I click the next button without selecting a check box nothing prompts me to do so. It is not grumbling about finding the control so it must be accessing it ok? sorry to take your time like this. I have been stuck for several days now on this. There is no validation code anywhere apart from where you suggest.

 

[bob fitz]

Can you post a copy of the db in A2003 mdb format for me to play with

 

[kroeger]

will try although I cannot see an option to save in that format. using 2010 version

 

[spikepl]

Well ... you got advice in post #13 that you ignored. Instead of remaining stuck, CHECK EACH BIT! Follow the debugging tips here: http://www.access-programmers.co.uk/forums/showthread.php?t=149429

 

[kroeger]

Well I don't see how I ignored the advice. You cannot break it down any more than trying one if statement surely? I have googled, I have searched these forums, I have been trying different things all day today and the last few days, as well as referring to books.

 

[bob fitz]

will try although I cannot see an option to save in that format. using 2010 version

try this: http://office.microsoft.com/en-gb/a...se-in-an-earlier-file-format-HA010341553.aspx

 

[kroeger]

sorry Bob, it keeps telling me that I have objects that are not compatible with the earlier format. I have paired it down to just three tables and the main form and sub form but it still will not let me save in the older format. I have no macros either.

 

[bob fitz]

In the Before Update event of the subform try this code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.chkAssociate = 0  Then

  MsgBox "Please select at least one contact type"

  Cancel = True
End If

 

[kroeger]

Hi Bob, I have entered your code and once I select the next record after not selecting a check box the message box opens as expected. Straight after I dismiss the message another message box opens saying "You can't go to the specified record". When I close this and try to select the check box it is not clickable. It is as if the control is locked? thanks again for your time.

 

[bob fitz]

Hi Bob, I have entered your code and once I select the next record after not selecting a check box the message box opens as expected. Straight after I dismiss the message another message box opens saying "You can't go to the specified record". When I close this and try to select the check box it is not clickable. It is as if the control is locked? thanks again for your time.

The "next record" on which form.
I have been giving some more thought to this.
First. In the screen shot that you posted I can see chkAssociate but I am unable to see chkBusiness or chkFriend. Can you confirm that these are all on the same sub form.
Second. Assuming the answer to the first point is yes, are there any other controls on that sub form that the user will definitely fill in. If not the user could create a new record in the main form and then move to create another main form record without ever making the sub form dirty, in which case the sub forms Before Update event will not fire. If this is possible you would need validation code in the main form as well.

 

[kroeger]

hi Bob, there are definitely three check boxes on that sub form, the view was obscuring it. also the controls are as follows:
two text boxes on the main form
three check boxes on the first sub form (sub-form named frmContactType)
three text boxes on the second sub form (sub-form named frmCommDetail)

the two text boxes on the main form are bound to tblContact (form named frmMainContacts)
the three checkboxes on the first sub form are bound to tblContactType
the three text boxes on the second sub form are bound to tblCommDetail

The fields that the check boxes are bound to are yes/no fields in the table. The text box fields are all of type text in the table fields. The primary key field is autonumber and the foreign key fields are of the number type.

the relationships for the tables are set as:

tblContact one to many on tblContactType
tblContact one to many on tblCommDetail

the Contact_ID pk field from tblContact is a foreign key in the other two tables.

There is no table level validation in use.

I have entered your code in both the main form and the sub-forms before update event. The messagebox displays and the unspecified record message still displays, and when dismissed the check box is not selectable it appears locked still.

I really appreciate your persistence with this.

 

[bob fitz]

Can you just let me know the name of the main form and the names of the two sub form controls. The names of the subform controls on the main form may not be the same as the name of the forms they display.
I will try to look at this tomorrow evening. Have to go out now to see West Ham Utd get beaten by Arsenal

 

[kroeger]

The main form is called frmMaintainContacts, the sub-form control for the sub form with the checkboxes is frmContactType and the actual checkbox controls themselves are called chkAssociate, chkBusiness and chkFriend. good luck with the football. Arsenal have dropped off a bit lately!!! I have attached example database in 2010 format as it will not let me save as earlier version. The record operations work fine, but no validation code is in place.

 

[bob fitz]

FYI The db you posted is actually in A2003 mdb format.
Since my last post, I have been playing around with a basic db, which I thought would be similar to the db that we have been discussing, to better understand how it might work. I have attached a copy. It only has one sub form at the moment, but I don't think adding a second would present any new challenges. Take a look and see if it works as you require. If it does, perhaps you can replicate the way it works in your own db. Of course, I will do my best if you need any help with that. Let us know how you get on

 

[kroeger]

Thanks for your time. I will have a play around with it and try and learn from it.

 

[bob fitz]

Thanks for your time. I will have a play around with it and try and learn from it.

I initially posted a db called Kroeger02.mdb, then edited the post a short while ago by replacing the db with one called Kroeger03.mdb
Please ensure you are using the latest copy. Let me know if you have any questions.

 

[kroeger]

thanks I got the latest version.

 

[bob fitz]

thanks I got the latest version.

Good. Just had another thought. You may want to change the Cycle property of the sub form to Current Record.