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

andrewboast

New member
Local time
Today, 04:28
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];
 
How do you run the query - by clicking it from the navigation window, or by some code, then show the code?
 
Hi JHB
It is run from code using
Set rstProd = dbOUT.OpenRecordset("qry_Offers_Out_Products")
Regards
Andrew
 
One other thing I forgot to mention, if I have the form open and then manually run the query it works?
 
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

Back
Top Bottom