Error - "Too few parameters. Expected 1"

[scubadiver007]

I have a drop-down box on a form to select a K-Code.

I can open query5 using the drop-down no problem but trying to export the data from Query5 to an Excel sheet comes up with this error. So I don't know where the problem lies.

I have three tables

ES Database figures:

ID
Service
Indicator
Practice_Code
Q1
Q2
Q3
Q4

Service list:

ID
Service
Indicator

Signup:

K-Code
Service
ID
Signup


Five Queries -

Query1:

SELECT [Service list].ID, [Service list].Service, [Service list].Indicator
FROM [Service list];


Query2:

SELECT [ES Database figures].ID, [ES Database figures].Service, [ES Database figures].Indicator, [ES Database figures].Practice_Code, [ES Database figures].Q1, [ES Database figures].Q2, [ES Database figures].Q3, [ES Database figures].Q4
FROM [ES Database figures]
WHERE ((([ES Database figures].Practice_Code)=[forms]![form1]![Practice_List]));


Query3 links Query1 and Query2 using an outerjoin because I need to select every service and indicator regardless of whatever data is available from the "ES database figures".

SELECT Query1.ID, Query1.Service, Query1.Indicator, Query2.Practice_Code, Query2.Q1, Query2.Q2, Query2.Q3, Query2.Q4
FROM Query1 LEFT JOIN Query2 ON Query1.ID = Query2.ID;


Query4 selects the K-Code so the correct sign up information is used.

SELECT Signup.[K-code], Signup.Service, Signup.ID, Signup.Signup
FROM Signup
WHERE (((Signup.[K-code])=[forms]![form1]![Practice_List]));


Query5 links Query3 and Query4 using an outer join.

SELECT Query4.Signup, Query3.Q1, Query3.Q2, Query3.Q3, Query3.Q4
FROM Query4 RIGHT JOIN Query3 ON Query4.ID = Query3.ID;


I can provide a basic example if it will help.

 

[boblarson]

The error lies in that you have a form reference for a value in query 4. You need to add parameters to it so it can know about the form.

See this to see how it is used:

 

[boblarson]

The error lies in that you have a form reference for a value in query 4. You need to add parameters to it so it can know about the form.

See this to see how it is used:

 

[scubadiver007]

Hello Bob,

I have tried that but it unfortunately doesn't work.

 

[spikepl]

How precisely do you execute the export and from where? Is the form containg the controls referred to from the queries open at the time?

 

[boblarson]

If the criteria is on an underlying query that the crosstab is on, or vice-versa, I have found that sometimes it helps to have the parameters defined in both places.

 

[scubadiver007]

The module I am using to the export the query to the excel file is in an "on-click" event of a button.

No queries are open when I click on the form.

 

[boblarson]

No queries are open when I click on the form.

Each one of those queries runs when you click on the form (even though they do not display). So it is necessary to do what I said.

 

[scubadiver007]

These are the changes I've made to the queries.

Query 2

PARAMETERS [forms]![form1]![practice_list] Text ( 255 );
SELECT [ES Database figures].ID, [ES Database figures].Service, [ES Database figures].Indicator, [ES Database figures].Practice_Code, [ES Database figures].Q1, [ES Database figures].Q2, [ES Database figures].Q3, [ES Database figures].Q4
FROM [ES Database figures]
WHERE ((([ES Database figures].Practice_Code)=[forms]![form1]![practice_list]));

Query3

PARAMETERS [forms]![form1]![practice_list] Text ( 255 );
SELECT Query1.ID, Query1.Service, Query1.Indicator, Query2.Practice_Code, Query2.Q1, Query2.Q2, Query2.Q3, Query2.Q4
FROM Query1 LEFT JOIN Query2 ON Query1.ID = Query2.ID;

Query4

PARAMETERS [forms]![form1]![practice_list] Text ( 255 );
SELECT Signup.[K-code], Signup.Service, Signup.ID, Signup.Signup
FROM Signup
WHERE (((Signup.[K-code])=[forms]![form1]![practice_list]));

Query5

PARAMETERS [forms]![form1]![practice_list] Text ( 255 );
SELECT Query4.Signup, Query3.Q1, Query3.Q2, Query3.Q3, Query3.Q4
FROM Query4 RIGHT JOIN Query3 ON Query4.ID = Query3.ID;




I can open the query directly but I am unable to from clicking on the button.

I took out all the parameters and I can export the data from Query3 and Query5.

I then reinserted the criteria into just Query2 and tried the button to export Query2 but I am still getting the same error.

 

[boblarson]

Okay, here's another method which I use sometimes when I just can't get the parameters to work right. There are other methods but this is what I use. First off, download the SQL Tools from Access MVP Armen Stein's website:
http://www.jstreettech.com/files/basJStreetSQLTools.zip

And then import the basSQLTools module from that database to yours.

Next, in your code for the button you can use:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strNewWhere As String
 
Set db = CurrentDb
Set qdf = db.Querydefs("Query2")
 
strNewWhere = "WHERE [ES Database figures].Practice_Code =  " & Chr(34) & [forms]![form1]![practice_listSet & Chr(34)
 
qdf.SQL = ReplaceWhereClause(qdf.SQL, strNewWhere)
qdf.Close
 
Set qdf = Currentdb.QueryDefs("Query4")
 
strNewWhere = "Where Signup.[K-code] =  " & Chr(34) & [forms]![form1]![practice_list] & Chr(34) 
 
qdf.SQL = ReplaceWhereClause(qdf.SQL, strNewWhere)
 
qdf.Close
 
set qdf = Nothing

And you would then do the opening code just below that. And that should take care of it (you would remove, from each query, the parameters thing that I had you add as we are adding them this new way).

EDIT: P.S. - I assumed that the codes you had were text so if they are numeric remember to remove the & Chr(34) parts from the code.

 

[lnwinslow]

I have tried everything and still get this error. I must be doing something foolish. Can anyone help?

I have attached my code. I apologize for readability, I have been pulling my hair out!

Thanks in advance.

 

[boblarson]

Your code has so many errors it is hard to know where to start.

1. There are several places where you are appending the word WHERE to things that it is ridiculous. You only use ONE WHERE in the ENTIRE SQL Statement. Any more of them and it will cause an error.

2. You have this code running twice:
Set FE = Screen.ActiveForm.Form
3. This is not right:
SQLWhere = "" 'setting up SQLWhere as null
You don't need to set it up as an empty string which you are doing. If you don't have a Where Clause to build, then you don't include it. If you haven't assigned anything to it, it will already be an empty string.

4. Instead of this:
If Not (IsNull(FE![cboPartID])) Then
just use
If Len(FE!cboPartID & vbNullString) > 0 Then
and then include the part for the where clause but then you don't need the ELSE, you keep each part as a separate part.

5. In the ELSE you seem to want to go on down and add criteria which includes this code:
SQLWhere = " Where "
which will do as I said earlier - it will just end up having too many "Where" words in it. You only need to add the WHERE at the very, very end before concatenating.
6. You don't need to add the semi-colon on at the end.

So here's your code modified to be more efficient.
I took out a few commented out sections just to make it easier to read here. If you need some which I took out then you can put it back in, but if you do the sorting code, take a hint from the way I did the code for your where clause. And since the productID would not need anything else I put it at the very end instead of the beginning so it would just override everything before building the code. You could set it up I guess at the beginning and then bypass everything else with an Else, but I just thought it was easier to read this way.

[FONT=Courier New]Private Sub FindPart_Click()[/FONT]
[FONT=Courier New]   On Error GoTo Err_FindPart_Click[/FONT]
[FONT=Courier New]   '*****************************************[/FONT]
[FONT=Courier New]   Dim dumval As Variant[/FONT]
[FONT=Courier New]   Dim FE     As Form[/FONT]
[FONT=Courier New]   Dim MyDB   As Database[/FONT]
[FONT=Courier New]   Dim Myset  As DAO.Recordset[/FONT]
[FONT=Courier New]   Dim MySource As Form[/FONT]
[FONT=Courier New]   Dim Flag   As Integer   ' use to add AND to SQL string[/FONT]
[FONT=Courier New]   Dim VariantFlag As Variant[/FONT]
[FONT=Courier New]   Dim FormView As Variant[/FONT]
[FONT=Courier New]   Dim SQLWhere As String    ' LNW 7/26/2004[/FONT]
[FONT=Courier New]   Dim SQLText As String  '7/26/2004 moved to declarations sectin 7/29/2004[/FONT]
[FONT=Courier New]   'Public SQLText As String[/FONT]
[FONT=Courier New]   Dim SQLSort As String[/FONT]
[FONT=Courier New]   Dim SQLSource As String   '11/10/97[/FONT]
[FONT=Courier New]   Dim MyQDef As QueryDef[/FONT]
[FONT=Courier New]   Set MyDB = CurrentDb[/FONT]
 
[FONT=Courier New]   Set FE = Screen.ActiveForm.Form[/FONT]
[FONT=Courier New]   ' sql view of qryParts[/FONT]
[FONT=Courier New]   SQLSource = "SELECT tblParts.PartID, tblParts.PartName, tblParts.PartDescription, "[/FONT]
[FONT=Courier New]   SQLSource = SQLSource + "tblParts.MFGPartNum , tblParts.CategoryID, tblParts.SupplierID, "[/FONT]
[FONT=Courier New]   SQLSource = SQLSource + "tblParts.DeviceID, tblParts.UnitsInStock, tblParts.UnitsOnOrder, "[/FONT]
[FONT=Courier New]   SQLSource = SQLSource + "tblParts.ReorderLevel, tblParts.Discontinued, tblParts.LeadTime, "[/FONT]
[FONT=Courier New]   SQLSource = SQLSource + "tblParts.Frame, tblParts.Drawer, tblParts.Bin, tblParts.PricePerUnit, "[/FONT]
[FONT=Courier New]   SQLSource = SQLSource + "tblParts.PricePerBox, tblParts.DevUsedID, tblParts.LastUsed "[/FONT]
[FONT=Courier New]   SQLSource = SQLSource + "FROM tblParts"[/FONT]
 
 
 
[FONT=Courier New]   If Len(FE![cboPartName] & vbNullString) > 0 Then[/FONT]
[FONT=Courier New]       SQLWhere = "tblParts.tblPartName = " & Chr(34) & FE![cboPartName] & Chr(34) & " AND "[/FONT]
[FONT=Courier New]   End If[/FONT]
 
 
[FONT=Courier New]   If Len(FE![cboPartDescription] & vbNullString) > 0 Then[/FONT]
[FONT=Courier New]       SQLWhere = SQLWhere & "tblParts.PartDescription = " & Chr(34) & FE![cboPartDescription] & Chr(34) & " AND "[/FONT]
[FONT=Courier New]   End If[/FONT]
 
 
[FONT=Courier New]   If Len(FE![cboMFGPartNum] & vbNullString) > 0 Then[/FONT]
[FONT=Courier New]       SQLWhere = SQLWhere & "tblParts.MFGPartNum = " & FE![cboMFGPartNum] & " AND "[/FONT]
[FONT=Courier New]   End If[/FONT]
 
[FONT=Courier New]   ' If PartID entered ignore other items on form, PartID is unique[/FONT]
[FONT=Courier New]   If Len(FE!cboPartID & vbNullString) > 0 Then[/FONT]
[FONT=Courier New]       SQLWhere = "tblParts.[PartID] =" & FE.[cboPartID] & ") "[/FONT]
[FONT=Courier New]   End If[/FONT]
[COLOR=green]     ' remove the AND at the end if there is one[/COLOR]
[FONT=Courier New]   If Right(SQLWhere, 5) = " AND " Then[/FONT]
[FONT=Courier New]       SQLWhere = Left(SQLWhere, Len(SQLWhere) - 5)[/FONT]
[FONT=Courier New]   End If[/FONT]
 
[FONT=Courier New]   VariantFlag = Flag[/FONT]
[FONT=Courier New]   FormView = A_FORMDS[/FONT]
 
[FONT=Courier New]   SQLText = SQLSource & SQLWhere & SQLSort[/FONT]
 
[FONT=Courier New]   'MsgBox "SQLText " & SQLText  'debug only[/FONT]
[FONT=Courier New]   Set Myset = MyDB.OpenRecordset(SQLText, DB_OPEN_SNAPSHOT)[/FONT]
 
[FONT=Courier New]   'MsgBox "Myset.RecordCount = " & CStr(Myset.RecordCount)[/FONT]
[FONT=Courier New]   If Myset.RecordCount = 0 Then[/FONT]
[FONT=Courier New]       MsgBox "No Records Match this criteria, Resetting, Try different criteria **Note you must clear Criteria!"[/FONT]
[FONT=Courier New]       ' reset to last record source[/FONT]
[FONT=Courier New]       ' need to reset this to a default[/FONT]
[FONT=Courier New]       Screen.ActiveForm.RecordSource = "qryParts"[/FONT]
[FONT=Courier New]       'SQLEquipSub = 2  'set up dummy value returned.[/FONT]
[FONT=Courier New]       'Exit Function[/FONT]
[FONT=Courier New]   Else  'found valid data[/FONT]
 
[FONT=Courier New]       Screen.ActiveForm.RecordSource = SQLText[/FONT]
 
[FONT=Courier New]   End If[/FONT]
 
[FONT=Courier New]   If (dumval = 2) Then    'no records found[/FONT]
[FONT=Courier New]       Debug.Print "No matching records"[/FONT]
[FONT=Courier New]       Debug.Print "FE.RecordSource", FE.RecordSource[/FONT]
[FONT=Courier New]   End If[/FONT]
[FONT=Courier New]Exit_FindPart_Click:[/FONT]
[FONT=Courier New]   Exit Sub[/FONT]
[FONT=Courier New]   'Exit Function[/FONT]
[FONT=Courier New]   'Resume Exit_FindPart_Click[/FONT]
[FONT=Courier New]Err_FindPart_Click:[/FONT]
[FONT=Courier New]   MsgBox Err.Description[/FONT]
[FONT=Courier New]   MsgBox "in the error loop"[/FONT]
[FONT=Courier New]   MsgBox "Error in FindPart_Click " & Error$[/FONT]
[FONT=Courier New]   Resume Exit_FindPart_Click[/FONT]
[FONT=Courier New]   ' this will never fire:[/FONT]
[FONT=Courier New]   MsgBox "at the end of the error loop"[/FONT]
[FONT=Courier New]End Sub[/FONT]

 

[boblarson]

One other thing - if you are wanting to assign this to the form as its record source we'll need to modify the query's SQL using a querydef object.

 

[lnwinslow]

Thank you very much. I will let you know how it works.

 

[lnwinslow]

After fixing the code you sent me I am back to where I was with my code, I still get the "too few parameters" error.

I guess I need more detail on the qrydef because I do want the query to be the form source.

 

[boblarson]

It would be like this (after the SQLText is built) -

Set MyQDef = CurrentDb.QueryDefs("QueryNameHere")
 
MyQDef.SQL = SQLText
MyQDef.Close
Set MyQDef = Nothing
 
' then open the form