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 · Accepted Answer · 2015-04-10 17:36:22Z

1

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)"

answered Apr 10, 2015 at 17:36

Corey Shuman

665 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Access Form / VBA: Concatenated Field Name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related