Error using CurrentDb.Execute(sqlStr) despite SQL string functioning properly in query builder

Question

I am building a MS Access database and I cannot find a solution to my issue anywhere. All I want to do is trigger an update to my table using SQL code upon clicking a button, however, each time I try to run this code I get the error: "Run time error 3061, Too few parameters. Expected 1". The naming of all the tables and fields I call in my SQL code is correct. I copy/pasted the SQL string from my debug print into a query builder and it worked without issue. My code is:

Private Sub cmdAddRev_Click()
Dim compNum As String
Dim docPath As String
Dim filePath As String
Dim lastRev As Integer
Dim updateRev As Integer
Dim sqlStr As String


compNum = Me.cboRevSelection.Value
docPath = Me.tboRevDocLoc.Value
filePath = Me.tboRevFileLoc.Value
lastRev = DLookup("numLastRev", "tblComponents", "num = [Forms]![frmRevisor]![cboRevSelection]")
updateRev = lastRev + 1

sqlStr = " UPDATE tblComponents "
sqlStr = sqlStr & " SET numLastRev = " & updateRev
sqlStr = sqlStr & " WHERE num = [Forms]![frmRevisor]![cboRevSelection] "

CurrentDb.Execute (sqlStr) 'this line is flagged when the error happens


Debug.Print sqlStr

End Sub

Gene · Accepted Answer · 2015-08-26 11:14:19Z

1

Change this line:

sqlStr = sqlStr & " WHERE num = [Forms]![frmRevisor]![cboRevSelection] "

to this:

sqlStr = sqlStr & " WHERE num = '" & [Forms]![frmRevisor]![cboRevSelection] & "'"

SQL from VBA, doesn't like parameters. Always use your references outside of the quotes, so that the query going to the execute already has values in it. (although using a parametized query would be better still...)

edited Aug 26, 2015 at 11:14

answered Aug 25, 2015 at 15:21

Gene

3926 silver badges15 bronze badges

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

3 Comments

George Over a year ago

Thanks! This was very close to what I needed, The only issue is that the value from cboRevSelection is a string, so I had to add single quotes around it.

Gene Over a year ago

edited my answer to reflect that.. curious though that you use the field name "num" (which i probably incorrectly assumed was short for "number") as a text field.

George Over a year ago

Gene, that was indeed a pretty misleading name, but this database is for mechanical parts/ assemblies whose "numbers" are often alphanumeric. So I am stuck defining part numbers as strings.

AVG · Accepted Answer · 2015-08-25 15:21:51Z

0

You can't reference a form when executing SQL via VBA. There are several options. Set a tempvar to the desired value and reference the tempvar in the SQL or create a public function referencing the form and use the function in the SQL

Public Function RevSelection()
RevSelection = [Forms]![frmRevisor]![cboRevSelection]
End Function

Then use

sqlStr = sqlStr & " WHERE num = RevSelection()"

or, the simplest, reference it as a value.

sqlStr = sqlStr & " WHERE num =" & [Forms]![frmRevisor]![cboRevSelection]

answered Aug 25, 2015 at 15:21

AVG

1,4621 gold badge9 silver badges12 bronze badges

1 Comment

Lionel T. Over a year ago

Maybe better like this: sqlStr = sqlStr & " WHERE num = " & RevSelection()

Collectives™ on Stack Overflow

Error using CurrentDb.Execute(sqlStr) despite SQL string functioning properly in query builder

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related