Too few parameters. Expected 1. - OpenRecordset() Help

[jdd3110]

I'm trying to run a DAO recordset when a value gets updated on a form in MS Access 97.

Dim rstTemp As DAO.Recordset

strSQL = "SELECT [WORK ORDER HISTORY].[INCIDENT NO], [WORK ORDER HISTORY].machine, [WORK ORDER HISTORY].DESCRIPTION, [WORK ORDER HISTORY].dept, EMPLOYEES.EMPLOYEE, [WORK ORDER HISTORY].[work completed] FROM [WORK ORDER HISTORY] INNER JOIN EMPLOYEES ON [WORK ORDER HISTORY].[ASSIGNED TO] = EMPLOYEES.EMPLOYEE_NAME WHERE ((([WORK ORDER HISTORY].[INCIDENT NO])=[Forms]![Maintenance WO Update]![Ticket#]))"
Set rstTemp = CurrentDb.OpenRecordset(strSQL)

When I run the code i get the following error:
Run-time error '3061':
Too few parameters. Expected 1.

I experimented with alot of options and I discovered that If I write a much simplier SQL statement (Select * From table), I won't get an error. Based on what I'm doing, I would really like to get my recordset to work with the large sql code above, but if not, then maybe someone can help me rethink my methods to solve what I need to get done.

Here is what I'm trying to accomplish. I have 2 tables. A Work Order History table and a Machine Work Order History Table. The form uses the Machine Table, so all of the controls are related to its fields. When the user enters a number into one of those fields, I need to run a query to pull data out of the Work Order History table, and then put the data into some of the controls on the form. So I figured I would run a recordset that would query my data in vba then I would be able to put what I want into whatever control I wanted. Does that make sense? I hope so.

if anyone can help me out I would really appreciate it.
Thanks alot
-Keith

 

[Golom]

The most common cause of "3061" is that you have specified a field name in your SQL that doesn't exist in the table. Without seeing the tables in question, I can't tell which one it might be. I do note that you reference "EMPLOYEES.EMPLOYEE" in the SELECT clause but you reference "EMPLOYEES.EMPLOYEE_NAME" in the FROM clause. Should they be the same and do they both exist?

 

[jdd3110]

Both the Employee and Employee_Name exist. I'm pretty sure the sql is good. I built it using the QBE Generator in access, so when I run the query it works. Then I copied the SQL statement from the QBE to my code. I also tried running the OpenRecordset() and using the name of the query instead of using the string, and I get the same error. Any thoughts?

 

[TonyJollans]

Hi jdd3110,

Using QBE and other interfaces, Access interprets references to the Forms Collection and passes on the results. From VBA you must also pass on the results - to do this you must force them to be evaluated first, so they need to go outside the quotes when you build the SQL string.

strSQL = "SELECT  ...  WHERE ((([WORK ORDER HISTORY].[INCIDENT NO])=

" &

[Forms]![Maintenance WO Update]![Ticket#]

 & "

))"

. or, if the field is not numeric ..

strSQL = "SELECT  ...  WHERE ((([WORK ORDER HISTORY].[INCIDENT NO])=

'" &

[Forms]![Maintenance WO Update]![Ticket#]

 & "'

))"

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[jdd3110]

That's you. You got it! Thanks alot for the help.

-Keith