Filtering a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need a macro that can filter a report based on multiple criteria for the
same field. I have users that have multiple regions that they have access
to. They should only see that information for which they have access. I
don't know the best way to apply the filter. I have a usertable that houses
their user name and password. Since some users have multiple regions, I
don't know how to store and/or pass that information into the query that the
report is based upon.
 
Karen, you will need to be comfortable with VBA to achieve this.

1. OpenRecordset() into the table that lists the regions for the user.

2. Loop through the records, building up a comma-delimited string of the
regions.

3. Use the IN operator with this string, and then use the string as the
WhereCondition for OpenReport.

For an example of looping through the items to build the string, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

The code and target string will be very similar, though you are looping the
records in the recordset rather than the ItemsSelected in the listbox.
 
Since I am not well versed in VBA, can you tell me what you mean by
OpenRecordset() and how that is written?
 
For information on how to use OpenRecordset:
1. Press Ctrl+G to open the Immediate Window.
2. Type OpenRecordset
3. Press F1

You will need to understand some VBA - e.g. variables, objects, loops, and
how to refer to objects - to achieve this.
 
Back
Top