How to make the part of the field name as a variable in a recordset

[Paul]

I have a recordset which returned a number of fields based on a query. I
want to append the record from the recordset to another table. I want to
make part of the field name as a variable before appending to another table
and the vba code does not recognize it. Please help.

Option Compare Database
Option Explicit

Function ConvertResponse()
Dim db1 As Database
Dim rst1 As Recordset
Dim db2 As Database
Dim rst2 As Recordset
Dim test As String
Dim i As Integer

Set db1 = CurrentDb
Set rst1 = db1.OpenRecordset("Qry_Combine Tables")
If Not rst1.BOF Then
rst1.MoveFirst
Do While Not rst1.EOF
Set db2 = CurrentDb
Set rst2 = db2.OpenRecordset("Activities Details", dbOpenDynaset)
With rst2
On Error Resume Next
For i = 0 To 14
i = 1 + i
rst2.AddNew
rst2!RecordID = rst1!RecordID
rst2!CourseName = rst1!QCourse(i) & "_1" .....................It
does not recognize this
rst2!ProfessionalDevelopment = rst1!QType & (i)
......................It does not recognize this
rst2!FiscalYear = rst1!QFY(i) .....................It does not
recognize this
rst2!Duration = rst1!QDuration(i) .....................It does
not recognize this
rst2!PaidPersonalTime = rst1!QPaidTime(i)
......................It does not recognize this
rst2!PaidByMSFHR = rst1!QFeesMSFHR(i) & "_1"
......................It does not recognize this
rst2!PaidByStudent = rst1!QFeesYou(i) & "_1"
......................It does not recognize this
rst2!BenefitsToMSFHR = rst1!QBenefitsMSFHR(i) & "_1"
......................It does not recognize this
rst2!BenefitsToYouPersonally = rst1!QBenefitsYou(i) & "_1"
......................It does not recognize this
rst2.Update
rst2.Close
Next
End With
Set rst2 = Nothing
Set db2 = Nothing
rst1.MoveNext
Loop
End If
Set rst1 = Nothing
Set db1 = Nothing
End Function

 

[John Vinson]

rst2!CourseName = rst1!QCourse(i) & "_1" .....................It
does not recognize this

Try rst1.Fields(QCourse(i) & "_1")

The Fields collection takes either a numeric index or the name of the
field as a text string as an argument, and is an alternative to the
!fieldname syntax.

John W. Vinson[MVP]