3061 VBA - SQL Query error "Too few parameters Expected 1." Simple query
I have the following code:
Private Sub lst1Model_Operation_Click()
Dim db As Database
Dim sSQL As String
Dim rst As Recordset
Set db = CurrentDb
sSQL = "SELECT * FROM qryOrder_Model_Operation_Value WHERE Model_Operation_ID = " & CInt(Me![lst1Model_Operation].Value)
Debug.Print sSQL 'when pasted this into a query SQL, it works flawlessly.
Set rst = db.OpenRecordset(sSQL) 'error line
'some code here
rst.Close
Set db = Nothing
End Sub
I am at a loss what to do. Debug.Print
looks like that:
SELECT * FROM qryOrder_Model_Operation_Value WHERE Model_Operation_ID = 748
And as I said, if I insert this Debug.Print
into the query in the access itself, it gives the desired results.
I tried adding '' around the value, but using CInt (), I already made sure it is parsed as a whole. Model_Operation_ID also expects to receive an integer (otherwise it won't work in a separate request).
Edit: QryOder_Model_Operation_Value looks like this:
SELECT tbl1Model_Operation.Model_Operation_ID, tbl1Model_Operation.Model_ID, tbl1Model_Operation.Operation_Value_ID, tbl2Operation_Value.Operation_Name_ID, tbl3OperationsList.Operation_Name, tbl1Order_Model.Quantity AS [Počet párov], tbl1Order_Model.Order_ID
FROM tbl3OperationsList INNER JOIN (tbl2Operation_Value INNER JOIN (tbl1Model_Operation INNER JOIN tbl1Order_Model ON tbl1Model_Operation.Model_ID = tbl1Order_Model.Model_ID) ON tbl2Operation_Value.Operation_Value_ID = tbl1Model_Operation.Operation_Value_ID) ON tbl3OperationsList.Operation_ID = tbl2Operation_Value.Operation_Name_ID;
source to share
Make sure Access understands that you want rst DAO.Recordset
instead of ADODB.Recordset
:
'Dim rst As Recordset Dim rst As DAO.Recordset
Both ADO and DAO object models include objects Recordset
. Although similar in some respects, they cannot be used interchangeably.
When your VBA project references in Access include the ADO version (ActiveX Data Objects) and that reference has a higher priority than the DAO reference will Dim rst As Recordset
give you ADODB.Recordset
. Avoid unwanted surprises by always qualifying your ads Recordset
with DAO
or ADODB
if necessary.
source to share