Enter Parameter Value when using SQL in Access VBA

Question

I keep getting an "Enter Parameter Value" input box when I run this code.

Private Sub UpdateTables()
Dim strSQL As String
strSQL = "update tblTest,ImportedTable set tblTest.Unit_Cost=ImportedTable.Unit_Cost where tblTest.Part_No=ImportedTable.Part_No"
DoCmd.RunSQL strSQL
End Sub

The box shows up for tblTest.Unit_Cost, ImportedTable.Unit_Cost, tbltTest.Part_No, and ImportedTable.Part_No.

The two tables, tblTest and ImportedTable exist and are spelled properly. Unit_Cost and Part_No both have spaces in the field names (I know that's terrible) hence the '_' in referencing them. I tried taking the space out and rerunning the code, but that didn't fix it.

Any thoughts on what I'm doing incorrectly? Thanks!

use [] around field names with spaces - e.g. ImportedTable.[Unit Cost] — SeanC
– SeanC, Commented Apr 3, 2013 at 14:50

Taliesin · Accepted Answer · 2013-04-03 14:51:39Z

3

In MS Access you must enclose field names in brackets [] when the field contains spaces.

Private Sub UpdateTables()
Dim strSQL As String
strSQL = "update tblTest,ImportedTable set tblTest.[Unit Cost]=ImportedTable.[Unit Cost] where tblTest.[Part No]=ImportedTable.[Part No]"
DoCmd.RunSQL strSQL
End Sub

answered Apr 3, 2013 at 14:51

Taliesin

4142 silver badges12 bronze badges

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

1 Comment

Taliesin Over a year ago

You are quite welcome. Please mark this answer as accepted so that others know a solution has been provided.

Collectives™ on Stack Overflow

Enter Parameter Value when using SQL in Access VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related