Populate multiple controls from recordset (1 Viewer)

[systemx]

Hi all,

Hopefully someone can help me with this piece of code.

I have six textbox controls (txtMVL1 - txtMVL6) and wan't to populate them from a recordset.

Here is the code I wrote to loop through the recordset -

'Populate the MVL details from tblMVL

strSQL = "SELECT * FROM tblMVL WHERE CUSTOMER_NO =" & CusNo & ";"

Set ADOrs = New ADODB.Recordset
ADOrs.ActiveConnection = CurrentProject.Connection
ADOrs.Open strSQL, , adOpenKeyset, adLockOptimistic

With ADOrs
    If .BOF Or .EOF Then
        .Close
    Else
        Do Until .EOF
            'Need help here
            .MoveNext
        Loop
    End If
    .Close
End With

Set ADOrs = Nothing

I am really not sure how to deal with populating each control. I did try declaring the control as a string eg. "txtMVL" & i with i incrementing by one in the loop - but this does not seem to work - I think perhaps syntax related.

eg.

Dim strControl As String
Dim i As Integer

i = 0
.
.
.

Do Until .EOF
            strControl = "txtMVL" & i
            Forms!MyForm!strControl.Value = .fields("MVL")
            i = i + 1
            .MoveNext
Loop

Can anyone advise me how to approach this problem?

Thanks and regards,

Rob

 

[systemx]

Got it working!

Managed it with this....

'Populate the MVL details from tblMVL
Dim i As Integer
Dim arrMVL As Variant

i = 0
arrMVL = Array(Me.txtMVL1, Me.txtMVL2, Me.txtMVL3, Me.txtMVL4, Me.txtMVL5, Me.txtMVL6)

strSQL = "SELECT * FROM tblMVL WHERE CUSTOMER_NO =" & CusNo & ";"

Set ADOrs = New ADODB.Recordset
ADOrs.ActiveConnection = CurrentProject.Connection
ADOrs.Open strSQL, , adOpenKeyset, adLockOptimistic

With ADOrs
    If .BOF Or .EOF Then
        .Close
    Else
        Do Until .EOF
            arrMVL(i).SetFocus
            Me.ActiveControl = .fields("MVL")
            i = i + 1
            .MoveNext
        Loop
    End If
    .Close
End With

Set ADOrs = Nothing