Applying a filter on a report

[Guest]

Hi,

I have a customer form on which I have a button to open a report. When I
open a report, I would like to apply a filter to the report, so that it shows
only the data for the customer being viewed in the form.

Here is the code that Access generated.

Dim stDocName As String

stDocName = "Portfolio Profitability Report"
DoCmd.OpenReport stDocName, acPreview

And this is what I added trying to apply a filter.

stDocName.Filter = "customer_id = Me.id"
st.DocName.FilterOn = True

Obviously, it doesn't work and I was hoping someone could show me how to do
it.

Many thanks.

 

[Keith Wilby]

Hi,

I have a customer form on which I have a button to open a report. When I
open a report, I would like to apply a filter to the report, so that it
shows
only the data for the customer being viewed in the form.

Here is the code that Access generated.

Dim stDocName As String

stDocName = "Portfolio Profitability Report"
DoCmd.OpenReport stDocName, acPreview

And this is what I added trying to apply a filter.

stDocName.Filter = "customer_id = Me.id"
st.DocName.FilterOn = True

Obviously, it doesn't work and I was hoping someone could show me how to
do
it.

Many thanks.

One of the arguments of the OpenReport method is WhereCondition. See this
for more info:

http://msdn2.microsoft.com/en-us/library/aa220304(office.11).aspx

Keith.
www.keithwilby.com

 

[fredg]

Hi,

I have a customer form on which I have a button to open a report. When I
open a report, I would like to apply a filter to the report, so that it shows
only the data for the customer being viewed in the form.

Here is the code that Access generated.

Dim stDocName As String

stDocName = "Portfolio Profitability Report"
DoCmd.OpenReport stDocName, acPreview

And this is what I added trying to apply a filter.

stDocName.Filter = "customer_id = Me.id"
st.DocName.FilterOn = True

Obviously, it doesn't work and I was hoping someone could show me how to do
it.

Many thanks.

You rally should have looked up the OpenReport method in VBA help!
You would use the OpenReport's Where argument.

Your table should have a unique prime key field.
In my example it is named [RecordID].
Code the command button's click event:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
Where Clause + Restrict data to a subset of records'