Seeing report filter in report open procedure

[ruth]

I want to use a different record source depending on whether my report was
filtered or not. If I request the report from the Student form, I want to use
qryBill for a single ID. If it's from the report form, I want to use
qryBilling, which selects based on criteria on the report form. I don't want
to have two versions of the report. My record source for the report is
qryBilling as a default.

On the student form I have the following code:
Private Sub PrintBill_Click()
On Error GoTo Err_PrintBill_Click

Dim stDocName As String
Dim PrintOption As Integer
Dim strWhere As String

If Me.PreviewBill Then PrintOption = 2
stDocName = "rptBilling"
strWhere = "ID=" & Me.ID

DoCmd.OpenReport stDocName, PrintOption, , strWhere

Exit_PrintBill_Click:
Exit Sub

Err_PrintBill_Click:
MsgBox Err.Description
Resume Exit_PrintBill_Click

End Sub

In the Open procedure of the rptBilling report I have:

Private Sub Report_Open(Cancel As Integer)
If Me.FilterOn Then Me.RecordSource = "qryBill"
End Sub

This doesn't seem to be working. qryBilling is always called and fails
because my report form is not open. When I debug, FilterOn is false and
Filter is "". How can I tell that I have passed a WHERE clause to the report?
I am using Access 2000.

 

[Duane Hookom]

Is there a reason why you don't always use a Where Condition so you can use
the same record source? Another option would be to change the SQL of a
single, saved query prior to opening the report.

 

[tina]

try adding an OpenArgs argument to the OpenReport action in the Student
form, as

DoCmd.OpenReport stDocName, PrintOption, , strWhere, 1

add the argument to the OpenReport action in the report form also, but set
the argument to zero (0) instead of 1.

then replace your code in the report's Open event procedure with the
following, as

Private Sub Report_Open(Cancel As Integer)

If Me.OpenArgs = 1 Then Me.RecordSource = "qryBill"

End Sub

i've never tried to change a report's RecordSource and then apply a WHERE
clause supplied by an OpenReport action, so i don't know how well that's
going to work. if it doesn't, suggest you explore the possibility of putting
the WHERE criteria in qryBill directly, rather than applying it in the
OpenReport action at runtime.

hth

 

[John Spencer]

Another option is to use the third argument - which allows you to specify a
different query as the record source for the report.

One call would be

DoCmd.OpenReport "rptBilling", PrintOption, "qryBill"

and the other call would be
DoCmd.OpenReport stDocName, PrintOption, , strWhere

Although as Duane pointed out, you should be able to use the strWhere clause
in either case to acheive what you want.

The requirement for specifying a different query as the source for the report
is that the query must contain at least all the fields that are used in the
report. It can have additional fields but those will be ignored.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[ruth]

Thank you all for your replies, even though I couldn't use them. A single
query would have been nice, but far too complicated. The original qrybilling
is complicated enough without adding to it. The same would be true of a where
clause to replace all the join conditions of the first query (actually a
string of queries). I tried using the third parameter, but it does not
replace the record source. Apparently, as I had thought, it simply replaces
the where condition. Also, as far as I can see, the OpenArgs argument exists
for a form but not a report.

What I decided to do instead was check to see which forms were loaded. That
information is available in the open procedure of the reports. It is now:

Private Sub Report_Open(Cancel As Integer)
If Not CurrentProject.AllForms("frmReports").IsLoaded Then Me.RecordSource
= "qryBill"
End Sub

This works fine.

 

[John Spencer]

Strange it does work for me. I did have a bit of a problem during testing - I
forgot to close the report before attempting to run the test. If the report
was open in design view, Access opened the report, but ignored the argument.
Once I closed the report completely, then my test code worked as described in
the help.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[tina]

Also, as far as I can see, the OpenArgs argument exists

for a form but not a report.

i'm guessing you're using Access97 then; OpenArgs was introduced for reports
in A2000.