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

Filter a report based on same filter on a form

P: n/a
Hi,

I have a form based on a table. When I filter the form I want to run a
report based on the same table with the same filter as the form. No problem
until I want to filter a combo box where the text value is on a different
table. The me.filter is then a text instead of the id-number from the lookup
table. This causes the report to prompt for the parameter.

How do I get by this problem? Do I need to create a temporary table? I
rather not. or is there another solution without using filter?
Appreciate any help
Richard
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Sounds like the combo's bound column is zero width?

You should still be able to use the value from the invisible column in the
WhereCondition for your OpenReport. Just use the Value of the combo.

It is also possible to use its Column() property if necessary. (That's
zero-based.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard" <rw**********@chello.se> wrote in message
news:dMQpb.883$%W3.1336@amstwist00...

I have a form based on a table. When I filter the form I want to run a
report based on the same table with the same filter as the form. No problem until I want to filter a combo box where the text value is on a different
table. The me.filter is then a text instead of the id-number from the lookup table. This causes the report to prompt for the parameter.

How do I get by this problem? Do I need to create a temporary table? I
rather not. or is there another solution without using filter?

Nov 12 '05 #2

P: n/a
Yes, that is true. But what I want to do is to use the filter the user sets
in the form, to the report.
For instance. If the user only wants to list clients from the State CA he
filters the form and the property me.filter sets to ((tblClients.State =
"CA")). That filter is then used to the report. Everything is working fine
until the user filters that combo. The values are taken from a lookup table
and the me.filter is set to: ((lookup_cboRemarks.remark = "Valid member")).
The lookup table is called tlkpRemarks and when I try to run the report it
asks for the parameter value of lookup_cboRemarks.remark.

The combo's bound column is zero width like you predicted and the second
column is all the remarks. I don't really want to alter the filter but if I
need to it gets messy because the filter string can get long and
complicated.

I am using Access 2002.
Appreciate any further help
Richard Wernerhjelm
"Allen Browne" <al*********@SeeSig.invalid> skrev i meddelandet
news:3f**********************@freenews.iinet.net.a u...
Sounds like the combo's bound column is zero width?

You should still be able to use the value from the invisible column in the
WhereCondition for your OpenReport. Just use the Value of the combo.

It is also possible to use its Column() property if necessary. (That's
zero-based.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard" <rw**********@chello.se> wrote in message
news:dMQpb.883$%W3.1336@amstwist00...

I have a form based on a table. When I filter the form I want to run a
report based on the same table with the same filter as the form. No

problem
until I want to filter a combo box where the text value is on a different table. The me.filter is then a text instead of the id-number from the

lookup
table. This causes the report to prompt for the parameter.

How do I get by this problem? Do I need to create a temporary table? I
rather not. or is there another solution without using filter?


Nov 12 '05 #3

P: n/a
So your request is that the report can interpret a filter containing:
lookup_cboRemarks.remark = "some text"

I'm not sure how the filter is being set: filter by form perhaps? Would it
be practical to include the combo's lookup table in the RecordSource of the
form (using an outer join query), so the filter could become:
SomeTable.remark = "some text"
The report would then be able to filter that correctly (assuming it is also
based on a query that contains the lookup table).

If that can't be done, it may be easier to change the way the filter string
is being generated so that it contains the value from the bound column. The
report would interpret that correctly. Typically I include unbound boxes in
the form header for the fields that user is likely to need to filter on, and
build the filter string programmatically. This has the advantage that it
works with mde files and runtimes where the filter-by-form is not available.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard" <rw**********@chello.se> wrote in message
news:ec3qb.988$%W3.1576@amstwist00...
Yes, that is true. But what I want to do is to use the filter the user sets in the form, to the report.
For instance. If the user only wants to list clients from the State CA he
filters the form and the property me.filter sets to ((tblClients.State =
"CA")). That filter is then used to the report. Everything is working fine
until the user filters that combo. The values are taken from a lookup table and the me.filter is set to: ((lookup_cboRemarks.remark = "Valid member")). The lookup table is called tlkpRemarks and when I try to run the report it
asks for the parameter value of lookup_cboRemarks.remark.

The combo's bound column is zero width like you predicted and the second
column is all the remarks. I don't really want to alter the filter but if I need to it gets messy because the filter string can get long and
complicated.

I am using Access 2002.
Appreciate any further help
Richard Wernerhjelm
"Allen Browne" <al*********@SeeSig.invalid> skrev i meddelandet
news:3f**********************@freenews.iinet.net.a u...
Sounds like the combo's bound column is zero width?

You should still be able to use the value from the invisible column in the
WhereCondition for your OpenReport. Just use the Value of the combo.

It is also possible to use its Column() property if necessary. (That's
zero-based.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard" <rw**********@chello.se> wrote in message
news:dMQpb.883$%W3.1336@amstwist00...

I have a form based on a table. When I filter the form I want to run a
report based on the same table with the same filter as the form. No

problem
until I want to filter a combo box where the text value is on a

different table. The me.filter is then a text instead of the id-number from the

lookup
table. This causes the report to prompt for the parameter.

How do I get by this problem? Do I need to create a temporary table? I
rather not. or is there another solution without using filter?

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.