473,324 Members | 2,178 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

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
3 2609
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Dalan | last post by:
Okay, I have worked on this and then some, but cannot seem to crack it. So if someone can straighten my code out, or suggest a new approach, then I'm all ears. Here goes: I have two tables - one...
1
by: Dalan | last post by:
I designed a dialog box with a combo to select individual vendors from a form and its related data for print ouput. Though the dialog box seems to work okay, I apparently do not have the filtering...
9
by: Brendan MAther | last post by:
I have a table called Contact_Info. I have a form that allows me to show all the contacts from a specified city and sector. Once these contacts appear on my new form I would like to be able to...
4
by: DebbieG | last post by:
I have a form based on this query: SELECT Students.LastSerDT, OtherInfo.Served, OtherInfo.HSGradYr, OtherInfo.ActivePart, OtherInfo.Served, Students.SSN, & ", " & & " " & AS Name,...
3
by: sea | last post by:
I need to be able to print the current form because the form has an embedded object of a .gif file, so report will not work -- does anyone know the way to do this? I tried some code, not sure what...
8
by: | last post by:
hi, i have a form on which a user can choose specific criteria such as dates etc, in order to filter the report that is called from the form. i do this by using the Where section of the...
1
by: Jimmy Stewart | last post by:
I have a continuous form with a list of items from a table. One of the fields in the table is "print". this allows the user to select items from the list on the form for printing in a report. on...
4
by: Jimmy | last post by:
Quick question for you. I'm using a small form for users to input a range of dates for a report (similar to the date range forms used in MS templates) that has an ok and a cancel button. How can I...
5
by: DAHMB | last post by:
Hi all, Using Access 2007 I have a report called Sunday School Attendance based on a Query called qryAttendance the query is as follows: SELECT tblSundaySchoolAttendance.StudentID,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.