simultaneously generate separate reports with filtered results

[mklitwin]

I'm not sure if I'm explaining this correctly but an example would probably make it clearer.

I'm looking to generate separate reports (with the same layouts) however the "filtered field" is different on each. For example, the 3 fields I want in the report are - "responsibility", "entity name", "reporting type". So, I want to be able to filter by responsibility ie, ny, moscow, etc and generate a separate report for each of the possible "responsibilities", returning its respective entities and their corresponding reporting type (all these items are contained in a query)

Ie moscow could be responsible for multiple entities and each entity has its own reporting requirements, so there would need to be a moscow summary report, a ny report, and so on for all the other possible "responsibilities"

Report 1:
Responsibility Entity Name Reporting Type
Moscow Entity 1 8858
Moscow Entity 2 8865
Moscow Entity 5 8858

Report 2:
Responsibility Entity Name Reporting Type
NY Entity 3 8850
NY Entity 4 8865
NY Entity 6 8860

Thanks

 

[CBrighton]

DoCmd.OpenReport has an arguement / parameter for filtering the results.

You can use a string in VBA to hold the contents of multiple controls on a form which detail criteria, or you could loop through a set list of criteria (like the different locations you mention) and open a report for each of them.

 

[mklitwin]

Thanks for the reply - im not too familiar with this level of expertise in access so if possible could you give me detailed instructions on doing this?

Best

 

[CBrighton]

Sure, need more info on exactly what you want first though.

If you can give me a few example pieces of criteria (and confirm whether the criteria are static or dynamic, i.e. a user inputs the criteria into a textbox) I can throw together some code to give you an idea.

 

[mklitwin]

Great .. so bascially this is how my db is set up.. i have a form that populates a table/query... basically a user will select options from a combo box and it will populate the record, i need to be able to filter the fields (and only select certain ones to include in the report) and generate a separate report for each.. i would think my data is pretty static? im not sure exactly what you mean, its a very simple database where we just store the details about a specific entity and its reporting/filing requirements.

what would be great is - if you know how pivots in excel work, you can select one, many, or all the possible items to display and then create a table from that - that type of interface would be perfect... only i would need to have each item in a separate report - ie we need to send moscow their report and ny their report...

technically (for now) the only thing we need to filter by is "responsibility - ie ny/moscow/etc.. but it would be great to be able to have this type of report where we can filter by any of the fields and then generate separate reports for them - in otherwords instead of filtering by responbility, we want a separate report for all entities that file and 8858 and then the ones that file an 8860 etc etc..

i hope this helps?

 

[CBrighton]

I'm busy now so no time to look at this today I'm afraid.

Will read tomorrow, assuming it's not solved by then.

 

[mklitwin]

By using this method http://support.microsoft.com/kb/208548 i was able to filter my query and generate a report .. However it seems that i will have to run my filter multiple times to generate this query for each responsible party... so is there a way to automate this to automatically create a separate report for each of the possible selected items..

ie if i want to filter by "responsible party" whose possible options are: moscow, ny, etc.. right now using the method above and filter by form, i am able to filter by moscow and generate a report, but i need to be able to have the option to select moscow and ny, and then have the report run for each individually?

 

[CBrighton]

Well that KB article shows you how to use the filter parameter when using OpenReport, which is the method I was going to suggest. So that's certainly a good start!

So, if you want to loop through a static list of criteria then what I would do is create a loop within the VBA.

Going by my quick skim of the link you posted you should have a command button doing something like this (I'll use the code from the link):

    If Me.Filter = "" Then
        MsgBox "Apply a filter to the form first."
    Else
        DoCmd.OpenReport "rptCustomers", acViewPreview, , Me.Filter
    End If
End Sub

Now, we are not using the filter of the form as the filter of the report so we can get rid of all of that except for the OpenReport line.

What we need to do then is create a list of the criteria required. An easy way to do this is to create a table to hold the various filters you want to apply. This has the advantage that if you need to add additional criteria (either to existing ones or as a new report) all you have to do is open the table rather than going through code.

This table only needs 1 field, though you can add an autonumber PK if you wish. I'd expect the records to be things like "[Responsible Entity] = NY", "[Responsible Entity] = Moscow", etc. Note the square brackets if the field has a space.

With that done you have a list of criteria and the command to open the report with a filter, so we just need to access that table in the VBA and program it to loop through each record, opening the report each time using the field value as the filter.

Here's some finished code:

dim db as Database
dim rst as Recordset
 
set db = CurrentDB
set rst = db.OpenRecordset("SELECT * FROM tblReportCriteria"
 
If rst.eof then
    msgbox "No criteria records found"
    Exit sub
End If
 
rst.movefirst
 
do until rst.eof
    DoCmd.OpenReport "rptCustomers", acViewPreview, , rst!Criteria
    rst.movenext
Loop

You will need to alter the table, field & report name, but you get the idea I hope.

 

[mklitwin]

i'm going to give this a stab and let you know how it goes... fingers crossed

 

[mklitwin]

where im gettin a bit stuck is on the "create a new table" should the field on the table have "[Responsible Entity] = NY" written in the "cell" if you will? - is it possible to use the query itself that has this data in there.. since the reponsible party list may change?

and the code you gave, do we put that in the VBA of the form we're using..

what happens to the "on click" of the first code you listed - do we erase that and replace it with your bottom code (in which case what do we click to generate the report)

but before i go this far.. is it possible to make a "multi-select" box instead of automatically choosing every possible party?

clearly im not familiar with this coding so i thank you for your patience

 

[CBrighton]

where im gettin a bit stuck is on the "create a new table" should the field on the table have "[Responsible Entity] = NY" written in the "cell" if you will? - is it possible to use the query itself that has this data in there.. since the reponsible party list may change?

and the code you gave, do we put that in the VBA of the form we're using..

what happens to the "on click" of the first code you listed - do we erase that and replace it with your bottom code (in which case what do we click to generate the report)

but before i go this far.. is it possible to make a "multi-select" box instead of automatically choosing every possible party?

clearly im not familiar with this coding so i thank you for your patience

OK, a lot of questions!

Yes, another way to do it could be a listbox with multi-select on.

If you will only ever be filtering on the location then you could set the row source to something like "SELECT DISTINCT [Responsible Entity] FROM TableName" which should populate the listbox with each unique value from that field.

You can then change the loop from looping through a recordset to looping through the listbox selections, however you will also have to change the OpenReport to take into account the lack of field name in the listbox.

e.g.

Dim oItem As Variant
For Each oItem In Me!lstEntities.ItemsSelected
    DoCmd.OpenReport "rptCustomers", acViewPreview, , "[responsible entity] = " & lstEntities.ItemData(oItem)
Next oItem

 

[mklitwin]

you seem to know your stuff - so... if this is what we wanted to do, could you give me the exact code (adn where to put it?)

id like to have some sort of multi-select box that gives us the option to filter based on any field in our query/table where all the data comes from.. and then ultimately select the fields i want in the report.. pretty much im trying to work out some type of "pivot table" interface and then it spit out separate reports for each..

and when you say "[responsible entity] - im gettin a bit lost ... i should just send you this file and you could do it in 5 seconds! ha (though id rather understand it myself but it may be hopeless)

 

[CBrighton]

If you upload a copy of your database and detail exactly what you want it to do (write what you want to code to do in pseudo-code including the names of queries being used and fields being checked as appropriate) I can have a look.

I try not to do any substantial code writing without seeing the database as I don't have the knowledge of the databases's tables, queries, relationships, etc.

Make sure it's in mdb format though as I use Access 2003.

 

[mklitwin]

yeah i'm using access 2003 - ill give this a shot within the next day or two and will definitely give it my best shot at explaining it!

Though im sure you can see the relationsihps and youll be quite surprised how simple it is..

Thanks!

--- apparently work jsut got very busy... so it may be sometime (though i'm sure youll get the email notification when its posted later!) - Thanks