Report Filter

[helpseeker]

Hello.
I am using Access2000
Report rptPrintLabels uses a query qryLabelData as recordsource.
rptPrintLabels: Filter = Yes
qryLabelData uses multiple functions.

When this report is printed by itself, all records are printed.

This report can also be called from - usually to print one label, where I
use this syntax:
stDocName = "rptPrintLabels"
StrFilter = "Select * from qryLabelData where ID = " & Me.txtID
DoCmd.OpenReport stDocName, acViewPreview, StrFilter

Report_open event:
intRecs = DCount("[ID]", Me.RecordSource)
If intRecs < 1 Then
MsgBox "No Records found. Cancelling report", vbOKOnly + vbInformation,
"Cannot run Report"
Cancel = True
Else
....
End If


When the report is called from the form only one record is displayed (as
expected)
All of this works fine, EXCEPT that I have noticed the following:
1. for the DCount statement, the entire query is executed, complete with all
function calls (ignoring the filter)
2. After the Report Open and before Detail_Print Event, the entire query is
run again.

Question:
If possible, I'd like to eliminate this double execution of the entire
query - unnecessary processing and resulting delay
Me.RecordCount and Me.RecordSetClone are not available

I'd rather not create a copy of qryLabelData with a criteria of
Forms!...txtID as I would have to create multiple such queries because I'd
like to have a single point of maintenence.

Thanks in advance for the assistance.

 

[helpseeker]

Marsh,
Thanks for the comments ,and for info on NoData event. Whole
Dcount concept thrown out the window.

One other thing:
When the report opens, users are prompted with an input box to enter a
PrintDate (defaults to today, can be a few days in the future)
This date value is written back to the table.
If I was printing only one record (or a subset defined by the where clause),
the PrintDate should be written back for the subset only.

Does this mean taht I have to execute an update statement in the detail
event - for current record? I would have rather have executed one update
statement in the Report Open event for the filtered subset, but that does
not seem possible.



| See comments interspersed below.
| --
| Marsh
| MVP [MS Access]
|
|
| helpseeker wrote:
| >I am using Access2000
| >Report rptPrintLabels uses a query qryLabelData as recordsource.
| >rptPrintLabels: Filter = Yes
| >qryLabelData uses multiple functions.
| >
| >When this report is printed by itself, all records are printed.
| >
| >This report can also be called from - usually to print one label, where I
| >use this syntax:
| >stDocName = "rptPrintLabels"
| >StrFilter = "Select * from qryLabelData where ID = " & Me.txtID
| >DoCmd.OpenReport stDocName, acViewPreview, StrFilter
|
| It think using the WhereCondition argument is cleaner than
| using the FilterName argument:
|
| stDocName = "rptPrintLabels"
| StrWhere = "ID = " & Me.txtID
| DoCmd.OpenReport stDocName, acViewPreview, ,StrWhere
|
|
| >Report_open event:
| >intRecs = DCount("[ID]", Me.RecordSource)
| >If intRecs < 1 Then
| > MsgBox "No Records found. Cancelling report", vbOKOnly +
vbInformation,
| >"Cannot run Report"
| > Cancel = True
| >Else
| >...
| >End If
| >
| >When the report is called from the form only one record is displayed (as
| >expected)
| >All of this works fine, EXCEPT that I have noticed the following:
| >1. for the DCount statement, the entire query is executed, complete with
all
| >function calls (ignoring the filter)
| >2. After the Report Open and before Detail_Print Event, the entire query
is
| >run again.
| >
| >Question:
| >If possible, I'd like to eliminate this double execution of the entire
| >query - unnecessary processing and resulting delay
| >Me.RecordCount and Me.RecordSetClone are not available
|
| Throw out the whole DCount concept and use the
| Report_NoData event:
|
| MsgBox "No Records found. Cancelling report", _
| vbOKOnly + vbInformation, "Cannot run Report"
| Cancel = True
|

 

[Marshall Barton]

Marsh,
Thanks for the comments ,and for info on NoData event. Whole
Dcount concept thrown out the window.

One other thing:
When the report opens, users are prompted with an input box to enter a
PrintDate (defaults to today, can be a few days in the future)
This date value is written back to the table.
If I was printing only one record (or a subset defined by the where clause),
the PrintDate should be written back for the subset only.

Does this mean taht I have to execute an update statement in the detail
event - for current record? I would have rather have executed one update
statement in the Report Open event for the filtered subset, but that does
not seem possible.

I prefer to do this kind of Update in the form. Add the
code to the print button's click event right after you've
set the StrWhere string.

dtPrint = CDate(InputBox( . . . ))
CurrentDb.Execute "UPDATE thetable " & _
"SET PrintDate=" & Format(dtPrint,"\#\/m\/d\/yyyy\#") & _
"WHERE " & StrWhere, dbFailOnError

If you feel it is important to keep the code in the report's
Open event, then you can use the report's Filter property to
retrieve the string that was specified in the openReport's
Where Condition argument.
--
Marsh
MVP [MS Access]

| See comments interspersed below.
|
| helpseeker wrote:
| >I am using Access2000
| >Report rptPrintLabels uses a query qryLabelData as recordsource.
| >rptPrintLabels: Filter = Yes
| >qryLabelData uses multiple functions.
| >
| >When this report is printed by itself, all records are printed.
| >
| >This report can also be called from - usually to print one label, where I
| >use this syntax:
| >stDocName = "rptPrintLabels"
| >StrFilter = "Select * from qryLabelData where ID = " & Me.txtID
| >DoCmd.OpenReport stDocName, acViewPreview, StrFilter
|
| It think using the WhereCondition argument is cleaner than
| using the FilterName argument:
|
| stDocName = "rptPrintLabels"
| StrWhere = "ID = " & Me.txtID
| DoCmd.OpenReport stDocName, acViewPreview, ,StrWhere
|
|
| >Report_open event:
| >intRecs = DCount("[ID]", Me.RecordSource)
| >If intRecs < 1 Then
| > MsgBox "No Records found. Cancelling report", vbOKOnly +
vbInformation,
| >"Cannot run Report"
| > Cancel = True
| >Else
| >...
| >End If
| >
| >When the report is called from the form only one record is displayed (as
| >expected)
| >All of this works fine, EXCEPT that I have noticed the following:
| >1. for the DCount statement, the entire query is executed, complete with
all
| >function calls (ignoring the filter)
| >2. After the Report Open and before Detail_Print Event, the entire query
is
| >run again.
| >
| >Question:
| >If possible, I'd like to eliminate this double execution of the entire
| >query - unnecessary processing and resulting delay
| >Me.RecordCount and Me.RecordSetClone are not available
|
| Throw out the whole DCount concept and use the
| Report_NoData event:
|
| MsgBox "No Records found. Cancelling report", _
| vbOKOnly + vbInformation, "Cannot run Report"
| Cancel = True

 

[helpseeker]

Marsh,
Again, thanks for the reply.
I have to have the prompt in the report because the report can be executed
by itself (in which case all labels are printed)
I am currently stepping through code that launches the report from the form.
I am using the where condition as you suggested.
(not the filter)
In the immediate window, ?me.filter returns an empty string ; ?me.filterOn
returns True
is something wrong?

I would prefer to print the reports through forms, if I could force the user
to use the forms - i.e. report fails with a message, asking the user to use
the forms - even pops it open ...

Since Access2000 does not support OpenArgs for reports - (XP does),
I cannot check for openargs and force the user to print the reports through
the forms.
I can think of a few ways to force users to print using forms -
a. set the Report's datasource to an empty table which is populated by the
form and is emptied on Report Close or
b. write to a global variable or table which is reset on report close
(is there a less convoluted way?)

 

[Marshall Barton]

Marsh,
Again, thanks for the reply.
I have to have the prompt in the report because the report can be executed
by itself (in which case all labels are printed)
I am currently stepping through code that launches the report from the form.
I am using the where condition as you suggested.
(not the filter)
In the immediate window, ?me.filter returns an empty string ; ?me.filterOn
returns True
is something wrong?

I don't know, but the filter property is something that has
a bad taste for me. When I tried using it this way, it had
the WhereCondition string in the report's Filter property,
but the FilterOn property was False. Don't ask me to
explain it (unless you were checking the form's Filter
property?).

I would prefer to print the reports through forms, if I could force the user
to use the forms - i.e. report fails with a message, asking the user to use
the forms - even pops it open ...

The first thing I would do is hide the database window using
Tools - Start Up menu. That won't stop anyone that knows
even a little about getting around in Access, but at least
it should make it clear that you don't support whatever
happens when they unhide the window and open the report
manually.

FWIW, I also disable the main menu bar and almost all the
tool bars to make it clear to the users that they do it my
way or they're on their own.

Since Access2000 does not support OpenArgs for reports - (XP does),
I cannot check for openargs and force the user to print the reports through
the forms.

I can think of a few ways to force users to print using forms -
a. set the Report's datasource to an empty table which is populated by the
form and is emptied on Report Close or

Yes, that would also thwart devious users. You can use the
report's open event to check if the form is open. Then, if
it is, grab the where stuff from the form and stuff the
appropriate SQL into the report's RecordSource property.
You don't need an empty table, just clear the report's
RecordSource property after you're done testing.

b. write to a global variable or table which is reset on report close
(is there a less convoluted way?)

I suppose that would also do it, but I try real hard to stay
away from global variables and a special table sounds too
complicated to me.