Thread: Option Group?
View Single Post
  #2  
Old December 14th, 2006, 01:03 AM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default Option Group?

You are trying to create a bitfield (i.e. a Number where each bit represents
a value)?

You can't do that with an option group, since an the group can have only one
value selected, and you want multiples. They will therefore need to be
stand-alone check boxes (placed in a Rectangle if you wish.)

Strictly a bitfield is not normalized. It might be better to create a
related table with a one-to-many relationship, so one record in your main
table can have many options (as different records) in a related table. It
will almost certainly be faster to query that way.

If you want to proceed with the bitfield, use stand-alone check boxes (not
in option group) that are unbound (not bound to any field.) You can then
write code for 3 events in your form to handle this:
a) Use the Current event of the field to examine the bits of the number
field, and populate the check boxes.

b) Use the AfterUpdate event of each check box to update the value of the
(hidden) number field.

c) Use the Undo event of the form (assumes Access 2000 or later) to reset
the check boxes based on the OldValue of the hidden number field.

The example below uses a Byte field named DOW for the days of the week, and
unbound check boxes named chk1 to chk7. The form's events call the code like
this:
- Form_Current contains:
Call ScatterToMealsDOW(Me, False)
- Form_Undo contains:
Call ScatterToMealsDOW(Me, True)
- The AfterUpdate of each check box contains:
Call GatherFromMealsDOW(Me)

In interpreting the value of the bitfields, you need to be aware that
expressions in queries or in the Access interface (e.g. in Conditional
Formatting) to not use the logical AND operator, so the same expression in
those contexts returns a different result than it would in VBA. Details:
ser-31.html#AmbiguousOperators

Finally, the sample code:

Public Function ScatterToDOW(frm As Form, bOldValue As Boolean)
On Error GoTo Err_ScatterToDOW
'Purpose: Check the boxes for Days-Of-Week,
' from the DOW bitfield.
'Usage: Called by Form_Current and Form_Undo
Dim i As Integer
Dim btDOW As Byte

If bOldValue Then
btDOW = Nz(frm.DOW.OldValue, 0)
Else
btDOW = Nz(frm.DOW.Value, 0)
End If
For i = 1 To 7
frm("chk" & i).Value = (btDOW And 2 ^ (i - 1))
Next

Exit_ScatterToDOW:
Exit Function

Err_ScatterToDOW:
MsgBox "Error " & Err.Number & ": " & Err.Description, ,"ScatterToDOW"
Resume Exit_ScatterToDOW
End Function

Public Function GatherFromDOW(frm As Form)
On Error GoTo Err_GatherFromDOW
'Purpose: Update the DOW bitfield, whenever a "Day" checkbox is
altered.
'Usage: Called by AfterUpdate of all 7 checkboxes
Dim i As Integer
Dim btDOW As Byte

For i = 1 To 7
If frm("chk" & i).Value Then
btDOW = btDOW + 2 ^ (i - 1)
End If
Next
frm.DOW = btDOW

Exit_GatherFromDOW:
Exit Function

Err_GatherFromDOW:
MsgBox "Error " & Err.Number & ": " & Err.Description, ,"GatherFromDOW"
Resume Exit_GatherFromDOW
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"G McGuire" wrote in message
...
Is it possible to have something like an option group, bound to a control,
with three checkboxes where each checkbox has the value 1,2 and 4
respectively. I want to be able to select any number of checkboxes in the
group. If the first two are selected the value 3 (1+2) would be stored in
the
control. If all three were selected the value 7 (1+2+4) would be stored.
If
the first and last were selected then 5 (1+4) would be stored....etc. Does
something like this exist or can someone shed some light on how it could
be
done? Thanks.