berm100
asked on
How to change record source in a report via vba
I have the following question.
I have a report that is suposed to run once for a series of 5 vendors. Right now I have 5 reports; one for each of the 5 vendors. Each report has a query as its record source.
I have rpt_vendor1, rpt_vendor2, rpt_vendor3, rpt_vendor4, and rpt_vendor5 as my report names.
The query names are:
qry_vendor1
qry_vendor2
qry_vendor3
qry_vendor4
qry_vendor5
What I would like to do is open a report in design view using a Docmd. After the report is open, I want to be able to change the record source and run the report for each of the 5 vendors, in a loop if possible
I can open the report, but cannot figure out how to change the record source, print the report, change the record source for the next vendor and print the report, etc.
I tried a few things using me! but could not get anything to work.
Please help
Berm
I have a report that is suposed to run once for a series of 5 vendors. Right now I have 5 reports; one for each of the 5 vendors. Each report has a query as its record source.
I have rpt_vendor1, rpt_vendor2, rpt_vendor3, rpt_vendor4, and rpt_vendor5 as my report names.
The query names are:
qry_vendor1
qry_vendor2
qry_vendor3
qry_vendor4
qry_vendor5
What I would like to do is open a report in design view using a Docmd. After the report is open, I want to be able to change the record source and run the report for each of the 5 vendors, in a loop if possible
I can open the report, but cannot figure out how to change the record source, print the report, change the record source for the next vendor and print the report, etc.
I tried a few things using me! but could not get anything to work.
Please help
Berm
You could call each in sucession passing the query name in the open args (like rockmuncher suggested), the code here will open the report in previewe mode and wait until the user closes it before previewing the next one
Call OpenReportAndWait("rptVend or", strArgs)
I would use slightly different test for the report open event
Private Sub Report_Open(Cancel As Integer)
If Len(Me.OpenArgs & vbNullString) > 0 Then
Me.Record1Source = Me.OpenArgs
End If
End Sub
Private Sub OpenReportAndWait(ReportNa me As String, OpenArgs As String)
Dim lngLoop As Long
Dim blnClosed As Boolean
DoCmd.OpenReport ReportName:=ReportName, _
View:=acViewPreview, _
OpenArgs:=WhereCondition
'loop until user closes the report
Do
'only check every 1000 loops
If lngLoop Mod 1000 Then
'let foreground process execute
DoEvents
'is the report still open?
If Reports.Count > 0 Then
blnClosed = False
Else
blnClosed = True
Exit Do
End If
lngLoop = 0
End If
lngLoop = lngLoop + 1
Loop
End Sub
Steve
Call OpenReportAndWait("rptVend
I would use slightly different test for the report open event
Private Sub Report_Open(Cancel As Integer)
If Len(Me.OpenArgs & vbNullString) > 0 Then
Me.Record1Source = Me.OpenArgs
End If
End Sub
Private Sub OpenReportAndWait(ReportNa
Dim lngLoop As Long
Dim blnClosed As Boolean
DoCmd.OpenReport ReportName:=ReportName, _
View:=acViewPreview, _
OpenArgs:=WhereCondition
'loop until user closes the report
Do
'only check every 1000 loops
If lngLoop Mod 1000 Then
'let foreground process execute
DoEvents
'is the report still open?
If Reports.Count > 0 Then
blnClosed = False
Else
blnClosed = True
Exit Do
End If
lngLoop = 0
End If
lngLoop = lngLoop + 1
Loop
End Sub
Steve
ASKER
Rockmuncher,
When you interrogate, I have a few questions:
You say to use the code:
if me.openargs & ""<>"" then me.recordsource=me.openarg s
I tried this a variety of ways and it did not work. What does ""<>"" mean? Was I supposed to use this exact syntax or am I not understanding something. Also, should this statement come below next like in your example or above next or does it not matter.
Thanks
When you interrogate, I have a few questions:
You say to use the code:
if me.openargs & ""<>"" then me.recordsource=me.openarg
I tried this a variety of ways and it did not work. What does ""<>"" mean? Was I supposed to use this exact syntax or am I not understanding something. Also, should this statement come below next like in your example or above next or does it not matter.
Thanks
rockmuncher was saying that you need to add code to your report's Open event and both our code examples do the same thing ...
mine
Private Sub Report_Open(Cancel As Integer)
If Len(Me.OpenArgs & vbNullString) > 0 Then
Me.Record1Source = Me.OpenArgs
End If
End Sub
his
Private Sub Report_Open(Cancel As Integer)
If Me.OpenArgs & "" <> "" Then Me.RecordSource = Me.OpenArgs
End Sub
Steve
mine
Private Sub Report_Open(Cancel As Integer)
If Len(Me.OpenArgs & vbNullString) > 0 Then
Me.Record1Source = Me.OpenArgs
End If
End Sub
his
Private Sub Report_Open(Cancel As Integer)
If Me.OpenArgs & "" <> "" Then Me.RecordSource = Me.OpenArgs
End Sub
Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
for i = 1 to 5
myQueryName="qry_vendor" & i
docmd.OpenReport "reportname",,,,,myQueryNa
next
Then interrogate the OpenArgs property within the report's Open event:
if me.openargs & "" <> "" then me.recordsource=me.openarg