Dynamic Form not so dyanmic

[dynamictiger]

Maybe I am going blind or stoopid (more stoopid) or both but I can’t see what is happening here.
I have a form which displays historic data for a data entry form. Previously it has been a static display displaying all records regardless of what we are testing for. However, this requires the user to move the form across to display pertinent records for the required test they are now conducting. I prefer a more interactive history where the history is recreated according to the test being conducted. So far so good. The problem appears to be in the code I am running see below:

Private Sub Form_Current()

    Call MakeQuery
    
    Call ClearFields

    Call ApplyControlSource

    Call CleanUpForm

End Sub

This is the calling routine that starts the process. The MakeQuery works fine and is omitted for clarity.
Clear Fields is as follows:

Private Sub ClearFields()
  Dim ctl    As Control

    For Each ctl In Me.Controls

        If ctl.ControlType = acLabel Then
            
                ctl.Width = 0

                ctl.Visible = False

        ElseIf ctl.ControlType = acTextBox Then

                ctl.ControlSource = ""

                ctl.Width = 0

                'ctl.Visible = False

        End If

        

    Next
    
End Sub

This code wanders through the form and resets the labels and controls to nothing and no width. This appears to function fine.

The next bit of code

Private Sub ApplyControlSource()
    Dim rst    As ADODB.Recordset
    Dim ctl    As Control
    Dim intCount As Integer
    Dim lblName As String

    Set rst = New ADODB.Recordset

    rst.Open "qryClientTestBase", CurrentProject.Connection, adOpenStatic, adLockOptimistic

    With rst

        If .RecordCount > 0 Then

            .MoveFirst

            intCount = 0

            For Each ctl In Me.Controls

                If Not .EOF Then

                    If ctl.ControlType = acTextBox Then

                        If ctl.ControlSource = "" Then

                            intCount = intCount + 1
                             
                            ctl.Width = (0.922 * 17)

                            ctl.ControlSource = .Fields("Acronym")
                            
                            Debug.Print ctl.ControlSource

                            Call AssignLabelNames(rst.Fields("Acronym"), intCount)

                            .MoveNext


                        End If

                    End If

                End If


            Next

        End If

    End With

    rst.Close

    Set rst = Nothing


End Sub
Private Sub AssignLabelNames(strName As String, intCount As Integer)
    Dim ctl    As Control

    For Each ctl In Me.Controls

        If ctl.ControlType = acLabel Then

Debug.Print ctl.Name

            If ctl.Name = "lbl" & intCount Then
            
                ctl.Visible = True
                
                ctl.Width = (0.922 * 17)

                ctl.Caption = strName
                
                Debug.Print ctl.Caption
                
                Exit For

            End If

        End If

    Next

End Sub

Seems to run okay in that I can see the lbls and txt boxes, however, they are blank. The code has been working correctly as a more or less static form. That is without the code to clear the fields. I cannot see where the error is occurring to make the form not visible as such. It is just displaying like an empty table with no data.

 

[pono1]

I looked at your code one and a half times and didn't see the form's RecordSource property updated -- I would guess it should be "qryClientTestBase."

Forgive me if it's in the code you posted or the Make Query Sub. Otherwise, you may want to leave the controls' ControlSource property alone (at least initially) and simply reset the form's recordsource property...

Regards,
Tim

 

[dynamictiger]

The forms recordsource is updated in the Makequery Sub