Access VBA for loop refer to variable using i

Question

I have a macro in Access 2016 using a for loop to check the values of check boxes. I want to submit the values to a table. I don't want the standard checkbox value for true (-1) I want the value of a 'rolling' count of which has been checked.

e.g. CB1 CB2 CB3 CB4

If only CB1 and CB4 are checked then I want to submit to the table the values 1 for CB1 and 2 for CB4.

My for loop is:

For i = 1 To 8
If Me.Controls("CP" & i) = -1 Then
"CP" & i = I    'ISSUE IS HERE
Debug.Print Me.Controls("CP" & i)
End If
Next i

How do I refer to the variable value? so CP & I = CP1, CP2, CP3, CP4 etc...

Me.Controls("CP" & i)= I?

Vityata
– Vityata

2017-11-23 11:55:03 +00:00
Commented Nov 23, 2017 at 11:55 — Vityata
– Vityata, Commented Nov 23, 2017 at 11:55

h2so4 · Accepted Answer · 2017-11-23 12:00:00Z

1

variable names, are static you cannot use the syntax "CP" & i to access a variable. you have to use array instead.

Dim arrCP(8)
For i=Me.controls("CP" & i)=-1 then
arrCP(i)=i
debug.print arrCP(i)
end if
next i

answered Nov 23, 2017 at 12:00

h2so4

1,5771 gold badge10 silver badges11 bronze badges

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

Comments

newacc2240 · Accepted Answer · 2017-11-23 12:00:46Z

0

Maybe something like this, using a countTrue to determine the number you would like to display.

Dim countTrue As Long: countTrue = 0
For i = 1 To 8
    If Me.Controls("CP" & i) = -1 Then
        countTrue = countTrue + 1
        Me.Controls("CP" & i) = countTrue
        Debug.Print Me.Controls("CP" & i)
    End If
Next i

answered Nov 23, 2017 at 12:00

newacc2240

1,4351 gold badge9 silver badges14 bronze badges

Collectives™ on Stack Overflow

Access VBA for loop refer to variable using i

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related