compose a control name from code, in loop

  • Thread starter Thread starter Greg Green
  • Start date Start date
G

Greg Green

I'm writing a loop that composes an SQL String along the way. The result of
the SQL String is passed into a control. With each pass of the loop, I'd
like to generate a control name that I can pass the result of the SQL String
into. I have this and it's hanging


--------------------------
Do Until strLoopCount = 2

strLoopCount = strLoopCount + 1
strHourEnd = strLoopCount & ":00"

strSQL = "SELECT tblValues.Value FROM tblValues WHERE
(((tblValues.Date)=#" & strRefDate & "#) AND ((tblValues.PointID)=1007) AND
((tblValues.HrEnd)='" & strHourEnd & "'));"


rst.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

Dim ctl As Control, frm As Form

Set frm = Me
Set ctl = "A" & strLoopCount <----- code breaks on
this line "type mismatch"

frm.ctl.Value = rst("Value")

Loop
--------------------------


What would do the trick?

Thanks - G
 
Instead of
Set ctl = "A" & strLoopCount

Use
Set ctl = frm.controls("A" & strLoopCount)

or
Set ctl = frm.controls("A" & strLoopCount)

Really you don't even need the form object variable - just refer directly to
'me'

set ctl=me.controls("A" & strLoopcount)

Also note that this line is incorrect -

frm.ctl.Value = rst("Value")

it should be:

ctl.value =rst("Value")

Oh dear, this brings up another problem - Value is not a good choice for a
field name since Value is a property of a field and a control. The above
reference is explicit and should work correctly but it would be far better
and far less confusing to have a different field name xxValue where xx is a
descriptor of the type of value you are storing.
 
Back
Top