Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008
    Posts
    35

    Unanswered: Query based off a form control

    I like to make a query that will pull information based off of the value placed inside a control on my form. For instance, I want to see all of the records in my subform's table that match it's parent table's autonumber.
    I found this in my Google travels:
    Code:
    SELECT A.tblAnasaziStaff_LAST_NAME, A.STARTDATE
    FROM tbl2DaysAppts A
    INNER JOIN tbl2DaysAppts B ON A.tblAnasaziStaff_LAST_NAME = B.tblAnasaziStaff_LAST_NAME AND A.STARTDATE = B.STARTDATE
    WHERE A.tblAnasaziStaff_LAST_NAME In ('SCARDINO','WRIGHT')) 
    ORDER BY A.STARTDATE;
    ...and it seems to be a good start however I have to use a LEFT JOIN as opposed to an inner join. I'm at a standstill as to where to go from here. Any suggestions would be great.

    My code lies below:
    Code:
    SELECT [ECN Table].[ECN#], [BOM TrackingTBL].ID, [BOM TrackingTBL].Action, [BOM TrackingTBL].[Bill Number], [BOM TrackingTBL].[Projects Affected]
    FROM [ECN Table] LEFT JOIN [BOM TrackingTBL] ON [ECN Table].[ECN#] = [BOM TrackingTBL].ID;

  2. #2
    Join Date
    Sep 2005
    Posts
    19
    trying adding a "where" clause to the end of your sql statement - something like "where [ECN Table].[ECN#] = my value".

  3. #3
    Join Date
    Oct 2008
    Posts
    35
    I've done that but it requires inputting the ECN number in manually. I'm looking for a way to grab the ECN number off of the open form.

  4. #4
    Join Date
    Oct 2008
    Posts
    35
    I've found the answer for anyone else's future reference. If you want to query a table based on a control on a form, use this setup:
    ([Forms]![MyForm]![MyObject]) as the criteria in the query field of the parent table. The query will only run correctly from the open form, which is perfect, at least for me, because the form needs to be open.

Posting Permissions

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