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
– Spreed, Commented Nov 3, 2017 at 12:20

Erik A · Accepted Answer · 2017-11-02 15:03:37Z

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 2, 2017 at 15:03

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

answered Nov 2, 2017 at 14:10

CallumDA

12.1k6 gold badges33 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Erik A Over a year ago

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)

Andre Over a year ago

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, ...

CallumDA Over a year ago

@ErikvonAsmuth, tagging works -- for the same reason I suggested putting them all in a frame and looping through UserForm1.Frame1.Controls, for example.

Erik A Over a year ago

@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 Over a year ago

Adjusted for use in MS Access, @Andre it should be fine now, I agree with CallumDA that Checkbox1, Checkbox2 etc. should be avoided.

|

Kostas K. · Accepted Answer · 2017-11-02 14:02:29Z

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 2, 2017 at 14:02

Kostas K.

8,5282 gold badges26 silver badges31 bronze badges

5 Comments

Erik A Over a year ago

Small note: use an integer instead of a long to save memory and execution time (the max value is 40)

Kostas K. Over a year ago

@Erik von Asmuth VBA internally converts all Integers to Long therefore there's no performance advantage.

CallumDA Over a year ago

@ErikvonAsmuth. I learnt something new today as well (thanks Kostas K.!) stackoverflow.com/questions/26409117/…

Erik A Over a year ago

@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.

Kostas K. Over a year ago

@Erik von Asmuth Ah, no bother man. I had to do the exact same thing when I found out. :)

Gordon Prince · Accepted Answer · 2017-11-02 14:05:09Z

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 2, 2017 at 14:05

Gordon Prince

1512 silver badges11 bronze badges

Collectives™ on Stack Overflow

Create dynamic input for variable

3 Answers 3

6 Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related