Is it possible to attach a label to a checkbox for coding purposes

[Guest]

I'm hoping that somebody knows how to do what I'm thinking is possible in my
mind.
I have a form with 25 check boxes and labels that describe each check box.
The purpose of the form is to allow the user to create a custom query that is
then executed after the user makes all desired selections by clicking the
"Submit" button. As is, the code is pretty complex to allow this
customization, joining together about 9 different strings of code. To give a
small example of how I'm doing this, I've included the following example:

If Board_Of_Health_chk = True And SqlAdditionFac = "" Then
SqlAdditionFac = "AND (((tbl_Specialties.Specialty)='Board Of Health') "
ElseIf Board_Of_Health_chk = True And SqlAdditionFac <> "" Then
SqlAdditionFac = SqlAdditionFac & "OR
((tbl_Specialties.Specialty)='Board Of Health')) "
End If

The problem I am having is that the form is somewhat dynamic and will be
changing fairly frequently. As is, everytime I need to add, delete, or
change a customizable category (such as "Board Of Health") there are a lot of
lines of code that have to be touched because I'm using specific names such
as "Board Of Health". What would be sooooo much better would be if I could
use a generic line of code that would allow me to cycle through the check box
controls one at a time to get the data I'm looking for.

To clarify:
I'm looking for code that will look at the first checkbox control on the form;
Determine if it is checked;
If it is checked, then save the check box's label into a string variable
(which I would then be able to refer to in code and append the custom code;
Then, go to the next check box control on the form

I would want this code to loop through the check box controls until it has
evaluated each one of them (another issue is how do I get the loop to stop)

If this can be accomplished - which it seems like it should be possible,
this would save me hours upon hours of form editting time and I would be very
thankful to anyone who could help.

 

[Bill Edwards]

One way would be the following:
' This code assumes a check box is called chkName
' and it has a label called lblName. For example:
' chkBoardOfHealth -> lblBoardOfHealth
On Error GoTo Err_Label
Dim ctl As Control
Dim strLabelName As String
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl.Value = True Then
strLabelName = "lbl" & Mid$(ctl.Name, 4, Len(ctl.Name))
MsgBox Me.Controls(strLabelName).Caption
End If
End If
Next ctl
Exit_Label:
Exit Sub
Err_Label:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_Label

 

[Bill Edwards]

You could also use the Tag property of the check box to do the same thing
and then you label caption could be anything.

 

[Guest]

You are a beautiful person - thanks for the prompt response!
I have not yet got to try your suggestions, but I can see the logic behind
them and I'm really excited to try them tomorrow when I get back to the
office.
I will post my results.
Thanks again!

 

[Guest]

Thanks for the additional suggestion - I've read a little about using the tag
property with other things I've done, but I never used it because i wasn't
quite sure about the syntax at the time. If you know it off the top of your
head, please give me an example of the syntax for this tag property you've
suggested. I'm sure I could find it with a little digging, but like I said,
if you know it off the top of your head, that would be even quicker. Thanks
for all your help!

 

[Bill Edwards]

Re: the Tag Property
When you create the checkbox:
1. Give it a name -- chkBoardOfHealth
2. In the tag property (it is on the Other Tab of the properties window),
enter the field name (Board_Of_Health)
3. Create whatever label you want for the chkBoardOfHealth checkbox and
call it whatever you want
4. Use the same code to cycle through the controls:

On Error GoTo Err_Label
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl.Value = True Then
MsgBox ctl.Tag
End If
End If
Next ctl
Exit_Label:
Exit Sub
Err_Label:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_Label

 

[Guest]

Bill,
Thanks for the info on the tag property. Sorry for not posting sooner --- I
decided to go with the tag method over the other idea because it seemed more
verstile for me. I've got things working beautifully and was able to
illiminate about 100 lines of code -- thanks A LOT.