Parameter qry Error (3061) Parameter is TextBox on a form RRS feed

  • Question

  • I have a query that works perfectly until I put in parameters that are drawn form a textbox

    If I enter the line below it fails, if I enter a number directly in the parameters it works.


    The really odd thing is if I open the form and then execute the query manually it works, however when run from code it fails with above error number.

    I am using the following to execture the query from code:

    Dim dbOUT As dao.Database
    Dim rstProd As dao.Recordset
    Set dbOUT = CurrentDb()

    Set rstProd = dbOUT.OpenRecordset("qry_Offers_Out_Products")

    While any assistance would be appreciated, I really would like to understand exactly why this is failing.

    TIA Andrew

    Below is the SQL:

    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, tbl_Offers_Out_Products.OfferID
    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) INNER JOIN qry_Offers_Out_Products_Sub ON tbl_Products.ProductID = qry_Offers_Out_Products_Sub.ProductID
    WHERE (((tbl_Offers_Out_Products.OfferID)=[Forms]![frm_Offers_Out]![txt_OfferOutID]))
    ORDER BY [Description] & " - " & [VolWeight] & " - " & [VarDescription];

    Thursday, June 16, 2016 9:54 AM

All replies

  • DAO operates at a lower level than the Access interface. It "knows" only about tables and queries, not about forms.

    As a workaround, open the query in design view.

    • On the Design tab of the ribbon, in the Show/Hide group, click Parameters.
    • In the Parameter column, enter [Forms]![frm_Offers_Out]![txt_OfferOutID]
    • In the Data Type column, select the correct data type (presumably Long Integer).
    • Click OK, then save and close the query.

    Now change the code as follows:

        Dim dbOUT As DAO.Database
        Dim qdfProd As DAO.QueryDef
        Dim rstProd As DAO.Recordset
        Set dbOUT = CurrentDb
        Set qdfProd = dbOUT.QueryDefs("qry_Offers_Out_Products")
        qdfProd.Parameters("[Forms]![frm_Offers_Out]![txt_OfferOutID]").Value = _
        Set rstProd = qdfProd.OpenRecordset

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, June 16, 2016 1:50 PM