calculating textual reference to form object

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

lets say I have form objects... L01,L02,L03....L35,L36 (in this instance
checkbox labels)

how can I directly reference them through indirect textual calculation?

like...
Function lblr(LO As Object, pntr As String)
'LO.Caption = DLookup("DistLbl", "DistLabel", "Code=" & pntr)
'DLookup("DistLbl", "DistLabel", "Code = pntr")
LO.Caption = DLookup("DistLbl", "DistLabel", "Code = pntr")
End Function
Private Sub Form_Open(Cancel As Integer)
Dim i As Integer
Dim ctrl As Object
'Dim LblRef As string
Dim LblRef As Variant

L01.Caption = DLookup("DistLbl", "DistLabel", "Code='01'")
For i = 2 To 36
Set ctrl = Nothing
LblRef = "L" & Format(i, "00")
Set ctrl.Type = Label
Set ctrl.Name = LblRef
ctrl.Caption = lblr(ctrl, Format(i, "00"))
Next i

however it bombs before I can specify the object I wish to manipulate
NOTE: some of the statements are commented out as they are other avenues
explored and rejected
 
For i = 2 To 36
Set ctrl = Nothing
LblRef = "L" & Format(i, "00")
Set ctrl = Me.Controls(LblRef)
ctrl.Caption = lblr(ctrl, Format(i, "00"))
Next i
 
Thanks - it worked after a fashion

sorry it seems I double submitted question - I got internet connection error
immediately after first post and figured it didn't get submitted so i
repeated only to find they both posted - You're ACES!!!!
 
You mean in DLookup("DistLbl", "DistLabel", "Code = pntr")?

You're passing a String variable (pntr). In order to get the value of the
variable, you need to have the reference to the variable outside of the
quotes. Otherwise, all you're doing is comparing Code to the literal pntr.
However, since Code is (presumably) a Text field in DistLabel, you need to
put quotes around the value being passed. That means:

DLookup("DistLbl", "DistLabel", "Code = '" & pntr & "'")

Exagerated for clarity, that's

DLookup("DistLbl", "DistLabel", "Code = ' " & pntr & " ' ")

I don't understand the point of the lblr function. All you really need is

For i = 2 To 36
Set ctrl = Nothing
LblRef = "L" & Format(i, "00")
Set ctrl = Me.Controls(LblRef)
ctrl.Caption = DLookup("DistLbl", "DistLabel", _
"Code = '" & Format(i, "00") & "'")
Next i

Alternatively, the solution Klatuu gave you in your other thread should work
well too.
 
Back
Top