By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,474 Members | 3,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,474 IT Pros & Developers. It's quick & easy.

Opening multiple instances of same Report - Update?

P: n/a
I've been reading all the posts on this topic. Most are years old, so
I have 2 questions:

1. Is there any improvement on opening the same report multiple times
(with different input parameters on each) with Access 2000?

2. In the post listed below (from March 1998) M G Foster posed a
solution that makes sense to me and looks like something I could learn.
HOWEVER, the code snippet refers to getting the input parameters from
a list box, and I am getting mine from a query.

Google Post Reference:
http://groups.google.com/group/comp....7a222561994b3a

Can anyone help me make the transition?

I am trying to run a report for one Merchant at a time. My query
selects the MerchantKey from tblMerchant, based on a field in the
table. MerchantKey is the only output field in my query, and is a
hidden field on the report. My code is (with help from other posts!~):

If Not rs.EOF Then
rs.MoveFirst 'Move to first record
Do While Not rs.EOF
lngMerchantKey = rs("MerchantKey") 'set variable from
field
' MsgBox lngMerchantKey
DoCmd.OpenReport strDocName, acPreview, , "[MerchantKey]="
& lngMerchantKey
rs.MoveNext
Loop
End If

I can see (with my message box) that I am looping through the merchants
as I want, I just can't get the report to open once for each merchant.
(Once open, the user will pdf each report for its merchant).
Thanks
sara

Jan 24 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"sara" <sa*******@yahoo.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:
I've been reading all the posts on this topic. Most are years
old, so I have 2 questions:

1. Is there any improvement on opening the same report
multiple times (with different input parameters on each) with
Access 2000?


nope, you'll find the same issue as in '97, which is the code
opens one report, and immediately goes through the loop and
opens the second, which closes the first, etc till the end.

I found a function, somewhere, called fFormIsOpen() that I
modified to block the opening of the next report while the first
is open. That code is at work, I'm at home or I'd post it.
Jan 25 '06 #2

P: n/a
This will open multiple instances of a report. I don't know if you can
adapt it to your particular needs.

The HasModule property of the Report must be True.

Dim aCustomers(3) As Report_Customers

Sub OpenCustomers()
Dim z As Long

CleanupCustomers

For z = 0 To 2
Set aCustomers(z) = New Report_Customers
Next z

With aCustomers(0)
.Caption = "All Customers"
.Visible = True
End With

With aCustomers(1)
.Caption = "CUSTOMERID LIKE 'B*'"
.Filter = "CUSTOMERID LIKE 'B*'"
.FilterOn = True
.Visible = True
End With

With aCustomers(2)
.Caption = "Country='Canada'"
.Filter = "Country='Canada'"
.FilterOn = True
.Visible = True
End With

DoCmd.RunCommand acCmdWindowCascade

End Sub

Sub CleanupCustomers()
Dim z As Long
On Error Resume Next
For z = 0 To 2
DoCmd.Close acReport, "Customers", acSaveNo
If Not aCustomers(z) Is Nothing Then Set aCustomers(z) =
Nothing
Next z
On Error GoTo 0
End Sub

Jan 25 '06 #3

P: n/a
Well, gentlemen, I did finally get something to work. It's a
combination of lots of things - connected by researching more posts,
based on what you had here.

I think it will do what I need. I don't know if it's "kosher" and I'm
sure it's not bullet-proof. BUT, I'm posting the code so you can see
what I did and maybe others can modify it for their use. It meets our
needs (and we only have 1 or 2 users).
Public Sub subRptBYMerchant()

' Run the R: Merchant Weekly Dept - Store Order only for the merchants
' who have "YES" in the MerchRpt field on tblMerchant

' Run for each merchant with MerchRpt = YES

Dim strDocName As String
Dim strStepErrorMsg As String

Dim lngMerchantKey As Long

Dim rs As Recordset
Dim strName As String
Dim db As Database
Dim strPathAndFile As String
Dim strPath As String
Dim strFile As String

On Error GoTo subRptBYMerchant_Err

strDocName = "rptMerchantWeeklyDeptStoreOrder"

strStepErrorMsg = "There was a problem with Merchant report by
Merchant"

Set db = CurrentDb

' This query holds the merchant keys that have RPT = Yes
Set rs = db.OpenRecordset("qrySelMerchForRpt")

' Set the path to put the snapped report for each merchant, so it can
be renamed and moved
strPath = "\\server-03\Building19\SalesAudit\HoldMerchRpt\"

' Default name the file 0.snp - this file should never be created
strFile = lngMerchantKey & ".snp"
strPathAndFile = strPath & strFile
If Not rs.EOF Then
rs.MoveFirst 'Move to first
record and create report
Do While Not rs.EOF
lngMerchantKey = rs("MerchantKey") ' set variable
from field
' Open the report just for this merchant
DoCmd.OpenReport strDocName, acPreview, ,
"[MerchantKey]=" & lngMerchantKey
strFile = lngMerchantKey & ".snp"
strPathAndFile = strPath & strFile
DoCmd.OutputTo acOutputReport, strDocName, "snapshot
format", _
strPathAndFile
DoCmd.Close acReport, strDocName
rs.MoveNext

Loop
End If

rs.Close

Set rs = Nothing
Set db = Nothing

subRptBYMerchant_Exit:
Exit Sub

subRptBYMerchant_Err:
MsgBox strStepErrorMsg & vbCrLf & vbCrLf & Err.Number & " " &
Err.Description
Resume subRptBYMerchant_Exit

End Sub

Again, thanks. I feel so accomplished when I get something to work
that saves my users minutes, but this saves them more like an hour!

Sara

Jan 25 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.