On Fri, 9 May 2008 05:17:12 -0700 (PDT), franc sutherland wrote:
Hello,
I have a report which I filter using the me.filter command in the
OnOpen event.
Me.Filter = "OrderID=" & Forms!variable_form_name![OrderID]
Me.FilterOn = True
I want to be able to open that report, filtered, from different
forms. How do I carry the name of the current form into the
'variable_form_name' part of the filter so that the report looks at
the current form to get the OrderID number and complete the filter?
I've tried pulling the form name through using
application.currentobjectname and putting it into a string, but it
didn't like that.
Many thanks,
Franc.
That's not the way to filter a report.
Remove whatever you have in the Report's Filter property.
Set FilterOn to No.
If the only difference between the reports is how it is filtered, then
simply use the Where clause argument of the OpenReport method to
filter the report differntly.
Let's assume FormA is used to open the report filtered on an [ID]
field.
You wish to have the report filtered according to the record currently
on the form, the customer number [ID] (a Number datatype).
Code a Command button's click event:
DoCmd.OpenReport "MonthlySales",acViewPreview, , "[ID] = " & Me.[ID]
only records for whichever customer is currently displayed on the form
will appear in the report.
Now lets say you wish to open the same report, but this time showing
the all customers in a specific city (shown in the form's current
record) but within a specific date range.
Add 2 unbound control to the same form.
Set their format to any valid date format.
Name one control "EnterStart". Name the other control "EnterEnd".
Code a different button's Click event (or use If .. Then logic on the
same event to get the correct filter):
DoCmd.OpenReport "MonthlySales",acViewPreview, , "[CityID] = " &
Me.[CityID] & "[DateField] Between #" & Me.[EnterStart] & "# and #" &
Me.[EnterEnd] & "#"
Enter the dates in the above controls.
Click the command button.
The same report will now return only records for that [CityID] within
the entered range of dates.
All of tha above code assumes [ID] and [CityID] are a Number datatyoe
field.
You have used just 1 form and 1 report, filtered according to which
code is run in the button click event.
See VBA help on Where Clause + Restrict data to a subset of records
for more information on the correct syntax used for different datatype
field values.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail