Create dynamic input for variable

Question

I hope someone can help, I am new to programming, my problem is this:

40 checkboxes on a form which one by one, needs to be checked for boolean "True" or "False" (checked or unchecked by user), so that I can run individual code for each "True" case.

I am trying to include a counter "i" in the "MS Access checkbox reference" which I use for the variable, to give me the value of the given checkbox. The following code should show what I try to accomplish, can anybody point me in the right direction, without using a very advanced solution? I presume it is because it cannot execute all of this in a single step or because it only sees my input for the variable as a string and not a command to execute :

Do While Flag = True

Dim CheckboxVar As Boolean
i = i + 1

If i = 40 Then
    Flag = False
End If

CheckboxVar = "Me.Checkbox" & i & ".Value"
AddWhereSQL = SQLBuilder (CheckboxVar)
Loop

(Retrieve the value of Checkbox1 and send it to SQLBuilder, retrieve Checkbox2 and send it to SQLBuilder, and so on)

Thank you all for your suggestions, for my case and persona, Kostas K's solution works best (It is implemented and running). I use the name of the control instead of tag, and I run individual/unique code depending on which checkbox is True. This site is a wonderful place with wonderful people, thank you very much. Michael — Spreed, Nov 03 '17 at 12:20

score 5 · Answer 1 · edited Nov 02 '17 at 15:03

5

Loop through your checkboxes like this:

Sub Test()
    Dim ctrl As Control

    For Each ctrl In Me.Controls
        If TypeOf ctrl Is CheckBox Then
            If ctrl.Value = True Then
                'do something
            End If
        End If
    Next ctrl
End Sub

Naming your checkboxes "Checkbox1", "Checkbox2", etc. is tiresome and not the best naming practice. The code above will find every checkbox on the form but you could easily restrict it to all checkboxes with a specific tag, for example.

edited Nov 02 '17 at 15:03

Erik A

31,639
12
42
67

answered Nov 02 '17 at 14:10

CallumDA

12,025
6
30
52

1

Note that you may have to account for checkboxes that shouldn't be in the loop. You can use tags for that (e.g. set a tag for all checkboxes that should, and check `If ctrl.Tag = "something" Then`) – Erik A Nov 02 '17 at 14:44
Your code applies to Userforms, but not to Access forms (they are not MSForms). -- With 40 checkboxes on a form, chances are that the *only* sensible way to name them is `Checkbox1`, `Checkbox2`, ... – Andre Nov 02 '17 at 14:45
@ErikvonAsmuth, tagging works -- for the same reason I suggested putting them all in a frame and looping through `UserForm1.Frame1.Controls`, for example. – CallumDA Nov 02 '17 at 14:49
@CallumDA Yes, but like Andre said, it's an Access question and not a userform, so frames are unavailable. Tags are the usual way for Access. Your further code will work fine after minor changes (remove `MSForms.`, change `UserForm1` to `Me`) – Erik A Nov 02 '17 at 14:59
@ErikvonAsmuth I don't have Access so I can't test it but good to know! Please feel free to edit this answer and change the declarations so that it's more suitable for OP. – CallumDA Nov 02 '17 at 15:02
1

Adjusted for use in MS Access, @Andre it should be fine now, I agree with CallumDA that Checkbox1, Checkbox2 etc. should be avoided. – Erik A Nov 02 '17 at 15:05

score 2 · Accepted Answer · answered Nov 02 '17 at 14:02

2

To loop through the controls, you can do this:

Dim i As Long
For i = 1 To 40
    If Me.Controls("CheckBoxControlName" & i).Value = -1 Then
        'The control value is True
    End If
Next i

If you code is placed in a Standard Module, change Me to Forms!YourFormName.

answered Nov 02 '17 at 14:02

Kostas K.

8,293
2
22
28

Small note: use an integer instead of a long to save memory and execution time (the max value is 40) – Erik A Nov 02 '17 at 14:10
2

@Erik von Asmuth VBA internally converts all Integers to Long therefore there's no performance advantage. – Kostas K. Nov 02 '17 at 14:14
@ErikvonAsmuth. I learnt something new today as well (thanks Kostas K.!) https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long – CallumDA Nov 02 '17 at 14:27
@KostasK. Thanks for the heads up, sorry for the incorrect criticism. Guess I can go optimize literally all code that uses integers I ever wrote. – Erik A Nov 02 '17 at 14:33
@Erik von Asmuth Ah, no bother man. I had to do the exact same thing when I found out. :) – Kostas K. Nov 02 '17 at 14:36

score 0 · Answer 3 · answered Nov 02 '17 at 14:05

0

I think you need a "for" loop. Maybe something like:

CheckboxVar = " WHERE "
For i = 1 to 40
    CheckboxVar = Me.Controls("Checkbox" & i).Value = True & " AND " & checkboxVar
next

answered Nov 02 '17 at 14:05

Gordon Prince

142
2
9

Create dynamic input for variable

3 Answers3