If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Option Group?
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
Option Group?
Thanks Allen! Very helpful.
Regards, Greg "Allen Browne" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|