Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: Passing parameters to Query/report

    Hey ppl,

    I have a report which gets its data from a query. I then have an onClick event on the form and what I am trying to do is send the JobID (which is a textbox on the form) to the report so that it doesn't display the prompt box. I can't do it through the WHERE parameter of the Macro to open the report (it doesn't seem to pick it up, maybe because the JobID is in the SQL as an @JobID parameter).

    Here's the code to the SQL which may better explain it (@JobID 4th line from bottom). The SQL works fine, just need to automatically pass the JobID into this.

    -------------------------------------------------------------
    SELECT Customer,Phone_Make,Imei,Date,Job_Ref,Pallet
    FROM tbl_Imei AS i, tbl_Pallets AS j, tbl_Jobs AS k, tbl_Phones AS l
    WHERE i.PalletID=j.PalletID
    AND j.JobID=k.JobID
    AND k.PhoneID=l.PhoneID
    AND Imei IN
    (SELECT a.Imei FROM tbl_Imei AS a, tbl_Pallets AS b
    WHERE a.Imei In
    (SELECT Imei FROM tbl_Imei GROUP BY Imei HAVING COUNT(*) > 1)
    AND b.JobID=@JobID
    AND a.PalletID=b.PalletID
    GROUP BY a.Imei)
    ORDER BY Imei,Date,Pallet;
    -------------------------------------------------------------

    Hope this is kinda clear to someone, cos I have no idea where to start on this one.

    Many thanks

    Kam

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: Passing parameters to Query/report

    Originally posted by jammykam
    Hey ppl,

    I have a report which gets its data from a query. I then have an onClick event on the form and what I am trying to do is send the JobID (which is a textbox on the form) to the report so that it doesn't display the prompt box. I can't do it through the WHERE parameter of the Macro to open the report (it doesn't seem to pick it up, maybe because the JobID is in the SQL as an @JobID parameter).

    Here's the code to the SQL which may better explain it (@JobID 4th line from bottom). The SQL works fine, just need to automatically pass the JobID into this.

    -------------------------------------------------------------
    SELECT Customer,Phone_Make,Imei,Date,Job_Ref,Pallet
    FROM tbl_Imei AS i, tbl_Pallets AS j, tbl_Jobs AS k, tbl_Phones AS l
    WHERE i.PalletID=j.PalletID
    AND j.JobID=k.JobID
    AND k.PhoneID=l.PhoneID
    AND Imei IN
    (SELECT a.Imei FROM tbl_Imei AS a, tbl_Pallets AS b
    WHERE a.Imei In
    (SELECT Imei FROM tbl_Imei GROUP BY Imei HAVING COUNT(*) > 1)
    AND b.JobID=@JobID
    AND a.PalletID=b.PalletID
    GROUP BY a.Imei)
    ORDER BY Imei,Date,Pallet;
    -------------------------------------------------------------

    Hope this is kinda clear to someone, cos I have no idea where to start on this one.

    Many thanks

    Kam

    Are you saying that the query is prompting the user for a value?

    If so and you don't want to change your query, could you dynamically

    assign the Recordsource for the report when opening from the form?

    I am visualizing this report being run from the form as well as by itself

    where the user is prompted for the JobID.

    Hope this helps.

    Gregg

  3. #3
    Join Date
    Oct 2003
    Posts
    3
    Hi Gregg

    Thats right the query is prompting for @JobID, which is the variable in the query. The report won't run by itself, it should always be called from the form since the databse window is not displayed on startup and therefore the users shouldn't really be browsing round the tables/forms/reports unless it is through the forms.

    I don't think I can change the query, due to the level of nesting involved (its the only I could think of achieving what I needed).

    How do I dynamically assign the recordsource to the report?

    Cheers
    Kam

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445
    Originally posted by jammykam
    Hi Gregg

    Thats right the query is prompting for @JobID, which is the variable in the query. The report won't run by itself, it should always be called from the form since the databse window is not displayed on startup and therefore the users shouldn't really be browsing round the tables/forms/reports unless it is through the forms.

    I don't think I can change the query, due to the level of nesting involved (its the only I could think of achieving what I needed).

    How do I dynamically assign the recordsource to the report?

    Cheers
    Kam
    Got you're reply. Let me check it out tonight and get back with you.

    Gregg

  5. #5
    Join Date
    Oct 2003
    Posts
    3
    Cheers Gregg but I did a search and found what I needed.

    It seems all I needed to do was assign the form variable in the query, so just changed

    b.JobID=@JobID to read b.JobID=[Forms]![frm_Jobs]![JobID]

    Never knew you do this, thought it was only SQL you could put in these. Thanks for the reply though, shortened the search by a bit


    Kam

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445
    Originally posted by jammykam
    Cheers Gregg but I did a search and found what I needed.

    It seems all I needed to do was assign the form variable in the query, so just changed

    b.JobID=@JobID to read b.JobID=[Forms]![frm_Jobs]![JobID]

    Never knew you do this, thought it was only SQL you could put in these. Thanks for the reply though, shortened the search by a bit


    Kam
    Glad it worked out. Talk to ya later.

    Gregg

Posting Permissions

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