Multiple check boxes 1

[JasonPurdueEE]

Good morning everybody!

I need some advice on efficiency. My new databse is going to require the user to input data from a printed form. On the printed form there are, among other things, 42 check boxes. I need to be able to select one, many, or all of the check boxes.

Would it be faster/more efficient to have 42 yes/no option groups, or just 42 unbound check boxes? I'm thinking either way my table is going to have to have 42 columns to hold this data. Is there a better way to do this? I have yet to start creating the tables, I'm still trying to think this one out. Can anybody shed some insight on this? Thanks in advance.

JASON

______________________________
Sleep is a poor substitute for coffee.

 

[FancyPrairie]

The easiest way would be to have 42 yes/no fields defined in your table. An alternate would be to declare a text field having a length of 6 (8 bits * 6 = 54). Then each bit of the string would represent a yes/no flag. Then you just need to And and Or the bits to set and read them (not too complicated).

Either way, you are still going to have to have 42 check boxes (rather than option groups). I would use somekind of naming convention for the checkboxes so that I could perform loops on them (i.e. set/clear all or a group of them). I might also write a quick function that would create the 42 listboxes (using my naming convention). Something like this (assuming name of the form is "Form1" and it is currently opened in design view).

Function BuildCheckBoxes()

    Dim chkBox As CheckBox
    Dim ctlLabel As Control
    
    Dim sngX As Single
    Dim sngY As Single
    
    Dim i As Integer

    sngX = 0
    sngY = 0.0208
    
    For i = 1 To 42
    
        sngY = (sngY + 0.3)
        
        Set chkBox = CreateControl("form1", acCheckBox, acDetail, , , 0, sngY * 1440)
        chkBox.Name = "chk" & i
        
        Set ctlLabel = CreateControl("form1", acLabel, , chkBox.Name, "NewLabel" & i, (sngX + 0.1667) * 1440, sngY * 1440)

    Next i
    
    MsgBox "Done"
    
End Function

 

[Leco]

That's a neat way of making the check boxes - but then how do you "gather" the info from individual boxes? Say I want to find out who has ticked box A, do I have to have a query with all 42 fields or 42 separate queries?
Many thanks for the code so far.

I Leco

 

[FancyPrairie]

If you had 42 individual yes/no fields defined in your table, then you would have to check each one in your query (not 42 separate queries). However, if you used the bits to store your info, it might be less cumbersome. I stated in my previous post to use a string. I should have said long integer (you will need 3 of them 16*3 = 48). Now, suppose you want to determine if check box 1 is selected and check box 3, then you would do something like this:

select * from tblYours where (lngBits1 And OCT(5)) = OCT(5))

Bit 0 = Yes (check box 1) 1
Bit 1 = No (check box 2) 0
Bit 2 = Yes (check box 3) 1

101 = Oct(5)

It's really not that bad working with bits and you would save space. But it depends on questions you will asking.

 

[Leco]

Now you've lost me! Where do I put that and how is it attached to the table?
Sorry if I'm being stupid, I've had a long day.

I Leco

 

[FancyPrairie]

Like I said in my first post, the easiest way to do it is to have 42 yes/no fields defined in your table. But, you're taking up space (may not be a big deal). But I would also use somekind of naming convention for these fields as well as the ones in the form so that you can loop thru easier.

If you go with the bit scenerio, you would need to define 3 long integers in your table. The first long integer number (which consists of 16 bits) would represent the answers to the 1st 16 yes/no question (bit = 1 if yes, bit = 0 if no). The second long intger would represent the next set of 16 and the 3rd long integer the next set of 16. If you do it this way you going to have to do some coding to determine which bits are set/cleared to determine which checkboxes should be checked/unchecked.

Now, let's say check box 1 and 3 are checked. That means the bit 0 and bit 2 are set in the 1st long integer. Therefore, the bit configuration would look like this:
0 000 000 000 000 101. 101 indicates bit 0 (2^^0 = 1), bit 2 (2^^2=4), which equals Octal 5 (1+4). (Some people use hexadecimal, I grew up with octal.)

Not that compliated if you know what you're doing (simple loops). But if you're unfamiliar with working with bits, then you may want to reconsider this method.

 

[Leco]

I'm OK with octals now I've seen what you're doing (something like setting sub net masks) though it may be 'using a sledgehammer to crack a walnut'
How would the loop code look?

I Leco

 

[FancyPrairie]

Again, this may lead down a path that might get complicated. The first thing I would do is to declare a public constant where I declare all of my public variables. The constant would be set equal to the number of check boxes you have. I would put the following code in the OnCurrent event of the form, where lng1 represents the 1st long integer you have defined in your table (it is an hidden bound control on your form. As is the other 2). lng2 represents the 2nd one and lng3 represents the 3rd one.

In my example I declared the constant in this procedure. Again, you should declare it public because other modules will be using it.

Note: I'm assuming your check box names are "chkBox0", "chkBox1", and so on.

Function Form_OnCurrent()

    Const gintTotalYN As Integer = 42
    
    Dim lng(1 To (gintTotalYN + 15) \ 16) As Long
    
    Dim lng1 As Long
    Dim lng2 As Long
    Dim lng3 As Long
    
    Dim i As Integer
    Dim k As Integer
    
    lng1 = 5         'Just used for testing (assuming check boxes 1 and 3 should be checked
    
    lng(1) = lng1
    lng(2) = lng2
    lng(3) = lng3
    
    For i = 1 To (gintTotalYN + 15) \ 16    'i.e. 1 to 3
    
        For k = 0 To 15
        
            If ((lng(i) And (2 ^ k)) = (2 ^ k)) Then
                MsgBox "chkBox" & ((i - 1) * 16) + k & " is checked"
            Else
                MsgBox "chkBox" & ((i - 1) * 16) + k & " is NOT checked"
            End If
            
        Next k
        
    Next i
        
End Function

Then in the OnBeforeUpdate event of the form, I would put the following code. It sets that correspond to the check boxes that have been checked.

Function Form_BeforeUpdate()

    Const gintTotalYN As Integer = 42
    
    Dim lng(1 To (gintTotalYN + 15) \ 16) As Long
    
    Dim lng1 As Long
    Dim lng2 As Long
    Dim lng3 As Long
    
    Dim i As Integer
    Dim k As Integer
    
    
    For i = 1 To (gintTotalYN + 15) \ 16    'i.e. 1 to 3
    
        lng(i) = 0
        
        For k = 0 To 15
        
            If (Me("chkBox" & ((i - 1) * 16) + k)) Then
                lng(i) = lng(i) Or (2 ^ k)
            End If
            
        Next k
        
    Next i
    
    lng1 = lng(1)
    lng2 = lng(2)
    lng3 = lng(3)
    
End Function

 

[Leco]

That certainly makes sense. Thanks a bunch - I'll give it a try.

I Leco

 

[JasonPurdueEE]

Thank you FancyPrairie for your reply. What you posted looks a little more complicated than I think I have time to do (I'm on a pretty tight timeline for this little project). I think I will just use 42 yes/no fields. Unless you think that method will be too slow. Thanks again for your help.

JASON

______________________________
Sleep is a poor substitute for coffee.

 

[FancyPrairie]

You may be right. As I qualified in my previous posts, it might get complicated. However, populating the check boxes (via OnCurrent) and reading them back in (via BeforeUpdate) would be a piece of cake. You already have the code above to do it. The unknown piece would be the SQL statement to report on. That might get complicated. Don't know until you try it. Like you said, you don't really have time to go down that path and then find out it's too complicated.

However, the reason I'm responding back is that I would still use a naming convention for your yes/no fields and the check boxes. I would also use the function (in my first post) that initially generates the check boxes on the form.

 

[JasonPurdueEE]

Thank you Fancy Prairie for the quck reply. I have used the naming convention chk1, chk2,chk3...

is there a really good benefit to using the function in your first post to generate the check boxes? I just created the fields in my table and then on my form selected all the fields from the Field List and dragged and dropped on to the form. perhaps I am overlooking something in your function?

______________________________
Sleep is a poor substitute for coffee.

 

[FancyPrairie]

You're right. I wasn't thinking.