Enable command button with checkboxes - help!

[hermanko]

Hi all,

I have a form based on a query that includes a Yes/No field as
checkboxes. I have a command button on the form footer that is default
at disabled when the form is opened (the checkboxes are all cleared as
well).

I need to enable the button once any checkbox is selected. The code I
have now only works partially. For example if i click any one checkbox,
the button enables fine. When i uncheck, it disables fine. The problem
occurs when I check several boxes, and as soon as I uncheck any one
box, the button will disable, even tho I still have other boxes
checked. Basically I need to fix my code so that when ANY one or more
checkbox is True i need the button to remain enabled. It doesn't sound
too hard to fix but i'm not strong with VB. My code is:

Private Sub Yes_No_AfterUpdate()
If Me![Yes/No] Then
Me!cmd_remove1.Enabled = True
Else
Me!cmd_remove1.Enabled = False
End If
End Sub

Any help would be great!
Herman

 

[Richard W. \(Rich\) McCabe via AccessMonster.com]

Have you tried using an "OR" in the if statement to check if any of the boxes
are checked?
If Check1[Yes/No] or Check2[yes/no] or Check3[yes/No] then.....
Rich

Hi all,

I have a form based on a query that includes a Yes/No field as
checkboxes. I have a command button on the form footer that is default
at disabled when the form is opened (the checkboxes are all cleared as
well).

I need to enable the button once any checkbox is selected. The code I
have now only works partially. For example if i click any one checkbox,
the button enables fine. When i uncheck, it disables fine. The problem
occurs when I check several boxes, and as soon as I uncheck any one
box, the button will disable, even tho I still have other boxes
checked. Basically I need to fix my code so that when ANY one or more
checkbox is True i need the button to remain enabled. It doesn't sound
too hard to fix but i'm not strong with VB. My code is:

Private Sub Yes_No_AfterUpdate()
If Me![Yes/No] Then
Me!cmd_remove1.Enabled = True
Else
Me!cmd_remove1.Enabled = False
End If
End Sub

Any help would be great!
Herman

 

[Barry Gilbert]

Not sure if this is the simplest solution, but you may need to walk
your displayed records and evaluate whether or not any are checked.

Something like this:

Dim rsClone As Recordset
Dim blnChecked As Boolean
Set rsClone = Me.Recordset
rsClone.MoveFirst
Do Until rsClone.EOF
If rsClone![Yes/No] Then
blnChecked=True
Exit Do
End If
rsClone.MoveNext
Loop
Me.cmd_Remove1.Enabled=blnChecked
Set rsClone=Nothing

HTH,
Barry

 

[Barry Gilbert]

This seems to be one checkbox in the detail section, so it shows up
once for each record.

 

[hermanko]

Barry,

Thank you so much for the sample code! It works out perfectly

Just one small thing. When i uncheck a box, the focus is always
shifting depending on if there are any other boxes check. I.e., if i
check the first and second box...then i decide i need to uncheck the
2nd box, the focus shifts up to the 1st box. However, if i also need to
uncheck the 1st box (leaveing all boxes clear), the focus suddenly
jumps to the last record.

I know it has something to do with how you are moving between records
in the code, but not sure how to just have the focus set on what ever
checkbox was last clicked on (no matter if it was clicked to check, or
UNcheck).

Herman

 

[hermanko]

I studied your sample code above and I know why it's doing that now,
however I dont know how to fix it.

Basically it kicks out of the Do loop as soon as it detects the first
instance where a checkbox is select, so that the focus remains set on
that first instance, no matter where it is in the recordset. Seeing as
how i could have an extremely long list of records, the user will get
quite frustrating if the focus keeps jumping all over the place while
he's checking and unchecking boxes.

Seems to me like I need a line of code to just set the focus on the
last button clicked. Can you suggestion anything? thank you!

 

[Barry Gilbert]

I found a mistake in my code. The third line should read

Set rsClone = Me.Recordsetclone

That should fix it.

Barry

 

[hermanko]

Thank you so much for your help!

I dont really understand why this works, but it works and that's all
that matter to me (for now). I guess i'll read thru the Help files and
see what i can learn, tho the best way to learn is by doing it (or
asking for help on google groups ) lol.

Herman

 

[hermanko]

I just tried playing around with checking and unchecking some of the
checkboxes, and now the functionality is not quite working. I changed
my code as per your last post, but now it doesn't enable/disable
correctly. for example, the button doesn't enable after i select my
first box (any box). it only enables if i select a second box.
Similarily, it doesn't disable when i uncheck all boxes. it only does
so after i subsequently CHECK a box.

 

[hermanko]

Currently, this VB code is under the checkbox control's AfterUpdate
event. Should it be elsewhere like on the form's OnCurrent?

 

[Barry Gilbert]

One more change:

The RecordsetClone is a copy of the form's recordset. We are traversing
the recordsetClone to see if the [Yes/No] of any record is true. The
problem is that, when the checkbox's AfterUpdate event fires, the
record has not yet been saved. To fix this, put:

Me.Dirty=False

just before Set rsClone = Me.RecordsetClone.

This will save the record and accurately reflect the new status in the
recordsetclone object.

Barry

 

[hermanko]

Now it works perfectly! Thanks so much for the prompt responses! You
have been most helpful
Herman

 

[Barry Gilbert]

You're welcome. Glad I could help.

 

[hermanko]

Hi, if anyone could help I'd really appreciate it....

My form with checkboxes and cmd buttons has evolved into 2 subforms
(each with checkboxes as a Yes/No field), and on the main form i have a
command button which is enabled only if at least one box (from either
subform) is checked.

I have used the same code as above (which i will reproduce below). One
of the subforms works perfectly, while I can't seem to even click the
boxes in the other subform. I used the exact same VB code in each
subform, under the checkbox's After Update event. Why is it not
allowing me to even select a checkbox in the other subform?

Code (used in both After Update events):

Private Sub Yes_No_AfterUpdate()
Dim rsClone As Recordset
Dim blnChecked As Boolean
Me.Dirty = False
Set rsClone = Me.RecordsetClone
rsClone.MoveFirst
Do Until rsClone.EOF
If rsClone![Yes/No] Then
blnChecked = True
Exit Do
End If
rsClone.MoveNext
Loop
[Forms]![Duplication]!cmd_remove.Enabled = blnChecked
Set rsClone = Nothing
End Sub

Thanks!
Herman