ERROR 3061 - Too few parameters

[BlueWolverine]

Hello

MS ACCESS 2003 on XP PRO.

I am trying to execute the following code.
The last line craps oput with a 3061 error Too Few parameters 1 expected.
IN the past, I have always addressed this using the qdf and Str_SQL method
below (Swap the form reference in the SQL to hard values so that vba doesn't
think the sql is looking at a form, which it doesn;t like.

The problem is it's not working. The query in question doesn't look at a
form, but the query it is based on does. SQL to follow vba below.

Why is this happening and what can I do to go around it? I want to use this
recordset and this is obvioulsy killing such efforts.

Thanks.



START VBA *******************************
Dim rs_PDI As Recordset, tqn_PDI As String, myDB As Database,
lng_record_count As Long, val_x As Long, val_y As Long, str_SQL As String
Dim qdf As QueryDef
Set myDB = CurrentDb
tqn_PDI = "q_Single_PDI1_Exportable"

'Set rs_PDI = myDB.OpenRecordset(tqn_PDI)

str_Route = [Forms]![SingleDocument]![Route]
Set qdf = myDB.QueryDefs(tqn_PDI)
str_SQL = qdf.SQL

str_SQL = Replace(str_SQL, "[Forms]![SingleDocument]![Route]", """" &
str_Route & """")
str_SQL = Replace(str_SQL, "Forms!SingleDocument!Route", """" &
str_Route & """")
str_SQL = Replace(str_SQL, "[Forms]![MainMenu]![g_Route]", """" &
str_Route & """")
str_SQL = Replace(str_SQL, "[Forms]![MainMenu]![g_Route]", """" &
str_Route & """")

Set rs_PDI = myDB.OpenRecordset(str_SQL)
END VBA*********************************************



START SQL *****************
SELECT "Pre-Drive Checklist" AS Category, [GQRS CAT] & "-" & [ID] AS
[OIS-ID], IIf([Action]="Function Check","F","") AS FC,
IIf([Action]="Inspect","I","") AS I, IIf([Action]="Observe","O","") AS O,
IIf([Action]="Special Test","S","") AS S, q_Single_PDI_1.[Work Element
Description], q_Single_PDI_1.Frequency AS Freq, "" AS P, "" AS F
FROM q_Single_PDI_1;
END SQL *********************

 

[Dirk Goldgar]

Hello

MS ACCESS 2003 on XP PRO.

I am trying to execute the following code.
The last line craps oput with a 3061 error Too Few parameters 1 expected.
IN the past, I have always addressed this using the qdf and Str_SQL method
below (Swap the form reference in the SQL to hard values so that vba
doesn't
think the sql is looking at a form, which it doesn;t like.

The problem is it's not working. The query in question doesn't look at a
form, but the query it is based on does. SQL to follow vba below.

Why is this happening and what can I do to go around it? I want to use
this
recordset and this is obvioulsy killing such efforts.

Thanks.



START VBA *******************************
Dim rs_PDI As Recordset, tqn_PDI As String, myDB As Database,
lng_record_count As Long, val_x As Long, val_y As Long, str_SQL As String
Dim qdf As QueryDef
Set myDB = CurrentDb
tqn_PDI = "q_Single_PDI1_Exportable"

'Set rs_PDI = myDB.OpenRecordset(tqn_PDI)

str_Route = [Forms]![SingleDocument]![Route]
Set qdf = myDB.QueryDefs(tqn_PDI)
str_SQL = qdf.SQL

str_SQL = Replace(str_SQL, "[Forms]![SingleDocument]![Route]", """" &
str_Route & """")
str_SQL = Replace(str_SQL, "Forms!SingleDocument!Route", """" &
str_Route & """")
str_SQL = Replace(str_SQL, "[Forms]![MainMenu]![g_Route]", """" &
str_Route & """")
str_SQL = Replace(str_SQL, "[Forms]![MainMenu]![g_Route]", """" &
str_Route & """")

Set rs_PDI = myDB.OpenRecordset(str_SQL)
END VBA*********************************************



START SQL *****************
SELECT "Pre-Drive Checklist" AS Category, [GQRS CAT] & "-" & [ID] AS
[OIS-ID], IIf([Action]="Function Check","F","") AS FC,
IIf([Action]="Inspect","I","") AS I, IIf([Action]="Observe","O","") AS O,
IIf([Action]="Special Test","S","") AS S, q_Single_PDI_1.[Work Element
Description], q_Single_PDI_1.Frequency AS Freq, "" AS P, "" AS F
FROM q_Single_PDI_1;
END SQL *********************

As you've noted, your approach doesn't work when your query is based on
another query with parameters that must be resolved. Use this method
instead, to let Access resolve the parameters for you:

'------ start of revised code ------

Dim myDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Dim rs_PDI As DAO.Recordset

Dim tqn_PDI As String
Dim lng_record_count As Long, val_x As Long, val_y As Long

Set myDB = CurrentDb

tqn_PDI = "q_Single_PDI1_Exportable"

Set qdf = myDB.QueryDefs(tqn_PDI)
With qdf
' Resolve all parameters.
For Each prm In .Parameters
prm.Value = Eval(prm.Name)
Next prm
' Open a recordset from the querydef.
Set rs_PDI = .OpenRecordset()
End With

' Now do things with rs_PDI

'------ end of revised code ------