checkboxes on a form and values in a table

[Guest]

Hi all,

I am very new to access, but I do have some VBA experience.

I have created a form with 5 checkboxes that represent contact methods for
customers (Mail, phone, email, pager, other). Any combination from all blank
to all checked is possible, which is 32 distinct possibilities. Here are the
two things I want to do, but I just do not know how to accomplish them in
Access.

As the user checks the checkboxes, I will use the OnClick method to evaluate
the state of all 5 checkboxes. Based on which checkboxes are selected, I
want to write a binary value from 0 to 31 into the "contact method" field of
the current record. In VBA, I do not know how to reference the current
record, nor the current field to write the value. Can someone help with
this? The form name is frm:Master_Form, the table name is tbl:Master_Table
and the field name is Contact Method.

Secondly, the form contains the record navigation controls at the bottom.
When a user clicks on any of those controls to change records, what event can
I use to evaluate the Contact Method field for the currently selected record,
so that I can check the appropriate checkboxes? What would that code look
like?

Many thanks,
Richard

 

[Allen Browne]

Hmm. A bit field, interfaced with 5 unbound check boxes.

In Access it's probably better to use the AfterUpdate event of the text
boxes to calculate the bit value, and write to your integer field.

All references in a bound form are automatically the current record. The
field you are writing to does not have to be represented by a text box: so
long as it appears in the form's RecordSource.

When you move record, the Current event of the form fires, so that's the one
you need to use to parse the bit-field and set your text boxes. You should
also use the Undo event of the form, to reset the textboxes, based on the
OldValue of the bit-field.

 

[Marshall Barton]

I am very new to access, but I do have some VBA experience.

I have created a form with 5 checkboxes that represent contact methods for
customers (Mail, phone, email, pager, other). Any combination from all blank
to all checked is possible, which is 32 distinct possibilities. Here are the
two things I want to do, but I just do not know how to accomplish them in
Access.

As the user checks the checkboxes, I will use the OnClick method to evaluate
the state of all 5 checkboxes. Based on which checkboxes are selected, I
want to write a binary value from 0 to 31 into the "contact method" field of
the current record. In VBA, I do not know how to reference the current
record, nor the current field to write the value. Can someone help with
this? The form name is frm:Master_Form, the table name is tbl:Master_Table
and the field name is Contact Method.

It's not good relational practice to pack multiple values
into a single field. Back in the old days when memory was
the critical resource, it made more sense than it does now
that machines are so big and fast.

Regardless of that, you can use something like this air
code:

[contact method] = -chk1 -2*chk2 -4*chk3 -8*chk4 -16*chk5

where chk1, ... are the names of your check boxes.

All references to controls and fields are to values in the
current record, so there is no issue with this.

Secondly, the form contains the record navigation controls at the bottom.
When a user clicks on any of those controls to change records, what event can
I use to evaluate the Contact Method field for the currently selected record,
so that I can check the appropriate checkboxes? What would that code look
like?

Use the form's Current event:

chk1 = CBool([contact method] And 1)
chk2 = CBool([contact method] And 2)
chk3 = CBool([contact method] And 4)
chk4 = CBool([contact method] And 8)
chk5 = CBool([contact method] And 16)