Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query with form control as parameter not working (2003)

    Hi, I have a problem query. It looks at a text box on a form to get the value it needs for a where clause. A simple thing that I have set up many times before. Unfortunately, this query is not working because it cannot seem to resolve the value of the text box. If I remove the form reference from the crieria pane of the query, the query brings back lots of records. If I replace the form reference with the value that I know is in the text box on the form, the query works (i.e., it brings back the data that I am expecting). The query is called from code. At the point where it is called I have stopped the code and used the immediate window to show the value returned by the form reference that I am using in the criteria pane (? [Forms]![frm_EnterPickResults]![BatchIDx]) and the expected data (in this case a batch ID) comes back. If I copy this batch ID, that I have acquired in the immediate window, back into the query - the query works.

    As far as I can tell, the form control reference is not being resolved by the query, even though it is resolved if typed into the immediate window. A very similar query, that is run from code in the previous line, which uses the same form control as a parameter, works fine.

    Is this a bug?

    This is the query that does not work:

    INSERT INTO tbl_Packaging ( BatchID, OrderID, apar_id, LineNum, PackType, ProdCode, QtyToPick, PickDate )
    SELECT tbl_PickAndPackBatchDetail.BatchID, tbl_PickAndPackBatchDetail.order_id, tbl_PickAndPackBatchDetail.apar_id, tbl_PickAndPackBatchDetail.line_no, "Pouches" AS PackType, qry_Relation_Pouches.rel_value, Sum(tbl_PickAndPackBatchDetail.QtyPicked) AS SumOfQtyPicked, First(Date()) AS PickDate
    FROM (tbl_PickAndPackBatchDetail LEFT JOIN qry_AlgArticleClient ON tbl_PickAndPackBatchDetail.article = qry_AlgArticleClient.article) LEFT JOIN qry_Relation_Pouches ON qry_AlgArticleClient.article_id = qry_Relation_Pouches.article_id
    WHERE (((qry_Relation_Pouches.rel_value) Is Not Null And (qry_Relation_Pouches.rel_value)<>"0" And (qry_Relation_Pouches.rel_value)<>"") AND ((tbl_PickAndPackBatchDetail.BatchID)=[Forms]![frm_EnterPickResults]![BatchIDx]) AND ((tbl_PickAndPackBatchDetail.QtyPicked)>0))
    GROUP BY tbl_PickAndPackBatchDetail.BatchID, tbl_PickAndPackBatchDetail.order_id, tbl_PickAndPackBatchDetail.apar_id, tbl_PickAndPackBatchDetail.line_no, "Pouches", qry_Relation_Pouches.rel_value;

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Query with form control as parameter not working (2003)

    How are you calling the query from code? That may make a difference.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query with form control as parameter not working (2003)

    Hi Hans,

    Both the query that works, and the one that doesn't, are called using DoCmd.OpenQuery "QueryName"

    Ian

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Query with form control as parameter not working (2003)

    I'm afraid I'd have to see the database then. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,783
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query with form control as parameter not working (2003)

    Ian,

    I have had something similar to this happen in databases where multiple update type queries are run one after one another. All of the queries seemed fine, however, to get them to work when being launched by code, I had to put in a docmd.close query queryname to close the queries before running the next one. Never did figure out what the issue was but it worked.

    Just a shot in the dark here, but try using trim with your data to get rid of any trailing spaces that you dont see. Also make sure you dont have conflicting data types. HTH
    Gary

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query with form control as parameter not working (2003)

    Gary,
    That looks very interesting. I must admit that this is not the first time that I have seen this problem, it's just the first time that I couldn't get around it by getting the query parameters from somewhere else. I'm not in the office today but I will check the query closing trick tomorrow.
    Hans,
    If Gary's tip doesn't work I'll send in a database. As I say, I have seen this before and would like to know what is going on.

    Thanks
    Ian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •