Re: Need help with "Binary field replaces 7 individual checkboxes"

Marshall,

thanks for the reply... ok, my goal is not to compress data. I simply want
to reduce the number of required fields from 7 to 1 (as part of streamlining
the apps).

Okay, I replaced my current function w/ yours... the "Invalid Null" error
disappeared.

But it doesn't save the checked checkboxes into my field "SecondaryTOA".
On reopening the form, all checks have been unset again.

How do I make sure to save those checks into the field?

Tom





"Marshall Barton" <marshbarton@xxxxxxxxxx> wrote in message
news:an25u1pti173vrcepjcd0gq8s2psc0sqph@xxxxxxxxxx
Tom wrote:
I have a form that contains 7 checkboxes. All of the checkboxes cover
the
"same subject". Currently, all of them are linked to their own field in
a
table.

I want to come up w/ a "smart way" (modify table/form) so that I only have
one field that stores any combination of the up to 7 values.

So, instead of having fields

Check1, Check2, Check3, ..., Check7 with values
[True], [False], [False], ..., [True]

I want to have a single field like e.g. "CheckAll" that stores a FieldId
(e.g. [1], [2], [3], [4], [5], [6], [7]) such as 1,2,3,7

I have done some research and came up the "concept" of using a binary
field... Unfortunatly, I'm unable to integrate this method into my
form...
specifically as it relates to saving the on/off values of the 7 checkboxes
into a single field.

Again, here's what I found:

Using the binary process, I'd store any combination of the 7
checked/unchecked checkboxes. For instance:

Check is on 1, 2, 4, 7 -- binary value equals "1001011"
Check is on only 5 -- binary value equals "0010000"
Check is on 1, 6 -- binary value equals "0100001"

I then came across the function below....

&&&&&&&&&&&&&&&&&

Dim i As Long
Dim SecondaryTOA As Integer

For i = 0 To 6
If Me.Controls("Check" & (i + 1)) Then
SecondaryTOA = SecondaryTOA Or 2 ^ i
End If
Next
&&&&&&&&&&&&&&&&&


Now, here's what I have in my testing db:
- Table with field "SecondaryTOA" (Integer)
- Form with 7 checkboxes... their control names are Check1, Check2,
Check3,
.... Check7
- Currently, the 7 checkboxes are unbound

If I can simply plug in this For loop into my form, how do I modify my
checkboxes? More specifcally,
- do the 7 checkboxes remain "unbound"?
- do I need to use 7 individual "AfterUpdate" events?, or
- do I plug this function into the OnCurrent event? Currently, I'm
getting
an error = "Invalid use of Null"
- if the above won't work for this approach, what other method could I use
to get this to work?


With todays large disks and memory there is no real need to
go through this kind of data compression, so I suggest that
you reconsider the whole concept.

If you really want to know, then you can set the unbound
check boxes in the form's Current event using something
along the lines of this air code:

For i = 0 To 6
Me("Check" & i+1) = CBool(Nz(SecondaryTOA, 0) And 2 ^ i)
Next i

--
Marsh
MVP [MS Access]


.

Relevant Pages