Loop through Form Textbox and enter ControlSource. 1

[patriciaxxx]

I have the code below which I’m in a right mess with.

Its suppose to first loop through fields in a table to get their field names and then loop through the textboxes on the form and enter the first field name as the ControlSource in the first textbox, and the second field name as the ControlSource in the second textbox, and so on, but only for textboxes which have the Tag value asterix (*) and only for as many field names as there are. There are 50 textboxes on the form with an asterix assigned to their Tag property and all 50 have their Visible property set to False so the code also needs to set the textbox Visible property to True but only for any Textboxes which have a field name assigned to the ControlSource.

Any help would be much appreciated.

Private Sub Form_Load()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.CreateQueryDef("", "SELECT tblEditLinkedTable.* FROM tblEditLinkedTable;")
Me.RecordSource = qdf.SQL
Set qdf = Nothing

Dim rst As New ADODB.Recordset
rst.Open "SELECT * FROM tblEditLinkedTable", CurrentProject.Connection, adOpenKeyset

Dim ctl As Control
Dim ii As Integer
Dim ss As String
For Each ctl In Me.Controls
For ii = 0 To rst.Fields.Count - 1
     ss = rst.Fields(ii).Name
     Debug.Print rst.Fields(ii).Name
        If ctl.ControlType = acTextBox Then
            If InStr(1, ctl.Tag, "*") <> 0 Then
ctl.ControlSource = ss
     Debug.Print ctl.ControlSource
               ctl.Visible = True
               ctl.BackColor = vbYellow
            End If
        End If
Next ii
Next ctl
'Debug.Print ss
   rst.Close
Set ctl = Nothing




End Sub

 

[PHV]

A starting point:

...
ii = 0
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        If InStr(1, ctl.Tag, "*") <> 0 Then
            ctl.ControlSource = rst.Fields(ii).Name
            ctl.Visible = True
            ctl.BackColor = vbYellow
            ii = ii + 1
            If ii >= rst.Fields.Count Then Exit For
        End If
    End If
Next ctl
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[patriciaxxx]

Thank you PH, it helped a lot.
Problem solved.