Framegroup using bitvalues and checkboxes

[DKDiveDude]

I am trying to create a framegroup with checkboxes, where each individual checkbox in the framegroup can stay on or off, meaning I want to be able to select (check) several checkboxes, or any combination on or off, not just the limitation of only any one checkbox on, and none other.

Hmm, maybe not the best description. But I have a field in a table, that a bit value, see this table field example:

numAttributes
0;"";1;"Metal";2;"Heavy";4;"Long"

Now an entry for the above example could have none, or several of the above attributes. So the stored value for an object that is made of metal, is heavy and long, would have the value 7 (1+2+4)

Now I want the same on my form, but creating a framegroup with the wizard, I can only select one checkbox at any given time. I want the option to select none, or any combination of them all, and then have the values for each checkbox stored in the table.

I am kind of surprised that this has not been thought of by Microsoft.

For this particular database, I have MANY of such attribute type fields. Any ideas?

 

[DKDiveDude]

Ok, I came up with the following solution, not award winning, but

1) To display a number as bits spread over multiple check boxes is easy, just set each checkbox's control source to the following:

Checkbox 1 =AndBitwiseComparison([numAttribute],1)
Checkbox 2 =AndBitwiseComparison([numAttribute],2)
Checkbox 3 =AndBitwiseComparison([numAttribute],4)
etc.....

numAttribute is the field where the data is stored off course, and AndBitwiseComparison is a custom function that does a bitwise AND operation since the crappy "Expression Builder" does not support bitwise commands. It takes the parameters current field value and bit comparison value.

Public Function AndBitwiseComparison(numValue As Long, numComparison As
Long) As Long
AndBitwiseComparison = numValue And numComparison
End Function


2) Now to actually set the value, after clicking a checkbox was a bit, no phun intended, more tricky as with my limited Access knowledge, I can not set a field value from another fields event using Expression Builder.

Another strange thing, is that the event Single Click did NOT work, so I used double click. So for each checkbox, I use the following on the "On Dbl Click" event:

Checkbox 1 =XorBitwiseComparison("Attribute",[numAttribute],1)
Checkbox 2 =XorBitwiseComparison("Attribute",[numAttribute],2)
Checkbox 3 =XorBitwiseComparison("Attribute",[numAttribute],8)
etc.....

Where XorBitwiseComparison is a custom function, that takes as parameters my checkbox group name, current value, and Xor bitvalue), like this:

Public Function XorBitwiseComparison(txtField As String, numValue As
Long, numComparison As Long) As Long
Select Case txtField
Case "Attribute1": Forms!frmTest!numAttribute = numValue
Xor numComparison
Case "Attribute2": Forms!frmTest!numAttribute = numValue
Xor numComparison
Case "Attribute3": Forms!frmTest!numAttribute = numValue
Xor numComparison
etc......
End Select
End Function


Any ideas on how to make this smoother?

 

[Pat Hartman]

I am kind of surprised that this has not been thought of by Microsoft.

- and rightly so! This is something that mainframe programmers STOPPED doing 30 years ago when relational databbases came into vogue. When you have a 1-many relationship, it should be stored as such. Mushing multiple values into a single field violates first normal form.

FYI, option groups return a SINGLE value and so are bound to a single field. If you wanted to store multiple values, you would need to put option buttons or checkboxes directly on the form and since you are not binding the option buttons, you will need to populate them in code in the form's Current event and capture their value in the form's BeforeUpdate event to store them "mushed".

FYI 2, the proper way to return to a form/report field from the form/report module is - Me.YourFieldName or Me.YourControlName depending on whether you want to address the underlying field or the actual control.

 

[DKDiveDude]

As always Pat I certainly appreciate your input.

However for this particular table, I ONLY wanted one table, as it was actually a table I was creating to be converted to MySQL and used via PHP on a website of mine. I there wanted to keep things simple, and just access one database, and manipulating bits is second nature to me as I started my programming 20 years ago using 6502/6510 assmebly code on the Commodore 64. For this particular database I wanted to avoid 100+ fields just containg a Yes or No value, and I also wanted to avoid having to look up in other tables. Just plain dirty

Thanks again...

 

[Pat Hartman]

If dirty works for you, go for it. It doesn't work for me which is why I hate web interfaces so much. They bring me back to the bad o'l mainframe green screen CICS days and the type of programming that that environment required.