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

Carrying form name through to add to filter in OnOpen event in report

P: n/a
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.
Jun 27 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Jun 27 '08 #2

P: n/a
On May 9, 5:54 pm, fredg <fgutk...@example.invalidwrote:
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
Hi Fred,

Thanks for your thorough reply. Lots of useful tips in there.

I am using the DoCmd.OutputTo command in this instance however, as
part of a process which generates a pdf file directly from a button on
the form.

Is there a way of combining the DoCmd.OutputTo command with the OnOpen
event to replicate the filtering you describe in the DoCmd.OpenReport
command Where statement?

Thanks again,

Franc.
Jun 27 '08 #3

P: n/a
On Tue, 13 May 2008 15:57:08 -0700 (PDT), franc sutherland wrote:
On May 9, 5:54 pm, fredg <fgutk...@example.invalidwrote:
>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

Hi Fred,

Thanks for your thorough reply. Lots of useful tips in there.

I am using the DoCmd.OutputTo command in this instance however, as
part of a process which generates a pdf file directly from a button on
the form.

Is there a way of combining the DoCmd.OutputTo command with the OnOpen
event to replicate the filtering you describe in the DoCmd.OpenReport
command Where statement?

Thanks again,

Franc.
I don't create .pdf documents so, unless you are using Access 2007
which does, the best I can do is show you how to output it as .rtf and
you can go from there.

Change the below Where clause to your needs.
Change field and report names as needed.

Dim strWhere As String
strWhere = "[LastName] = """ & [LastName] & """"
DoCmd.OpenReport "ReportName", acViewPreview, , strWhere
DoCmd.SelectObject acReport, "ReportName", False

DoCmd.OutputTo acOutputReport, "ReportName", acFormatRTF,
"C:\Documents and Settings\Owner.MYCOMPUTER\Desktop\YourDocName.rtf"

The above will output the previewed report (which has been filtered on
the LastName field shown on the current form record) as a .rtf file.

I hope this helps.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Jun 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.