Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    Globalized
    Posts
    14

    Question Unanswered: Filtering report

    Hi,

    I'm trying to achieve the following,

    A button on a form. Click on this button to call up a report for a particular client id. This client id comes from the form.

    The query that the report is based on is as follows,

    SELECT Clients.client_id, Clients.agency_name, Projects.description, Format([date_due],"mmmm") AS MonthName, Projects.fee
    FROM Clients INNER JOIN Projects ON Clients.client_id = Projects.client_id
    GROUP BY Clients.client_id, Clients.agency_name, Projects.description, Format([date_due],"mmmm"), Projects.fee
    HAVING (((Clients.client_id)=[id]));

    The code for the button is,

    stDocName = "Sales Reports"
    stQueryName = "SalesReport"
    stLinkCriteria = "[id ]=" & Me![client_id]
    DoCmd.OpenReport stDocName, acViewPreview, stQueryName, stLinkCriteria


    The problem: when I click on button it pops up a dialog box wanting me to enter the id !!!

    Clearly there is a problem with the above code. But where?

    Thanks for any help with this. It's driving me insane.

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb OK, Here we go

    If the report is ONLY called by this form.

    Change the query so that it refers to the control on the form with the client it:

    Under the client ID in the query put

    Forms!NameOfYourForm.NameOfTheTextBoxOrComboWithCl ientID

    THEN

    Change the code behind the button to

    Forms!NameOfYourForm.visible = false 'This hides the form
    stDocName = "NAME OF YOUR REPORT"
    DoCmd.OpenReport stDocName, acPreview 'This opens the report

    IN THE REPORT_CLOSE EVENT, PUT THIS CODE

    Private Sub Report_Close()

    Forms!NAMEOFYOURFORM.Visible = True

    End Sub

    THIS SHOULD DO THE TRICK, WHEN THE REPORT IS CLOSED, THE USER WILL BE "BACK ON THE FORM"
    Last edited by garethdart; 01-18-04 at 06:55.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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