Thread: Filtering report
01-18-04, 06:12 #1Registered User
- Join Date
- Jan 2004
Unanswered: Filtering report
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
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.
01-18-04, 06:46 #2Registered User
- Join Date
- Sep 2002
- South Wales
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
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
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