Need to get VBA code to tell which checkboxes are selected on form

[Guest]

Good afternoon,

I am using a VBA module to open an Access form and offer several check boxes
to offer a user to select which documents they would like to print, but I
need to know what VBA commands to use that will tell if a check box is
checked or not. I tried using an If statement like: (Note check29 is name of
checkbox on form)

If check29 = True Then
Debug.Print "Print 29"
Else
End If

And procedure will compile and run, but won't print anything in the
immediate window. I tried to also use a boolean variable and set variable =
check29 and used that in If statement but still no difference. Should I use a
Me.check29 statement instead? If so, I am not sure how to do this.

Any suggestions? Thanks.

Cordially,

 

[Guest]

I forgot to ask. I also need to now how to reference the form in my VBA
module. I defined a variable "CurrForm as Form" to use to reference my
form but I am notsure how to do that, and how to refernce any text boxes,
etc. on that form. If I can figure that out, I think I will know how
to check if the boxes are checked.

 

[Terrell Miller]

Good afternoon,

I am using a VBA module to open an Access form and offer several check boxes
to offer a user to select which documents they would like to print, but I
need to know what VBA commands to use that will tell if a check box is
checked or not. I tried using an If statement like: (Note check29 is name of
checkbox on form)

If check29 = True Then
Debug.Print "Print 29"
Else
End If

an Access checkbox has three possible values: Unselected = Null (if you
have never touched the checkbox since the current record comes into
focus), Selected = -1 (not 1 as you may expect), and Deselected = 0 (if
you check it and then remove the check by clicking again).

So your code shown above runs, but the value of your checkbox is never
True, it's always an integer. So the debug.print never fires.

NB: the Yes/No field type in an Access table and a checkbox on a form
are *not* the same and don't have the same values. This becomes very
important when you export an Access recordset into another application.
Some can handle the Yes/No format, but others like SAS don't know what
you're talking about. So we always use an Integer field linked to a
hidden textbox on the form (because we want to translate to Checked = 1,
Unckecked = 0 for statistical analysis purposes, but there's no way to
get a 1 value from an Access checkbox, so we have to cheat a little).
That textbox's value is programmatically set based on teh value of the
visible, unbound checkbox that the user interacts with. Typically that's
with an _AfterUpdate handler on teh checkbox.

I use Public integer constants called iChkSelected, iChkDeselected, and
iChkNull, assigning the proper numeric values as the user interacts with
the form. Then when I need to test the value of the checkbox, I do
something like this:

With Me
Select Case .chkMyCheckbox.Value
Case iChkNull, iChkDeselected: .txtMyCheckbox.Value = 0
Case iChkSelected: .txtMyCheckbox.Value = 1
End Select
End With

You could do the same thing by multiplying the value of the checkbox by
-1 and dumping that in the textbox instead of using this Select Case
structure. Either way should work fine. I use this because it's a little
more self-documenting.

You just have to make sure that you have a _Current event handler on
your form that takes the incoming value of the hidden textbox and uses
that to set the value of the visible checkbox. Otherwise when you
navigate from record to record you would retain whatever state the
checkbox happens to be in at the time, regardless of what's in the
actual field in the table.

HTH,

--
Terrell Miller
(e-mail address removed)

"Every gardener knows nature's random cruelty"
-Paul Simon RE: George Harrison

 

[Tim Ferguson]

I need to know what VBA commands to use that will tell if a
check box is checked or not. I tried using an If statement like: (Note
check29 is name of checkbox on form)

If check29 = True Then
Debug.Print "Print 29"
End If

And procedure will compile and run, but won't print anything in the
immediate window.

Hmm: where is this code running and how are you calling it and how are
you opening the form?

If the code is in the form's own module, then this should work fine. I
would prefix it with the Me object

If Me!Check29

but it works quite happily without. In VBA, comparing anything with TRUE
is true if it's non-zero; only zero = FALSE. Any control value, though,
can be NULL, so you need to make sure that your control is not grey.

If you are running the code in a module, then you'll need to reference
the control properly:

If forms("MyForm").Controls("check29) = True Then...

Finally, check that the code is being called at all:

If check29 = True then
debug.print "29 is true"

Elseif check29 = False
debug.print "29 is false"

Else
debug.print "29 is neither true nor false"

End if

because if the control is null, it'll fail =True and =False.

I forgot to ask. I also need to now how to reference the form in my
VBA module. I defined a variable "CurrForm as Form" to use to
reference my form but I am notsure how to do that, and how to refernce
any text boxes, etc. on that form. If I can figure that out, I
think I will know how to check if the boxes are checked.

See above. You can use a variable if you want:

dim frm as Form

set frm = Forms("MyForm")

If frm!check29 Then ...


The Me object is a shorthand for the form that contains the module that
contains the code that the Me appears in... IYSWIM

Hope it helps


Tim F