Parameter qry Error (3061) Parametr is TextBox on a form (1 Viewer)

andrewboast

New member
Local time
Today, 14:06
Joined
Jun 15, 2016
Messages
5
Hi
Just wondered if anyone could shed some light on the issue I am having. I have a saved parameter query that will not run when I point the parameter to a TextBox on a form. The error is 3061 "Too few parameters. Expected 1."

It is a number, if I enter the number directly in the sql then the query runs.

Both examples below are saved queries, I have tried building the sql at run time but get the same issue.

I have tried using VAL() for the parameter to make sure it is not coming through as a string.

Any clues would greatly appreciated, I have tried a 101 different ways and searched the net till my eyelids were falling off.

TIA

Andrew

This does not work.
SELECT tbl_Products_Barcodes.BarcodeID, [Description] & " - " & [VolWeight] & " - " & [VarDescription] AS Product, tbl_Offers_Out_Products.[Case QTY], tbl_Offers_Out_Products.[Pallet QTY], tbl_Offers_Out_Products.Available, tbl_Offers_Out_Products.CasePrice, Round([CasePrice]/[Case QTY],3) AS UnitCost, tbl_Products_Barcodes.EAN, tbl_Products.ProductID, tbl_Offers_Out_Products.PriceNotes
FROM (tbl_Products INNER JOIN tbl_Products_Barcodes ON tbl_Products.ProductID = tbl_Products_Barcodes.ProductID) INNER JOIN tbl_Offers_Out_Products ON tbl_Products_Barcodes.BarcodeID = tbl_Offers_Out_Products.BarcodeID
WHERE (((tbl_Offers_Out_Products.OfferID)=[Forms]![frm_Offers_Out]![txt_OfferOutID]))
ORDER BY [Description] & " - " & [VolWeight] & " - " & [VarDescription];

This Works.
SELECT tbl_Products_Barcodes.BarcodeID, [Description] & " - " & [VolWeight] & " - " & [VarDescription] AS Product, tbl_Offers_Out_Products.[Case QTY], tbl_Offers_Out_Products.[Pallet QTY], tbl_Offers_Out_Products.Available, tbl_Offers_Out_Products.CasePrice, Round([CasePrice]/[Case QTY],3) AS UnitCost, tbl_Products_Barcodes.EAN, tbl_Products.ProductID, tbl_Offers_Out_Products.PriceNotes
FROM (tbl_Products INNER JOIN tbl_Products_Barcodes ON tbl_Products.ProductID = tbl_Products_Barcodes.ProductID) INNER JOIN tbl_Offers_Out_Products ON tbl_Products_Barcodes.BarcodeID = tbl_Offers_Out_Products.BarcodeID
WHERE (((tbl_Offers_Out_Products.OfferID)=11667))
ORDER BY [Description] & " - " & [VolWeight] & " - " & [VarDescription];
 

JHB

Have been here a while
Local time
Today, 15:06
Joined
Jun 17, 2012
Messages
7,732
How do you run the query - by clicking it from the navigation window, or by some code, then show the code?
 

andrewboast

New member
Local time
Today, 14:06
Joined
Jun 15, 2016
Messages
5
Hi JHB
It is run from code using
Set rstProd = dbOUT.OpenRecordset("qry_Offers_Out_Products")
Regards
Andrew
 

andrewboast

New member
Local time
Today, 14:06
Joined
Jun 15, 2016
Messages
5
One other thing I forgot to mention, if I have the form open and then manually run the query it works?
 

JHB

Have been here a while
Local time
Today, 15:06
Joined
Jun 17, 2012
Messages
7,732
You need to set the value from the form using the Parameters and the QueryDef.
Change your query to below:
PARAMETERS TheParameter Long;
SELECT tbl_Products_Barcodes.BarcodeID, [Description] & " - " & [VolWeight] & " - " & [VarDescription] AS Product, tbl_Offers_Out_Products.[Case QTY], tbl_Offers_Out_Products.[Pallet QTY], tbl_Offers_Out_Products.Available, tbl_Offers_Out_Products.CasePrice, Round([CasePrice]/[Case QTY],3) AS UnitCost, tbl_Products_Barcodes.EAN, tbl_Products.ProductID, tbl_Offers_Out_Products.PriceNotes
FROM (tbl_Products INNER JOIN tbl_Products_Barcodes ON tbl_Products.ProductID = tbl_Products_Barcodes.ProductID) INNER JOIN tbl_Offers_Out_Products ON tbl_Products_Barcodes.BarcodeID = tbl_Offers_Out_Products.BarcodeID
WHERE (((tbl_Offers_Out_Products.OfferID)=[TheParameter]))
ORDER BY [Description] & " - " & [VolWeight] & " - " & [VarDescription];
And in your code:
Code:
 Dim qdf As dao.QueryDef
 Set qdf = dbOUT.QueryDefs("[B]YourQueryName[/B]")
 qdf.Parameters("TheParameter") = [Forms]![frm_Offers_Out]![txt_OfferOutID]
 Set rstProd = qdf.OpenRecordset()
Another way is the below:
Set rstProd = dbOUT.OpenRecordset("SELECT tbl_Products_Barcodes.BarcodeID, [Description] & ' - ' & [VolWeight] & ' - ' & [VarDescription] AS Product, tbl_Offers_Out_Products.[Case QTY], tbl_Offers_Out_Products.[Pallet QTY], tbl_Offers_Out_Products.Available, tbl_Offers_Out_Products.CasePrice, Round([CasePrice]/[Case QTY],3) AS UnitCost, tbl_Products_Barcodes.EAN, tbl_Products.ProductID, tbl_Offers_Out_Products.PriceNotes
FROM (tbl_Products INNER JOIN tbl_Products_Barcodes ON tbl_Products.ProductID = tbl_Products_Barcodes.ProductID) INNER JOIN tbl_Offers_Out_Products ON tbl_Products_Barcodes.BarcodeID = tbl_Offers_Out_Products.BarcodeID
WHERE (((tbl_Offers_Out_Products.OfferID)=" & [Forms]![frm_Offers_Out]![txt_OfferOutID] & "))
ORDER BY [Description] & ' - ' & [VolWeight] & ' - ' & [VarDescription];"
 

Users who are viewing this thread

Top Bottom