Matching form data entry boxes to record set names via loop

[newbieX]

I have a form with several data entry boxes (text, combo, check boxes, and option groups). The name of the data entry boxes match the field names of the corresponding table that the data will be appended to/ drawn from.



When the user selects a value from a combo box, a sql is executed that gathers the data meeting the criteria and auto populates a bunch of fields in the form. My real form has many fields in it and I will be running similar functions throughout (e.g. writing data to table), I would like to create a loop to perform the field matching instead of listing each form data entry box equaling its corresponding record source.

Using this test example, how can I alter my script to a loop instead:

Private Sub txt_ID_AfterUpdate()

    Dim db As Database
    Dim rs As DAO.Recordset
    Dim strSQL As String

        strSQL = "SELECT Table1.ID, Table1.Color, Table1.Make, Table1.Model, Table2.FName, Table2.LName FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID WHERE"
        strSQL = strSQL & "[Table1].ID = """ & Me.txt_ID & """"
        
        Set rs = Currentdb.OpenRecordset(strSQL, DB_OPEN_DYNASET)
        
        Me!ID = rs!ID
        Me!Color = rs!Color
        Me!Make = rs!Make
        Me!Model = rs!Model
        Me!FName = rs!FName
        Me!LName = rs!LName

        rs.Close
        Set rs = Nothing
End Sub

I want it to do something like this:

Private Sub txt_ID_AfterUpdate()

    Dim db As Database
    Dim rs As DAO.Recordset
    Dim strSQL As String

        strSQL = "SELECT Table1.ID, Table1.Color, Table1.Make, Table1.Model, Table2.FName, Table2.LName FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID WHERE"
        strSQL = strSQL & "[Table1].ID = """ & Me.txt_ID & """"
        
        Set rs = Currentdb.OpenRecordset(strSQL, DB_OPEN_DYNASET)

        For each FieldName in FormA
            Me!<FieldName> = rs!<FieldName>
        Next FieldName

        rs.Close
        Set rs = Nothing
End Sub

Many thanks in advance.

[June7]

Is ID an autonumber type field?

Why change the code if it already works?

Those 6 fields are they only ones in the form RecordSource?

Why duplicating data?

[newbieX]

This is an example only. My real database is much more complex with many more fields to loop through than is shown in example. ID is not an autonumber. Duplication of data is required because data entry person does not have direct access to master tables. I would appreciate an answer if for no other reason than curiosity at how to make the coding more concise.

[June7]

Your SQL needs a space after the WHERE keyword. The ID field is a text data type?

The SQL is joining the two tables and both tables have the same field names. Might be an issue with this concept because of the table name qualifier. That becomes part of the fieldname and there is no equivalence to the form field names, unless the form RecordSource also joins the tables.

Maybe easiest to loop through the recordset fields instead of looping through the form fields.

If Not rs.EOF Then
For i = 0 To rs.Fields.Count - 1
Me.Recordset.Fields(rs.Fields(i).Name) = rs.Fields(i)
Next
End If

Or if bound textboxes have same name as the field:

Me.Controls(rs.Fields(i).Name) = rs.Fields(i)

[newbieX]

This code that you provided worked but needed to add period to rsFields(i)

If Not rs.EOF Then
    For i = 0 To rs.Fields.Count - 1
       Me.Controls(rs.Fields(i).Name) = rs.Fields(i)
    Next
End If
rs.Close
Set rs = Nothing

I finally settled on this code. Thanks for pointing me in the right direction.

rs.MoveFirst
For Each fld In rs.Fields
    Me.Controls(fld.Name) = fld
Next fld

[June7]

Sorry for typo. I edited the post.

Glad you figured it out.