ERROR 3061 - Too few parameters

  • Thread starter Thread starter BlueWolverine
  • Start date Start date
B

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 *********************
 
BlueWolverine said:
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 *********************
Click to expand...


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 ------
 
Back
Top