Form Objects - Set using variables???

[Jason Gyetko]

I'm trying to set form objects as visble using variable objects (if that
makes sense). The routine listed below works for the hard coded object
'lblT0', but what I really need to do is have that object be a variable so
that with this single loop I can set all my objects to visible. Ex: lblT0,
lblT1, lblT2, etc. I thought I would be able to do this by replacing my
hard coded set statement 'Set lblT = lblT0' with this 'Set lblT = lblT & i'
but I get a Run-time error '438': Object doesn't support this property or
method. Can anyone tell me what I need to do to get this to work?

Private Sub Form_Load()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim lblT As Object
Dim txtT As Object
i = 0
Set lblT = lblT0 'Would like to use something like - Set lblT = lblT
& i
'which would set my object to lblT0, lblT1,
etc.
Set txtT = lblT0 'Would like to use something like - Set txtT = txtT
& i
'which would set my object to txtT0, txtT1,
etc.
Set db = CurrentDb()
Set rs = db.OpenRecordset _
("SELECT tblTestMaster.TSdesc FROM tblTestMaster")

With rs
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
'Display all relevant fields - HERE IS WHERE MY VARIABLES ARE USED
If .Fields( i ).SourceTable = "tblTestMaster" Then
lblT.Visible = True
txtT.Visible = True
lblT.Caption = .Fields( i )
End If
i = i + 1
.MoveNext
Loop
End If
.Close
End With

 

[Dirk Goldgar]

I'm trying to set form objects as visble using variable objects (if
that makes sense). The routine listed below works for the hard coded
object 'lblT0', but what I really need to do is have that object be a
variable so that with this single loop I can set all my objects to
visible. Ex: lblT0, lblT1, lblT2, etc. I thought I would be able
to do this by replacing my hard coded set statement 'Set lblT =
lblT0' with this 'Set lblT = lblT & i' but I get a Run-time error
'438': Object doesn't support this property or method. Can anyone
tell me what I need to do to get this to work?

Private Sub Form_Load()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim lblT As Object
Dim txtT As Object
i = 0
Set lblT = lblT0 'Would like to use something like - Set lblT
= lblT & i
'which would set my object to lblT0,
lblT1, etc.
Set txtT = lblT0 'Would like to use something like - Set txtT
= txtT & i
'which would set my object to txtT0,
txtT1, etc.
Set db = CurrentDb()
Set rs = db.OpenRecordset _
("SELECT tblTestMaster.TSdesc FROM tblTestMaster")

With rs
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
'Display all relevant fields - HERE IS WHERE MY VARIABLES
ARE USED If .Fields( i ).SourceTable = "tblTestMaster" Then
lblT.Visible = True
txtT.Visible = True
lblT.Caption = .Fields( i )
End If
i = i + 1
.MoveNext
Loop
End If
.Close
End With

Your terminology is a bit confusing, but I take that by "form objects"
you actually are referring to *control* objects on the form. So lblT0,
lblT1, etc., are the names of controls. In that case, you can build a
string index into the form's Controls collection to refer to these
objects. It might look like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer

i = 0

Set db = CurrentDb()
Set rs = db.OpenRecordset _
("SELECT tblTestMaster.TSdesc FROM tblTestMaster")

With rs
If .RecordCount > 0 Then
Do Until .EOF
'Display all relevant fields
If .Fields( i ).SourceTable = "tblTestMaster" Then
Me.Controls("lblT" & i).Visible = True
Me.Controls("lblT" & i).Caption = .Fields( i )
Me.Controls("txtT" & i).Visible = True
End If
i = i + 1
.MoveNext
Loop
End If
.Close
End With

 

[Jason Gyetko]

Thanks, that works perfectly. Sorry about the terminology.