The 4th parameter (WhereCondition) of the OpenReport method is the way I
would go with this report.
Basically, you setup a single report so that it will print all of the
companies (looks like you already have this), then, you pass the OpenReport
method a WHERE clause to restrict the recordset.
Hope these two examples give you an idea of how to address this.
Dale
Private Sub cmd_FilterA_Click
'This assumes your report includes a [CompanyName] field
docmd.openreport "yourReport",,, "[CompanyName] Like 'A*'"
End Sub
Private Sub cmd_ListFilter_Click
'This is setup to handle a multi-select listbox.
Dim varItem as Variant
Dim varList as Variant, varWhere as variant
'This assumes that the bound column of the listbox is 0 and that this
'column contains a numeric value. If the bound column is text, use the
'Second line inside the For Next loop, rather than the first
For each varItem in me.lst_Filter.ItemsSelected
varList = (varList + ",") & me.lst_Filter.Column(0, varItem)
'varList = (varList + ",") & ("'" & me.lst_Filter.column(0,varItem)
& "'")
Next
varWhere = "[CompanyCategoryID] IN (" + varList + ")"
docmd.openReport "yourReport",,,varWhere
End Sub
--
Email address is not valid.
Please reply to newsgroup only.
I was Woundering if some one could point me in the right direction in
creating a button that will open my report,
This Difference with this button, is that the form has 2 lots a filter
option associated to it.
The First Type Is a Macro Type A-Z Alphabetical FIilter,
The Second Type is a List Box Filter, With Marco SQL Code In it, This
List Box then Filters Companys Via Service eg Ductwork Contractors.
What I Want is the Button To Print All Related Filtered Companys. So
if a Filterd "A" All (A) Companys Would Be Printed Only On My Report.
Or If I Clicked "Ductwork" on The List Box, Only All (Ductwork)
Companys Would Be Printed On My Report.
I've Managed To Set Up a Button To Just Print Current Record Via
Report,
And Ive Managed To Do a Print All Button But This Prints The Whole
Table Contents of Companys.
Dan Cawthorne
Thanks For Getting Back Dale,
Had a Play With The Two Lots of Codes You Gave Me,
The First Code That You Gave Me, Seem To Only filters Out Company's
Which I Set In The Like Bit!
And doesnt Effect What Ive Filtered Out On The From
So if i have The Following Code: docmd.openreport "Rrpt_Suppliers",,,
"[Company] Like 'A*'"
and i Filter The B Contacts on my form it still Prints The Company's
Beginning With A
For This To Work Id Have Create a Small Form, and Have 27 Report
Buttons With All Stating a Different Like Letter.
Is There away round that i can carry the filter over?
The Second Code For The List Box Im Getting The Following Error
is
Run Time Error '3075':
Syntax Error In String In Query Expression '('[ServiceID] IN
(,SDU))'. (SDU is The ServiceID for Ductwork
and ServiceID is The Bound Column)
This Is The Adopted Code For my data base
Private Sub Command47_Click()
'This is setup to handle a multi-select listbox.
Dim varItem As Variant
Dim varList As Variant, varWhere As Variant
'This assumes that the bound column of the listbox is 0 and that
this
'column contains a numeric value. If the bound column is text,
use the
'Second line inside the For Next loop, rather than the first
For Each varItem In Me.List0.ItemsSelected
varList = (varList + ",") & Me.List0.Column(0, varItem)
'varList = (varList + ",") & ("'" &
me.List0.column(0,varItem)& "'")
Next
varWhere = "'[ServiceID] IN (" + varList + ")"
DoCmd.OpenReport "Rrpt_Suppliers", acViewPreview, , varWhere
End Sub
The Service Table Consists of to Fields
Service Code and Service
Which On The Suppliers Table I have a Service Field Which Looks Up The
Bound Column of The Service Table.
To Create My List Box Which Named "List0" Date Source is SELECT
Suppliers_Service_Type.[Service Code], Suppliers_Service_Type.[Service
Description] FROM Suppliers_Service_Type;
I Created a Query and in the Query I Added The CompanyID From The
Suppliers Table and The Added The Service ID From The A Table Called
Supplier_Service_Link (This Table has The CompanyID and ServiceID in
it and are Both Primary Keys - It Allow me to have Multiply Services
Assigned To Each Comany ID)
Then In The Query I Then Went To View as SQL And Then Copied The
Following code into a Marco Filter WHERE Condition,
SELECT Suppliers.CompanyID
FROM Suppliers INNER JOIN Supplier_Service_Link ON
Suppliers.CompanyID=Supplier_Service_Link.CompanyID
WHERE (((Supplier_Service_Link.ServiceID)=forms!suppliers!List0));
And The Applied The Macro To The after Update on the Listbox "List0"
But Just Going Through that Code ive Notice on My Link Table I Have
The Service Code Named ServiceID But On The Service Table it Self I
have The Field Called Service Code,
But I Cant See That Causing The Problem Cause The Filter Works On The
Forms.
