using docmd.openReport to open multiple instances for diff. criterias

[cayomat]

hi everyone,

I have tried to open the multiple instances of the same report with different criterias. but I can only open the first one.

my code is simply like this:

docmd.openreport "report1",acpreview,, "select=87"
docmd.openreport "report1",acpreview,, "select=1"
this code only opens the report where select field is 87.

but if I use this:
docmd.openreport "report1",acpreview,, "select=87"_
or "select=1"

then I can open 2 of them.

but my problem is, I have alot of selection criteria. and when database becomes extra large. then I can not use this type of criteria because the limitation of 32000 char.

is there any one who can help me.
I will be very happy, if i can find a solution...
thank you...
cayomat
)

 

[randysmid]

Hi,
I have had this problem, and came up with a different solution. Instead of trying to pass all of the criteria to the report as a filter, I decided to use "make table" queries to retrieve only the data I wanted. Because of the complexity involved in some of these, I used multiple queries where I was either appending, or updating, the new subtable.
Here is a sampling of what my code looked like:
strRptName = "MonthlyClose_ActBatch"
DoCmd.OpenQuery "qryMonthlyAllRept1"
DoCmd.OpenQuery "qryMonthlyActBatchRept1"
DoCmd.OpenQuery "qryMonthlyActBatchRept2" 'makes tblTemp2
DoCmd.OpenReport strRptName, acViewPreview

The queries used criteria based on fields found on this form, e.g. lstMonth. The report used the final table (tblTemp2) as its' recordsource.

HTH,
Randy Smith
California Teachers Association

 

[danvlas]

Create the sub:

Sub FilterMyReport(Crt As String)
Dim rpt As Report
Set rpt = Reports("YourReportName&quot
'you can even say:
'Set rpt = Screen.ActiveReport
rpt.Filter = crt
rpt.FilterOn = True
Set rpt = Nothing
End Sub

Open the report unfiltered
Then call the sub repeteadly:

Call FilterMyReport("YourCriteriaStringHere&quot

You can use
ftp://ftp.artrom.ro/SearchForm.zip to do this for any report or form or datasheet.

Good luck

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[cayomat]

Randy and Daniel,
thank you for your help. I have a lot of problems and things to do, in these days. For this reason I could not try your solutions. but when I have a time, I try and give you feedback. thank you again.
)
cayomat