Share via

No value given for one or more required parameters

Anonymous
2013-09-16T23:47:52+00:00

I have an Access VBA routine that uses a query with one of the criteria being a batch number from a form.  The routine worked for a week and then gave me a -2147217904 No value given for one or more required parameters error message.  After a great deal of testing I took the table, and created a new query and form in a blank db and it all worked for a few days and then gave me the same error.  I've take the query and written an SQL statement with the same result.  I created a new batch field in my table that is a text (string) instead of a long integer and it works.

Here is the SQL statement:

rst_Box.Open "SELECT Prod_Batch_Nbr, CustNum, PartNum, cpt_Crate, cpt_Box, cpt_OW, cpt_IH, cpt_Total_Order_Qty, cpt_Total_Ship_To_Qty" _

        & " FROM tbl_Production_Sched_Shutters" _

        & " WHERE [Prod_Batch_Nbr] = Forms!frm_D075_ShutterSelection.[cpt_Batch_Nbr] AND ([PartNum] = '" & str_Part1 & "' Or PartNum = '" & str_Part2 & "'Or PartNum = '" & str_Part3 & "');", _

         CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Here is the SQL statement with the batch number as a string:

rst_Box.Open "SELECT Prod_Batch_Nbr_String, CustNum, PartNum, cpt_Crate, cpt_Box, cpt_OW, cpt_IH, cpt_Total_Order_Qty, cpt_Total_Ship_To_Qty" _

        & " FROM tbl_Production_Sched_Shutters" _

        & " WHERE (([Prod_Batch_Nbr_String] = '" & Forms!frm_D075_ShutterSelection.[cpt_Batch_Nbr_String] & "') AND ([PartNum] = '" & str_Part1 & "' Or PartNum = '" & str_Part2 & "'Or PartNum = '" & str_Part3 & "'));", _

         CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2013-09-17T04:20:11+00:00

    The SQL search engine has no knowledge of VBA variables. You need to concatenate the value of the variable into the SQL string, not its name:

        rst_Box.Open "SELECT Prod_Batch_Nbr, cpt_Box, cpt_OW, cpt_IH" _

            & " FROM tbl_Production_Sched_Shutters" _

            & " WHERE Prod_Batch_Nbr = " & lng_Batch_Nbr & ";", _

             CurrentProject.Connection, adOpenDynamic, adLockOptimistic

    Now if lng_Batch_Nbr is equal to 2467 it will work, because you will get the same string as in your second example.

    3 people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-09-17T18:33:49+00:00

    Thanks John, that worked.  I've not written much SQL and was copying the SQL from the query which obviously does not work the same way.  The only reason I was using SQL was because the query quit working.  Anyway, this works, thank you very much.

    Lon

    0 comments
  2. Anonymous
    2013-09-17T01:05:06+00:00

    Thanks for the response. I've spent 8 hours on this with no luck.

    I cut the SQL to a very concise statement:

        rst_Box.Open "SELECT Prod_Batch_Nbr, cpt_Box, cpt_OW, cpt_IH" _

            & " FROM tbl_Production_Sched_Shutters" _

            & " WHERE Prod_Batch_Nbr = lng_Batch_Nbr;", _

             CurrentProject.Connection, adOpenDynamic, adLockOptimistic

    lng_Batch_Nbr does not reference the form at all now but is hard coded with still the same result.

    And this one works:

    rst_Box.Open "SELECT Prod_Batch_Nbr, cpt_Box, cpt_OW, cpt_IH" _

            & " FROM tbl_Production_Sched_Shutters" _

            & " WHERE Prod_Batch_Nbr = 2467;", _

             CurrentProject.Connection, adOpenDynamic, adLockOptimistic

    0 comments
  3. Anonymous
    2013-09-17T00:19:04+00:00

    Where are str_Part1, str_Part2 and str_Part3 defined? Might any of them be NULL or zero length strings? How many digits are there in the PartNum field (a Long Integer is limited to 2^31-1 = 2147483647)? As a rule identifiers such as part numbers should be stored in Text fields rather than any sort of number).

    0 comments