Access Form / VBA: Concatenated Field Name

Question

I created a Form in Access, and within this form there is a button which will allow the user to insert a record into a table ("Tbl")

The format of this table is: Report1_Field1 Report1_Field2 Report2_Field1 Report2_Field2 (and so on)

The Form will ask the user to: - Select the report name ("ReportName"); which could be "Report1" or "Report2" - Input a value for Field1 and Field2

The VBA code behind the button is as follows:

Private Sub ButtonUpdate
Dim NameReport as String
Dim FirstField as String
Dim SecondField as String

NameReport = ReportName
FirstField = ReportName & "_Field1"
SecondField = ReportName & "_Field2"

DoCmd.RunSQL "INSERT INTO tbl (FirstField, SecondField) VALUES (Field1, Field2)"

End Sub

I am however getting the Run-Time error 3127: The INSERT INTO statement contains the following unknown field name: 'FirstField'. Make sure you have typed the name correctly, and try the operations again.

Thoughts?

Corey Shuman Corey Shuman · Accepted Answer · 2015-04-10T17:36:22

The SQL string is reading "FirstField" and "SecondField" as literal text instead of using your variables. Try this:

DoCmd.RunSQL "INSERT INTO tbl (" & FirstField & ", " & SecondField & ") VALUES (Field1, Field2)"

Access Form / VBA: Concatenated Field Name

1 Answers