Filter by Form...How can the Report Reflect the Filter?

[Guest]

I would like to filter by form, and have my report reflect the filters. My
report currently always contains all of the data, regardless of my filter.

Thanks!
Bryan

 

[Allen Browne]

Place a command button on your form to fire off the report filtered the way
the form is. Use the Filter property of the form as the WhereCondition for
OpenReport.

Example:

Private Sub cmd_Preview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

If you are using Access 2002 or later, and there are combo boxes on the form
where the bound column is hidden and you filtered the form by those combos,
you may find the filter string contain a reference to "Lookup_..." To get
the report to work with this, you will need to alias the lookup tables the
same way in the report's query.

 

[Guest]

Allen-

I am using Access 2007.

I tried your suggestion, and I think it will work but I must be missing
something. Now when I filter the form and hit the button, I get a blank
report. It opens, just nothing on it. If I run the report without filtering,
I get the original data on the report that it was created with, not the whole
unfiltered table.

I'm sure it's a problem with my code...any ideas?

This is my first time playing with writing code....so thank you!

 

[Allen Browne]

After opening the report, press Ctrl+G to open the Immediate Window.

Enter:
? Reports![Report1].Filter
substituting your report name instead of Report1.

What does it say?
Does this make sense?
Essentially it has to be something that would work in the WHERE clause of a
query.

 

[Guest]

Thank you Allen!!

Now that I have this figured out, I have a couple questions on how the form
is displayed to the user...

1. I will create a button that allows a user to filter the columns by
"checking" the items to include for that column. (the window that pops up
when you click filter).

Currently, only the first filtered selection shows up on my form (probably
in Alphabetical order). If a user selects multiple items out of the check
box's, is there a way to program the box to say "Multiple" so the user does
not get confused and think they only have one seleted?

2. Is there a way to hide the data sheet part at the bottom of the form, as
this is irrevelant to the user, and does not need to be shown on the form?

Thanks again for the help!

After opening the report, press Ctrl+G to open the Immediate Window.

Enter:
? Reports![Report1].Filter
substituting your report name instead of Report1.

What does it say?
Does this make sense?
Essentially it has to be something that would work in the WHERE clause of a
query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen-

I am using Access 2007.

I tried your suggestion, and I think it will work but I must be missing
something. Now when I filter the form and hit the button, I get a blank
report. It opens, just nothing on it. If I run the report without
filtering,
I get the original data on the report that it was created with, not the
whole
unfiltered table.

I'm sure it's a problem with my code...any ideas?

This is my first time playing with writing code....so thank you!

 

[Allen Browne]

Not sure I follow what you are doing.

Re #1:
====
If you want a check box to select multiple records, you need a yes/no field
in your table.

If you want to select the fields to filter by, the best solution might be to
provide unbound search boxes for each relevant field (not all fields will be
relevant), and then build the filter string from only those boxes where the
user actually entered something. There's example code in the database you
can download from this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

If you wanted to allow the user to choose multiple values within one column,
you may be able to use a multi-select list box, as described here:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

Re #2:
====
You can set the Visible property of the Form Footer section to False if that
accomplishes what you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen!!

Now that I have this figured out, I have a couple questions on how the
form
is displayed to the user...

1. I will create a button that allows a user to filter the columns by
"checking" the items to include for that column. (the window that pops up
when you click filter).

Currently, only the first filtered selection shows up on my form (probably
in Alphabetical order). If a user selects multiple items out of the check
box's, is there a way to program the box to say "Multiple" so the user
does
not get confused and think they only have one seleted?

2. Is there a way to hide the data sheet part at the bottom of the form,
as
this is irrevelant to the user, and does not need to be shown on the form?

Thanks again for the help!

After opening the report, press Ctrl+G to open the Immediate Window.

Enter:
? Reports![Report1].Filter
substituting your report name instead of Report1.

What does it say?
Does this make sense?
Essentially it has to be something that would work in the WHERE clause of
a
query.

Allen-

I am using Access 2007.

I tried your suggestion, and I think it will work but I must be missing
something. Now when I filter the form and hit the button, I get a blank
report. It opens, just nothing on it. If I run the report without
filtering,
I get the original data on the report that it was created with, not the
whole
unfiltered table.

I'm sure it's a problem with my code...any ideas?

This is my first time playing with writing code....so thank you!

:

Place a command button on your form to fire off the report filtered
the
way
the form is. Use the Filter property of the form as the WhereCondition
for
OpenReport.

Example:

Private Sub cmd_Preview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

If you are using Access 2002 or later, and there are combo boxes
on the form where the bound column is hidden and you filtered the
form by those combos,
you may find the filter string contain a reference to "Lookup_..." To
get
the report to work with this, you will need to alias the lookup tables
the same way in the report's query.

I would like to filter by form, and have my report reflect the
filters.
My report currently always contains all of the data, regardless
of my filter.

 

[Guest]

Allen-

I will try to clarify a bit more on what I am trying to accomplish.

First off, I have a flat table, not relational. This is due to the fact that
it is linked to my ArcView GIS software. I am only using Access as a tool to
filter and print reports, not to manage input data and so on. So I have only
one large table that all of my information is stored in.

Each client of ours has multiple attributes that we keep track of, which is
what makes up our table. I have been searching for the best way to run a
query, and then be able to filter that query and generate a report from the
filtered results. For example, we may have a general query about the location
of the clients, but when I run the report I may only want Iowa and Minnesota
to be on the report, not everyone.....

But...

It has to allow me to choose more than one, which is why a combo box won't
work. And a list box gets too long. Which led me to the split form. What I
found works best for me is the ability to filter the fields by check box's.
For example, if I set my form up with the data sheet at the bottom of the
form, if I have the cursor inside of the column that I want to filter, I hit
the filter button from "Home" and it pops up a box that allows me to sort A
to Z, Z to A, etc. and also allows me to check which customers I want
included on the datasheet, which in turn show up on the report. I am using
Access '07 if that is different....

So....I was trying to create a button on the split form that would open up
that filter and allow the user to filter the columns, but it wouldn't let me.

Also, I could not find a way to hide the data sheet. There was not a false
option, only Yes and No. And No didn't do anything.

Thanks again for your help, sorry I am a bit naive with this yet!
Let me know if you need any clarification...and I look forward to your
response!

Bryan

Not sure I follow what you are doing.

Re #1:
====
If you want a check box to select multiple records, you need a yes/no field
in your table.

If you want to select the fields to filter by, the best solution might be to
provide unbound search boxes for each relevant field (not all fields will be
relevant), and then build the filter string from only those boxes where the
user actually entered something. There's example code in the database you
can download from this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

If you wanted to allow the user to choose multiple values within one column,
you may be able to use a multi-select list box, as described here:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

Re #2:
====
You can set the Visible property of the Form Footer section to False if that
accomplishes what you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen!!

Now that I have this figured out, I have a couple questions on how the
form
is displayed to the user...

1. I will create a button that allows a user to filter the columns by
"checking" the items to include for that column. (the window that pops up
when you click filter).

Currently, only the first filtered selection shows up on my form (probably
in Alphabetical order). If a user selects multiple items out of the check
box's, is there a way to program the box to say "Multiple" so the user
does
not get confused and think they only have one seleted?

2. Is there a way to hide the data sheet part at the bottom of the form,
as
this is irrevelant to the user, and does not need to be shown on the form?

Thanks again for the help!

After opening the report, press Ctrl+G to open the Immediate Window.

Enter:
? Reports![Report1].Filter
substituting your report name instead of Report1.

What does it say?
Does this make sense?
Essentially it has to be something that would work in the WHERE clause of
a
query.

Allen-

I am using Access 2007.

I tried your suggestion, and I think it will work but I must be missing
something. Now when I filter the form and hit the button, I get a blank
report. It opens, just nothing on it. If I run the report without
filtering,
I get the original data on the report that it was created with, not the
whole
unfiltered table.

I'm sure it's a problem with my code...any ideas?

This is my first time playing with writing code....so thank you!

:

Place a command button on your form to fire off the report filtered
the
way
the form is. Use the Filter property of the form as the WhereCondition
for
OpenReport.

Example:

Private Sub cmd_Preview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

If you are using Access 2002 or later, and there are combo boxes
on the form where the bound column is hidden and you filtered the
form by those combos,
you may find the filter string contain a reference to "Lookup_..." To
get
the report to work with this, you will need to alias the lookup tables
the same way in the report's query.

I would like to filter by form, and have my report reflect the
filters.
My report currently always contains all of the data, regardless
of my filter.

 

[Allen Browne]

The A2007 filter does offer you the multiple-check boxes on some fields.

If you want to simulate this with a command button, when you click the
command button, it will have focus. Consequently, you will need to set focus
to the previous control first, and then activate the filter menu. You'll
need to add error handling, but the basic idea is:
Screen.PreviousControl.SetFocus
RunCommand acCmdFilterMenu

I have not tried to hide the datasheet of a split form, so can't comment on
that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I will try to clarify a bit more on what I am trying to accomplish.

First off, I have a flat table, not relational. This is due to the fact
that
it is linked to my ArcView GIS software. I am only using Access as a tool
to
filter and print reports, not to manage input data and so on. So I have
only
one large table that all of my information is stored in.

Each client of ours has multiple attributes that we keep track of, which
is
what makes up our table. I have been searching for the best way to run a
query, and then be able to filter that query and generate a report from
the
filtered results. For example, we may have a general query about the
location
of the clients, but when I run the report I may only want Iowa and
Minnesota
to be on the report, not everyone.....

But...

It has to allow me to choose more than one, which is why a combo box won't
work. And a list box gets too long. Which led me to the split form. What I
found works best for me is the ability to filter the fields by check
box's.
For example, if I set my form up with the data sheet at the bottom of the
form, if I have the cursor inside of the column that I want to filter, I
hit
the filter button from "Home" and it pops up a box that allows me to sort
A
to Z, Z to A, etc. and also allows me to check which customers I want
included on the datasheet, which in turn show up on the report. I am using
Access '07 if that is different....

So....I was trying to create a button on the split form that would open up
that filter and allow the user to filter the columns, but it wouldn't let
me.

Also, I could not find a way to hide the data sheet. There was not a false
option, only Yes and No. And No didn't do anything.

Thanks again for your help, sorry I am a bit naive with this yet!
Let me know if you need any clarification...and I look forward to your
response!

Bryan

Not sure I follow what you are doing.

Re #1:
====
If you want a check box to select multiple records, you need a yes/no
field
in your table.

If you want to select the fields to filter by, the best solution might be
to
provide unbound search boxes for each relevant field (not all fields will
be
relevant), and then build the filter string from only those boxes where
the
user actually entered something. There's example code in the database you
can download from this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

If you wanted to allow the user to choose multiple values within one
column,
you may be able to use a multi-select list box, as described here:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

Re #2:
====
You can set the Visible property of the Form Footer section to False if
that
accomplishes what you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen!!

Now that I have this figured out, I have a couple questions on how the
form
is displayed to the user...

1. I will create a button that allows a user to filter the columns by
"checking" the items to include for that column. (the window that pops
up
when you click filter).

Currently, only the first filtered selection shows up on my form
(probably
in Alphabetical order). If a user selects multiple items out of the
check
box's, is there a way to program the box to say "Multiple" so the user
does
not get confused and think they only have one seleted?

2. Is there a way to hide the data sheet part at the bottom of the
form,
as
this is irrevelant to the user, and does not need to be shown on the
form?

Thanks again for the help!

:

After opening the report, press Ctrl+G to open the Immediate Window.

Enter:
? Reports![Report1].Filter
substituting your report name instead of Report1.

What does it say?
Does this make sense?
Essentially it has to be something that would work in the WHERE clause
of
a
query.

Allen-

I am using Access 2007.

I tried your suggestion, and I think it will work but I must be
missing
something. Now when I filter the form and hit the button, I get a
blank
report. It opens, just nothing on it. If I run the report without
filtering,
I get the original data on the report that it was created with, not
the
whole
unfiltered table.

I'm sure it's a problem with my code...any ideas?

This is my first time playing with writing code....so thank you!

:

Place a command button on your form to fire off the report filtered
the
way
the form is. Use the Filter property of the form as the
WhereCondition
for
OpenReport.

Example:

Private Sub cmd_Preview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

If you are using Access 2002 or later, and there are combo boxes
on the form where the bound column is hidden and you filtered the
form by those combos,
you may find the filter string contain a reference to "Lookup_..."
To
get
the report to work with this, you will need to alias the lookup
tables
the same way in the report's query.

I would like to filter by form, and have my report reflect the
filters.
My report currently always contains all of the data, regardless
of my filter.