Loop through names of controls

[Baby Face Lee]

Hello (again)
Sorry to keep bothering you guys but I'm having probs trying to do a loop.
I've got a control (well two infact) that each have the same name except for
a number at the end (eg cboEstCode1 to cboEstCode12) - there are 12 of them.
I'm running some SQL to insert the value in each control into a table. I
thought I'd do a For/Next loop that goes round 12 times adding the digit to
the end of the control name but the SQL is taking the string values literally
(not surprisingly) rather than finding the value that's in the name of the
control.
Here's my code:

Set db = DBEngine(0)(0)

For intEstCode = 1 To 12
stEstCode = "cboEstCode" & intEstCode
For intCands = 1 To 12
stCands = "txtCands" & intCands

strSQL = "INSERT INTO tbl_Estimates (CentreNo, EstCode, Cands)" _
& " VALUES (" & Me!cboCentreNo & ", " & "'" & stEstCode & "'" & ", "
& stCands & ");"
db.Execute strSQL
Exit For
Next
Next

I know this is not the way a form should be constructed but I'm trying to
'push' the data into a table to improve speed and a continuous form doesn't
work because being Unbound it doesn't hold more than one record at a time.
Sorry for all the waffle but can you help with this? Have I got the loop
structure right too?
Thanks guys!
Lee

 

[MikeB]

Hello (again)
Sorry to keep bothering you guys but I'm having probs trying to do a loop.
I've got a control (well two infact) that each have the same name except for
a number at the end (eg cboEstCode1 to cboEstCode12) - there are 12 of them.
I'm running some SQL to insert the value in each control into a table. I
thought I'd do a For/Next loop that goes round 12 times adding the digit to
the end of the control name but the SQL is taking the string values literally
(not surprisingly) rather than finding the value that's in the name of the
control.
Here's my code:

Set db = DBEngine(0)(0)

For intEstCode = 1 To 12
stEstCode = "cboEstCode" & intEstCode
For intCands = 1 To 12
stCands = "txtCands" & intCands

strSQL = "INSERT INTO tbl_Estimates (CentreNo, EstCode, Cands)" _
& " VALUES (" & Me!cboCentreNo & ", " & "'" & stEstCode & "'" & ", "
& stCands & ");"
db.Execute strSQL
Exit For
Next
Next

You can't do anything to an object unless you are working with it directly or by
object reference. All you are doing in code is setting a text value and not
setting an object reference. Use something like the following to loop through
the controls to find the ones that are combo boxes and then manipulate the
object once you find it...

Dim ctrl As Control
Dim i As Integer
For i = 1 To 12
For Each ctrl In Me.Controls
If ctrl.ControlType = acComboBox Then
If ctrl.Name = "Combo" & CStr(i) Then
'do your stuff to the control reference "ctrl"
End If
End If

Next ctrl
Next

Yes you have to loop through the control collection 12 times unless you want to
set up an array or something like that to check off each combo box as it is
found, but this is pretty simple and you won't notice any speed problems because
of it.

 

[Dan Artuso]

Hi,
This is how you would refer to a control in your loop:
stEstCode = Me("cboEstCode" & intEstCode)

AND

stCands = Me("txtCands" & intCands)

 

[MikeB]

oops. Looked too quick. Didn't scroll down to the string of the sql. I
thought he was having trouble getting an object in his loop.

 

[Baby Face Lee]

Thanks for your help - much appreciated (and MikeB too!)

Best regards,

Lee

 

[Dan Artuso]

ha ha, that's funny because when I checked back here and saw you had posted a reply
to the OP and one to my post, I immediately thought, Oh s**t, i must have read through the
post too quickly and missed something