One piece of code to work for multiple check boxes

[mlkiser via AccessMonster.com]

I am working on some checklists (7 versions to be exact). Each of these
checklists has a butt load of checkboxes. They are set up in pairs. For each
pair, only one of the checkboxes can (well, currently, should) be checked. So,
if you check one, the other should uncheck, and vice-versa. I can do it with
code, it is fairly simple. Here is a sample of one checkbox in one pair on
one checklist:

Private Sub Check_250w_1_AfterUpdate()
If Me!Check_250w_1 Then
Me!Check_250w_2 = False
cl_250w_2 = 0
End If
End Sub

If they check box Check_250w_1, then check box Check_250w_1 will uncheck and
the corresponding table value (cl_250w_2) is set to zero.

Now, like I said, there are a ton of these check box pairs. What I would like
to do is basically write this like a SAS macro such that, when a box is
checked or unchecked, the check box name is passed to the macro. That way,
one piece of code like that above would work for every checkbox. I want it to
look something like:

Private Sub Checkbox_AfterUpdate(strCheckBox)
If Me!strCheckBox_1 Then
Me!strCheckBox_2 = False
End If
End Sub

I am assuming this has to be a module so that I can call it. But how do I
reference the current checkbox name? For instance, I know the statement:

strFrom=me.form.name

would give me the name of the form in the var strFrom. But can you refer to a
checkbox name in the same way? Something like me.form.checkbox? I know that
doesn’t work, I tried. But I am wondering if there is a way to refer to the
name of the checkbox in that manner?

 

[RBear3]

Sounds like you should be using radio buttons instead of checkboxes.

Or, perhaps you should have a one-to-many relationship set up where you only
have "true" values created in your related tables. For example...

TblPerson
PersonNumber
PersonFirstName
PersonLastName
etc.

TblPersonSkills
PersonNumber
Skill

Each person would have one record in the person table. They would have
multiple entries (one for each skill) in the Skills table.

For example, John can weld and saw and wire. Phil can weld and drill. Matt
can Drill and saw.

In short, if you have "tons of checkboxes" then your table is probably not
built to work well with Microsoft Access.

 

[mlkiser via AccessMonster.com]

I understand what you are saying and agree. Unfortunately this is a
conversion from a FoxPro database and the table layout is what I am stuck
with. I suppose I could attempt to sell the "owner" on a full restructuring
of the DB. It may end up saving time and effort, and improve efficiency, in
the long run.

Sounds like you should be using radio buttons instead of checkboxes.

Or, perhaps you should have a one-to-many relationship set up where you only
have "true" values created in your related tables. For example...

TblPerson
PersonNumber
PersonFirstName
PersonLastName
etc.

TblPersonSkills
PersonNumber
Skill

Each person would have one record in the person table. They would have
multiple entries (one for each skill) in the Skills table.

For example, John can weld and saw and wire. Phil can weld and drill. Matt
can Drill and saw.

In short, if you have "tons of checkboxes" then your table is probably not
built to work well with Microsoft Access.

I am working on some checklists (7 versions to be exact). Each of these
checklists has a butt load of checkboxes. They are set up in pairs. For

[quoted text clipped - 43 lines]

the
name of the checkbox in that manner?

 

[George Nicholson]

Clicking the control could check it or uncheck it. You can't assume one or
the other, but you don't want to get bogged down with unnecessary testing if
you can avoid it. Just set the "other" control to the opposite value of the
just-changed control.

Private Sub chkBox_1_AfterUpdate()
Call UpDateCheckboxes("chkBox_1")
'or, the answer to the question that you actually *did* ask
'Call UpDateCheckboxes(ActiveControl.Name)
End Sub

Private Sub UpDateCheckboxes(strCheckBox as string)
Select Case strCheckbox
Case "chkBox_1"
Me.Controls("chkBox_2") = Not Me.Controls("chkBox_1")
Case "chkBox_2"
Me.Controls("chkBox_1") = Not Me.Controls("chkBox_2")
................
................
Case Else
"MsgBox "Error: Unexpected Checkbox Name"
End Select
End Sub

Alternate approach: You could store the name of the "mirror" control in the
Tag property of its opposite. Then you could simplify your code down to
something like:

'(Assumes chkBox_1.Tag = "chkBox_2")

Private Sub UpDateCheckboxes(strCheckBox as string)

Dim ctl as Control
Set ctl = Me.Controls(strCheckbox)

Me.Controls(ctl.Tag) = Not ctl

End Sub


HTH,

 

[mlkiser via AccessMonster.com]

Thanks for your help. I will use this. But as a "sub" question, I will still
need to have an event for each check box using the method you described.
There is no way to do it such that I can call the same event for every
checkbox and then call UpDateCheckboxes using the AcitveControl.Name as the
passed parameter? In other words, no matter what box I check or uncheck, it
would call something like this:

Private Sub Anycheckbox_AfterUpdate()
Call UpDateCheckboxes(ActiveControl.Name)
End Sub

? That is what I was really hoping to be able to do, have one procdure that
would be used for every case. Or does Access just not work that way, where
you can have one Subroutine called by multiple events?

Clicking the control could check it or uncheck it. You can't assume one or
the other, but you don't want to get bogged down with unnecessary testing if
you can avoid it. Just set the "other" control to the opposite value of the
just-changed control.

Private Sub chkBox_1_AfterUpdate()
Call UpDateCheckboxes("chkBox_1")
'or, the answer to the question that you actually *did* ask
'Call UpDateCheckboxes(ActiveControl.Name)
End Sub

Private Sub UpDateCheckboxes(strCheckBox as string)
Select Case strCheckbox
Case "chkBox_1"
Me.Controls("chkBox_2") = Not Me.Controls("chkBox_1")
Case "chkBox_2"
Me.Controls("chkBox_1") = Not Me.Controls("chkBox_2")
................
................
Case Else
"MsgBox "Error: Unexpected Checkbox Name"
End Select
End Sub

Alternate approach: You could store the name of the "mirror" control in the
Tag property of its opposite. Then you could simplify your code down to
something like:

'(Assumes chkBox_1.Tag = "chkBox_2")

Private Sub UpDateCheckboxes(strCheckBox as string)

Dim ctl as Control
Set ctl = Me.Controls(strCheckbox)

Me.Controls(ctl.Tag) = Not ctl

End Sub

HTH,

I am working on some checklists (7 versions to be exact). Each of these
checklists has a butt load of checkboxes. They are set up in pairs. For

[quoted text clipped - 43 lines]

the
name of the checkbox in that manner?

 

[Douglas J. Steele]

Make UpdateCheckboxes a function, not a sub, and don't pass it any
arguments. Inside the function, refer to Screen.ActiveControl to get a
reference to the check box that was checked (or Screen.ActiveControl.Name to
get its name), and have it do whatever you want.

Now, select all of the checkboxes which need this functionality (you can do
this by lassoing them, or by clicking on each one while holding down the
shift key). While they're all selected, go to the Properties window, and put

=UpdateCheckboxes()

as the AfterUpdate property (include the equals sign and the parentheses)
That function will now fire after any of those checkboxes are updated.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for your help. I will use this. But as a "sub" question, I will
still
need to have an event for each check box using the method you described.
There is no way to do it such that I can call the same event for every
checkbox and then call UpDateCheckboxes using the AcitveControl.Name as
the
passed parameter? In other words, no matter what box I check or uncheck,
it
would call something like this:

Private Sub Anycheckbox_AfterUpdate()
Call UpDateCheckboxes(ActiveControl.Name)
End Sub

? That is what I was really hoping to be able to do, have one procdure
that
would be used for every case. Or does Access just not work that way, where
you can have one Subroutine called by multiple events?

Clicking the control could check it or uncheck it. You can't assume one or
the other, but you don't want to get bogged down with unnecessary testing
if
you can avoid it. Just set the "other" control to the opposite value of
the
just-changed control.

Private Sub chkBox_1_AfterUpdate()
Call UpDateCheckboxes("chkBox_1")
'or, the answer to the question that you actually *did* ask
'Call UpDateCheckboxes(ActiveControl.Name)
End Sub

Private Sub UpDateCheckboxes(strCheckBox as string)
Select Case strCheckbox
Case "chkBox_1"
Me.Controls("chkBox_2") = Not Me.Controls("chkBox_1")
Case "chkBox_2"
Me.Controls("chkBox_1") = Not Me.Controls("chkBox_2")
................
................
Case Else
"MsgBox "Error: Unexpected Checkbox Name"
End Select
End Sub

Alternate approach: You could store the name of the "mirror" control in
the
Tag property of its opposite. Then you could simplify your code down to
something like:

'(Assumes chkBox_1.Tag = "chkBox_2")

Private Sub UpDateCheckboxes(strCheckBox as string)

Dim ctl as Control
Set ctl = Me.Controls(strCheckbox)

Me.Controls(ctl.Tag) = Not ctl

End Sub

HTH,

I am working on some checklists (7 versions to be exact). Each of these
checklists has a butt load of checkboxes. They are set up in pairs. For

[quoted text clipped - 43 lines]

the
name of the checkbox in that manner?

 

[Chris2]

Now, like I said, there are a ton of these check box pairs. What I would like
to do is basically write this like a SAS macro such that, when a box is
checked or unchecked, the check box name is passed to the macro.

mlkiser,


It sounds like you wish to programmatically access and control the
"Controls" on each form.

This can be done.

Here is an example of how to print out (in the Immediate Window) the
names of every control on a particular form.

I created a form named Form4, and dropped 4 check-boxes (with their
attendant lables) onto the form.

(This code was executed in a Module, and so refers to the form
directly.)


Public Sub sForm4_ControlsSearch()

Dim cnt As Control
Dim cntColl As Controls

Set cntColl = Form_Form4.Controls

For Each cnt In cntColl

Debug.Print cnt.Properties("Name")

Next

End Sub


Now, that should give you some ideas about how to manipulate Controls
en-masse, like:


IF cnt.properties("Name") = "Check_" & strControlName THEN

'Some code like:

IF me.Controls.Item("Check_" & strControlName) THEN

me.Controls.Item("Check_" & strControlName) = False

END IF

ELSE

'Other code

END IF.


The variable strControlName is assembled by you according to your
control's naming rules.

If you want, you could also iterate through the forms collection, and
then iterate each form's controls collection (two loops), all from one
routine.


There, hopefully those examples will get your thoughts flowing.

Add error handling, adapt to your purposes, etc.


Sincerely,

Chris O.

 

[mlkiser via AccessMonster.com]

Awesome guys, thanks for all the help!

<snip>

mlkiser,

It sounds like you wish to programmatically access and control the
"Controls" on each form.

This can be done.

Here is an example of how to print out (in the Immediate Window) the
names of every control on a particular form.

I created a form named Form4, and dropped 4 check-boxes (with their
attendant lables) onto the form.

(This code was executed in a Module, and so refers to the form
directly.)

Public Sub sForm4_ControlsSearch()

Dim cnt As Control
Dim cntColl As Controls

Set cntColl = Form_Form4.Controls

For Each cnt In cntColl

Debug.Print cnt.Properties("Name")

Next

End Sub

Now, that should give you some ideas about how to manipulate Controls
en-masse, like:

IF cnt.properties("Name") = "Check_" & strControlName THEN

'Some code like:

IF me.Controls.Item("Check_" & strControlName) THEN

me.Controls.Item("Check_" & strControlName) = False

END IF

ELSE

'Other code

END IF.

The variable strControlName is assembled by you according to your
control's naming rules.

If you want, you could also iterate through the forms collection, and
then iterate each form's controls collection (two loops), all from one
routine.

There, hopefully those examples will get your thoughts flowing.

Add error handling, adapt to your purposes, etc.

Sincerely,

Chris O.

 

[mlkiser via AccessMonster.com]

Bah, I'm still stuck (but nearly there). Sorry for being such a newb, but
dangit, I'm going to get this to work if it kills me!

Here is the code I wrote (well, you wrote and I modified). It works as an
Event, but not as a Function:

Function CheckBoxUpdate()

Dim strCheckbox As String
Dim strBox As String
Dim strOppBox As String

strCheckbox = ActiveControl.name
strBox = Left(strCheckbox, (Len(strCheckbox) - 1))

If Right(strCheckbox, 1) = "1" Then
strOppBox = strBox & "2"
Else:
strOppBox = strBox & "1"
End If

If Me.Controls.Item(strCheckbox) Then
Me.Controls.Item(strOppBox) = False
End If

End Function

It is choking on the line:

If Me.Controls.Item(strCheckbox) Then

I am getting a compile error on Use of the Me Keyword. I guess Me isn't
recognized in the function like it would be in an event procedure. Or in a
Module for that matter. What do I need to substitute for Me in this instance?
I have tried several different things to no avail.

 

[Douglas J. Steele]

Try

If Me.Controls(strCheckbox) Then
Me.Controls(strOppBox) = False
End If

or

Me.Controls(strOppBox) = Not Me.Controls(strCheckbox)

 

[mlkiser via AccessMonster.com]

Nope. It still chokes on the "Me" keyword.

Try

If Me.Controls(strCheckbox) Then
Me.Controls(strOppBox) = False
End If

or

Me.Controls(strOppBox) = Not Me.Controls(strCheckbox)

Bah, I'm still stuck (but nearly there). Sorry for being such a newb, but
dangit, I'm going to get this to work if it kills me!

[quoted text clipped - 32 lines]

instance?
I have tried several different things to no avail.

 

[Douglas J. Steele]

It would be a lot easier if you didn't trim so much....

Where did you put the function: in a stand-alone module, or in the module
associated with the form? You can only use Me in the latter case.

If it's a stand-alone module, try:

Function CheckBoxUpdate()

Dim strCheckbox As String
Dim strBox As String
Dim strOppBox As String
Dim strParent As String

strCheckbox = ActiveControl.name
strParent = ActiveControl.Parent.Name
strBox = Left(strCheckbox, (Len(strCheckbox) - 1))

If Right(strCheckbox, 1) = "1" Then
strOppBox = strBox & "2"
Else:
strOppBox = strBox & "1"
End If

If Forms(strParent).Controls(strCheckbox) Then
Forms(strParent).Controls(strOppBox) = False
End If

End Function

Note that this will not work properly if the form in question is a subform:
more work will be required.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Nope. It still chokes on the "Me" keyword.

Try

If Me.Controls(strCheckbox) Then
Me.Controls(strOppBox) = False
End If

or

Me.Controls(strOppBox) = Not Me.Controls(strCheckbox)

Bah, I'm still stuck (but nearly there). Sorry for being such a newb,
but
dangit, I'm going to get this to work if it kills me!

[quoted text clipped - 32 lines]

instance?
I have tried several different things to no avail.

 

[George Nicholson]

Me should work *if* the code is in the code module of a class (form or
report).

I'm going to guess that your function is in a General module, where Me would
be meaningless.

HTH,

Nope. It still chokes on the "Me" keyword.

Try

If Me.Controls(strCheckbox) Then
Me.Controls(strOppBox) = False
End If

or

Me.Controls(strOppBox) = Not Me.Controls(strCheckbox)

Bah, I'm still stuck (but nearly there). Sorry for being such a newb,
but
dangit, I'm going to get this to work if it kills me!

[quoted text clipped - 32 lines]

instance?
I have tried several different things to no avail.

 

[mlkiser via AccessMonster.com]

** DUH ** Yes, that was it and yes, it was that simple! I moved it on to the
form page and wallah, success. Thanks to all!

Me should work *if* the code is in the code module of a class (form or
report).

I'm going to guess that your function is in a General module, where Me would
be meaningless.

HTH,

Nope. It still chokes on the "Me" keyword.

[quoted text clipped - 14 lines]

 

[Chris2]

Bah, I'm still stuck (but nearly there). Sorry for being such a newb, but
dangit, I'm going to get this to work if it kills me!

Here is the code I wrote (well, you wrote and I modified). It works as an
Event, but not as a Function:

Function CheckBoxUpdate()

Dim strCheckbox As String
Dim strBox As String
Dim strOppBox As String

strCheckbox = ActiveControl.name
strBox = Left(strCheckbox, (Len(strCheckbox) - 1))

If Right(strCheckbox, 1) = "1" Then
strOppBox = strBox & "2"
Else:
strOppBox = strBox & "1"
End If

If Me.Controls.Item(strCheckbox) Then
Me.Controls.Item(strOppBox) = False
End If

End Function

It is choking on the line:

If Me.Controls.Item(strCheckbox) Then

I am getting a compile error on Use of the Me Keyword. I guess Me isn't
recognized in the function like it would be in an event procedure. Or in a
Module for that matter. What do I need to substitute for Me in this instance?
I have tried several different things to no avail.

--

mlkiser,

A compile error on "Me"?

Let me guess, your function is not on the code-page for the form, but is located in a
module, right?

"Me" is probably out-of-scope inside your function.


In the event code, you would add:


Dim strFormName as string

strFormName = me.name

YourFunctionName(strFormName)


In the function, do something like:


Dim frm As Form


Set frm = Forms.Item(strYourFunctionsReceivingVariableName)

With frm.Controls

If .Item("Check2") Then

.Item("Check2") = False

Else

.Item("Check2") = True

End If

End With


That should allow you to refer to the form where the function was last called.


Sincerely,

Chris O.

 

[Klatuu]

One of the earliest response gave you what I think is the better approach.
That is using radio buttons, or option buttons, or if you must check boxes in
an option group. I understand this is a conversion from FoxPro, but that
should not require any change in the table.
Each option group would be bound to a field in the form's recordset. The
option group would have two option buttons and each of those buttons will
return the correct value to correspond to the value the field is expecting
for the condition.
--
Dave Hargis, Microsoft Access MVP

Nope. It still chokes on the "Me" keyword.

Try

If Me.Controls(strCheckbox) Then
Me.Controls(strOppBox) = False
End If

or

Me.Controls(strOppBox) = Not Me.Controls(strCheckbox)

Bah, I'm still stuck (but nearly there). Sorry for being such a newb, but
dangit, I'm going to get this to work if it kills me!

[quoted text clipped - 32 lines]

instance?
I have tried several different things to no avail.

 

[mlkiser via AccessMonster.com]

I see what you mean. I played with Option Groups and see where this is a much
better option. Let me ask you this. If I were to use check boxes, currently
there is a value stored for each checkbox in each "group" pair. i.e.
Check_250_1 and Check_250_2. I assume if I use the Option Group, there will
only be one value for this group, i.e. Check_250, and it would be set to 1 or
2 to indicate the selected box, correct? If so, is there an option to have
neither box checked? Or must one always be selected?

One of the earliest response gave you what I think is the better approach.
That is using radio buttons, or option buttons, or if you must check boxes in
an option group. I understand this is a conversion from FoxPro, but that
should not require any change in the table.
Each option group would be bound to a field in the form's recordset. The
option group would have two option buttons and each of those buttons will
return the correct value to correspond to the value the field is expecting
for the condition.

Nope. It still chokes on the "Me" keyword.

[quoted text clipped - 13 lines]

 

[Klatuu]

It is possible, but the easier way would be to add a 3rd button to the group.
You can set it up so the default value for the option group is Null, but
then once you select a value, you can't get it back to Null.
--
Dave Hargis, Microsoft Access MVP

I see what you mean. I played with Option Groups and see where this is a much
better option. Let me ask you this. If I were to use check boxes, currently
there is a value stored for each checkbox in each "group" pair. i.e.
Check_250_1 and Check_250_2. I assume if I use the Option Group, there will
only be one value for this group, i.e. Check_250, and it would be set to 1 or
2 to indicate the selected box, correct? If so, is there an option to have
neither box checked? Or must one always be selected?

One of the earliest response gave you what I think is the better approach.
That is using radio buttons, or option buttons, or if you must check boxes in
an option group. I understand this is a conversion from FoxPro, but that
should not require any change in the table.
Each option group would be bound to a field in the form's recordset. The
option group would have two option buttons and each of those buttons will
return the correct value to correspond to the value the field is expecting
for the condition.

Nope. It still chokes on the "Me" keyword.

[quoted text clipped - 13 lines]

instance?
I have tried several different things to no avail.

 

[mlkiser via AccessMonster.com]

That's a shame. Seems they would have written it such that you could use the
value 0, 1 or 2 such that 0 meant no selection, 1 meant one box and two meant
the other. I will need to see if my user wants the ability for neither box to
be checked.

It is possible, but the easier way would be to add a 3rd button to the group.
You can set it up so the default value for the option group is Null, but
then once you select a value, you can't get it back to Null.

I see what you mean. I played with Option Groups and see where this is a much
better option. Let me ask you this. If I were to use check boxes, currently

[quoted text clipped - 17 lines]

 

[Klatuu]

Ok, good luck.
--
Dave Hargis, Microsoft Access MVP

That's a shame. Seems they would have written it such that you could use the
value 0, 1 or 2 such that 0 meant no selection, 1 meant one box and two meant
the other. I will need to see if my user wants the ability for neither box to
be checked.

It is possible, but the easier way would be to add a 3rd button to the group.
You can set it up so the default value for the option group is Null, but
then once you select a value, you can't get it back to Null.

I see what you mean. I played with Option Groups and see where this is a much
better option. Let me ask you this. If I were to use check boxes, currently

[quoted text clipped - 17 lines]

instance?
I have tried several different things to no avail.