Me.filter failing

[dick]

I am just trying to print/report the results of a "filter by
selection" which is done by right-clicking a form, filling in values,
and "applying the filter."
I have searched the newsgroups, and there are many examples. BUT, they
fail sometimes.

The techique is to pass the form's Me.filter as the "where condition"
in a
Docmd.openreport statement in code behind a "print button" on the
form.

It WORKS when the fields being filtered (used as selectors) exist in
the underlying DB table "naturally."

It FAILS when the fields being filtered are in the underlying DB table
as an integer index value, and the form has a combo-box which uses a
2nd table to populate the combo-box (a very standard setup).

In successful case, debug in immediate window shows Me.filter to be
((tblCandidates.FirstName Like "pr*"))
Clearly a reference to a column name in the underlying table.

In failure case, debug in immediate window shows Me.filter to be
((Lookup_cboPos1.Positions="Accountant Sr"))
Obiviously the report does not know what to do with this char string.

Access created these "filter strings", not me. What to do?

[Larry Linson]

Access doesn't generally "just create arbitrary filter strings on its own"
as might be implied by your statement about it being "Access' filter
string". Could you clarify just why there is a filter string at all... must
be something either you or the user has done.

You might first try looking at the code in the AfterUpdate event of Combo
Box cboPOS1. You might also consider whether this is a Combo referencing a
Lookup Field in a Table (we have far more queries about problems with lookup
fields than I can imagine people benefitting from them, and I strongly
discourage using them, because they violate good relational DB design
practices and obscure what is actually stored).

Larry Linson
Microsoft Access MVP

"dick" <d_*****@usa.net> wrote in message
news:42**************************@posting.google.c om...

I am just trying to print/report the results of a "filter by
selection" which is done by right-clicking a form, filling in values,
and "applying the filter."
I have searched the newsgroups, and there are many examples. BUT, they
fail sometimes.

The techique is to pass the form's Me.filter as the "where condition"
in a
Docmd.openreport statement in code behind a "print button" on the
form.

It WORKS when the fields being filtered (used as selectors) exist in
the underlying DB table "naturally."

It FAILS when the fields being filtered are in the underlying DB table
as an integer index value, and the form has a combo-box which uses a
2nd table to populate the combo-box (a very standard setup).

In successful case, debug in immediate window shows Me.filter to be
((tblCandidates.FirstName Like "pr*"))
Clearly a reference to a column name in the underlying table.

In failure case, debug in immediate window shows Me.filter to be
((Lookup_cboPos1.Positions="Accountant Sr"))
Obiviously the report does not know what to do with this char string.

Access created these "filter strings", not me. What to do?

[Allen Browne]

Sometimes Access tries to be too smart.

You may be able to solve the problem by using a query as the source for your
report. Include the lookup table in the query. Use an outer join if some
records don't have a value in the lookup field. (Double-click the line
joining the 2 tables in query design to change it to an outer join.)

--
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.

"dick" <d_*****@usa.net> wrote in message
news:42**************************@posting.google.c om...

I am just trying to print/report the results of a "filter by
selection" which is done by right-clicking a form, filling in values,
and "applying the filter."
I have searched the newsgroups, and there are many examples. BUT, they
fail sometimes.

The techique is to pass the form's Me.filter as the "where condition"
in a
Docmd.openreport statement in code behind a "print button" on the
form.

It WORKS when the fields being filtered (used as selectors) exist in
the underlying DB table "naturally."

It FAILS when the fields being filtered are in the underlying DB table
as an integer index value, and the form has a combo-box which uses a
2nd table to populate the combo-box (a very standard setup).

In successful case, debug in immediate window shows Me.filter to be
((tblCandidates.FirstName Like "pr*"))
Clearly a reference to a column name in the underlying table.

In failure case, debug in immediate window shows Me.filter to be
((Lookup_cboPos1.Positions="Accountant Sr"))
Obiviously the report does not know what to do with this char string.

Access created these "filter strings", not me. What to do?

[Dick Penny]

Allen,
You are *exactly* right. Today I found this *exact* Q answered by you in a
Google newsgroup with a guy in Sweden. But I still do not know what to do
because:
1) there are approx 15 controls, combo-boxes, on the form (maybe 40 controls
in all) of which the user can choose ANY combination to perform a filter.
Does this imply/mean that the data source for the form has to go from 1
table to, say, 16 tables?
2) and do I have to change the sources for the 15 combo-boxes, each to 2
table sources with a join?

Given that a recordset has been produced by the filter by form action, isn't
there some way I can pass this dynaset to the report? Why force the whole DB
apparatus to do the query a 2nd time? Can I create a temporary table from
the recordcource, and pass the temp tbl to the report?
Dick

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

Sometimes Access tries to be too smart.

You may be able to solve the problem by using a query as the source for your report. Include the lookup table in the query. Use an outer join if some
records don't have a value in the lookup field. (Double-click the line
joining the 2 tables in query design to change it to an outer join.)

--
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.

"dick" <d_*****@usa.net> wrote in message
news:42**************************@posting.google.c om...

I am just trying to print/report the results of a "filter by
selection" which is done by right-clicking a form, filling in values,
and "applying the filter."
I have searched the newsgroups, and there are many examples. BUT, they
fail sometimes.

The techique is to pass the form's Me.filter as the "where condition"
in a
Docmd.openreport statement in code behind a "print button" on the
form.

It WORKS when the fields being filtered (used as selectors) exist in
the underlying DB table "naturally."

It FAILS when the fields being filtered are in the underlying DB table
as an integer index value, and the form has a combo-box which uses a
2nd table to populate the combo-box (a very standard setup).

In successful case, debug in immediate window shows Me.filter to be
((tblCandidates.FirstName Like "pr*"))
Clearly a reference to a column name in the underlying table.

In failure case, debug in immediate window shows Me.filter to be
((Lookup_cboPos1.Positions="Accountant Sr"))
Obiviously the report does not know what to do with this char string.

Access created these "filter strings", not me. What to do?

[Dick Penny]

Larry,
Sorry, but this whole game is vocabulary. I appologize for not speaking
yours.
BUT, yes, Access did create a filter string on its own in response to the
user doing a "filter by form" as I
stated in my 1st posting. The combo-box in question is populated by a Select
statement from (what I call) a secondary table given an index (I guess
Access calls it an ID) in the main underlying table. There is no "lookup"
anywhere by a user or in VBA code. IMHO, Access translated the Select
statement into the filter lookup string that I posted. This seems to puzzle
a lot of persons.

Given that there are approx. 15 combo-boxes (populated by 2nd tables) and
30-40 other controls on the form that the user can use to filter (I prefer
select) data, I don't know what to do. My throughts drift toward use the
recordset produced by the "filter by form" action to drive the report rather
than the query string represent by Me.filter. After all, we did the query
once, why do it twice? BUT, I have had no luck in passing this recordset to
the report. It seems, by definiton, that a report's record source is a
string (not a handle sort of thingy).

Any thoughts appreciated.

Happy Holidays.

Dick Penny

"Larry Linson" <bo*****@localhost.not> wrote in message
news:N7*****************@nwrddc03.gnilink.net...

Access doesn't generally "just create arbitrary filter strings on its own"
as might be implied by your statement about it being "Access' filter
string". Could you clarify just why there is a filter string at all... must be something either you or the user has done.

You might first try looking at the code in the AfterUpdate event of Combo
Box cboPOS1. You might also consider whether this is a Combo referencing a
Lookup Field in a Table (we have far more queries about problems with lookup fields than I can imagine people benefitting from them, and I strongly
discourage using them, because they violate good relational DB design
practices and obscure what is actually stored).

Larry Linson
Microsoft Access MVP

"dick" <d_*****@usa.net> wrote in message
news:42**************************@posting.google.c om...

I am just trying to print/report the results of a "filter by
selection" which is done by right-clicking a form, filling in values,
and "applying the filter."
I have searched the newsgroups, and there are many examples. BUT, they
fail sometimes.

The techique is to pass the form's Me.filter as the "where condition"
in a
Docmd.openreport statement in code behind a "print button" on the
form.

It WORKS when the fields being filtered (used as selectors) exist in
the underlying DB table "naturally."

It FAILS when the fields being filtered are in the underlying DB table
as an integer index value, and the form has a combo-box which uses a
2nd table to populate the combo-box (a very standard setup).

In successful case, debug in immediate window shows Me.filter to be
((tblCandidates.FirstName Like "pr*"))
Clearly a reference to a column name in the underlying table.

In failure case, debug in immediate window shows Me.filter to be
((Lookup_cboPos1.Positions="Accountant Sr"))
Obiviously the report does not know what to do with this char string.

Access created these "filter strings", not me. What to do?

[Allen Browne]

In recent versions of Access, you may be able to get a reference to the
Recordset of the form, and assign it to the Recordset of the report in
Report_Open. Haven't really tried, as I found this kind of approach a bit
unstable for real world applications.

Dick, you sound like you are fairly serious and aware. Do you realise that
Filter-by-Form will not work if you release an MDE/runtime? As a result, I
never use it; instead I provide controls in the Form Header section for the
kinds of filtering the user is likely to need. It's a little more work to
develop, but much more flexible and clients find it very convenient and easy
to use.

As for your specific questions, the RecordSource of the form should not have
to change. The report's query would need to contain all the tables for the
lookup fields, so that it can make sense of "tblCandidates.FirstName" and
resolve it.

--
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.

"Dick Penny" <pe*******@comcast.net> wrote in message
news:0o********************@comcast.com...

Allen,
You are *exactly* right. Today I found this *exact* Q answered by you in a
Google newsgroup with a guy in Sweden. But I still do not know what to do
because:
1) there are approx 15 controls, combo-boxes, on the form (maybe 40 controls in all) of which the user can choose ANY combination to perform a filter.
Does this imply/mean that the data source for the form has to go from 1
table to, say, 16 tables?
2) and do I have to change the sources for the 15 combo-boxes, each to 2
table sources with a join?

Given that a recordset has been produced by the filter by form action, isn't there some way I can pass this dynaset to the report? Why force the whole DB apparatus to do the query a 2nd time? Can I create a temporary table from
the recordcource, and pass the temp tbl to the report?
Dick

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

Sometimes Access tries to be too smart.

You may be able to solve the problem by using a query as the source for

your

report. Include the lookup table in the query. Use an outer join if some
records don't have a value in the lookup field. (Double-click the line
joining the 2 tables in query design to change it to an outer join.)

--
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.

"dick" <d_*****@usa.net> wrote in message
news:42**************************@posting.google.c om...

I am just trying to print/report the results of a "filter by
selection" which is done by right-clicking a form, filling in values,
and "applying the filter."
I have searched the newsgroups, and there are many examples. BUT, they
fail sometimes.

The techique is to pass the form's Me.filter as the "where condition"
in a
Docmd.openreport statement in code behind a "print button" on the
form.

It WORKS when the fields being filtered (used as selectors) exist in
the underlying DB table "naturally."

It FAILS when the fields being filtered are in the underlying DB table
as an integer index value, and the form has a combo-box which uses a
2nd table to populate the combo-box (a very standard setup).

In successful case, debug in immediate window shows Me.filter to be
((tblCandidates.FirstName Like "pr*"))
Clearly a reference to a column name in the underlying table.

In failure case, debug in immediate window shows Me.filter to be
((Lookup_cboPos1.Positions="Accountant Sr"))
Obiviously the report does not know what to do with this char string.

Access created these "filter strings", not me. What to do?

[Dick Penny]

Thank you Allen. I guess now I know most of the possible options.

I am not a guru, just a finance guy that has had to do a LOT of data
manipulation & conversion over the years. This particular DB is for internal
use of a 5 person placement and consulting agency so I am not at all worried
about MDE/runtime. I only got involved because the office "stafft" was
complaining that they could not print the results of a search (filter by
form). Then I found that ~50% of the DB objects were corrupt (obiviously not
being used), and it was impossible to make any changes without cleaning it
up. After 6 hours on phone with a MS guru in India, I've got a clean DB. Now
I am trying to put in a "few tiny changes."

Happy Holidays (it is New Year's eve here)

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

In recent versions of Access, you may be able to get a reference to the
Recordset of the form, and assign it to the Recordset of the report in
Report_Open. Haven't really tried, as I found this kind of approach a bit
unstable for real world applications.

Dick, you sound like you are fairly serious and aware. Do you realise that
Filter-by-Form will not work if you release an MDE/runtime? As a result, I
never use it; instead I provide controls in the Form Header section for the kinds of filtering the user is likely to need. It's a little more work to
develop, but much more flexible and clients find it very convenient and easy to use.

As for your specific questions, the RecordSource of the form should not have to change. The report's query would need to contain all the tables for the
lookup fields, so that it can make sense of "tblCandidates.FirstName" and
resolve it.

--
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.

"Dick Penny" <pe*******@comcast.net> wrote in message
news:0o********************@comcast.com...

Allen,
You are *exactly* right. Today I found this *exact* Q answered by you in a Google newsgroup with a guy in Sweden. But I still do not know what to do because:
1) there are approx 15 controls, combo-boxes, on the form (maybe 40 controls

in all) of which the user can choose ANY combination to perform a filter. Does this imply/mean that the data source for the form has to go from 1
table to, say, 16 tables?
2) and do I have to change the sources for the 15 combo-boxes, each to 2 table sources with a join?

Given that a recordset has been produced by the filter by form action,

isn't

there some way I can pass this dynaset to the report? Why force the whole DB

apparatus to do the query a 2nd time? Can I create a temporary table

from the recordcource, and pass the temp tbl to the report?
Dick

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

Sometimes Access tries to be too smart.

You may be able to solve the problem by using a query as the source for

your

report. Include the lookup table in the query. Use an outer join if

some records don't have a value in the lookup field. (Double-click the line
joining the 2 tables in query design to change it to an outer join.)

--
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.

"dick" <d_*****@usa.net> wrote in message
news:42**************************@posting.google.c om...
> I am just trying to print/report the results of a "filter by
> selection" which is done by right-clicking a form, filling in values, > and "applying the filter."
> I have searched the newsgroups, and there are many examples. BUT, they > fail sometimes.
>
> The techique is to pass the form's Me.filter as the "where condition" > in a
> Docmd.openreport statement in code behind a "print button" on the
> form.
>
> It WORKS when the fields being filtered (used as selectors) exist in
> the underlying DB table "naturally."
>
> It FAILS when the fields being filtered are in the underlying DB table > as an integer index value, and the form has a combo-box which uses a
> 2nd table to populate the combo-box (a very standard setup).
>
> In successful case, debug in immediate window shows Me.filter to be
> ((tblCandidates.FirstName Like "pr*"))
> Clearly a reference to a column name in the underlying table.
>
> In failure case, debug in immediate window shows Me.filter to be
> ((Lookup_cboPos1.Positions="Accountant Sr"))
> Obiviously the report does not know what to do with this char string. >
> Access created these "filter strings", not me. What to do?

[David W. Fenton]

d_*****@usa.net (dick) wrote in
<42**************************@posting.google.com >:

The techique is to pass the form's Me.filter as the "where
condition" in a
Docmd.openreport statement in code behind a "print button" on the
form.

It WORKS when the fields being filtered (used as selectors) exist
in the underlying DB table "naturally."

It FAILS when the fields being filtered are in the underlying DB
table as an integer index value, and the form has a combo-box
which uses a 2nd table to populate the combo-box (a very standard
setup).

I've done this kind of thing in cases where I had a list form that
users could filter and wanted a quick printout representing the
same data. What I do is take the filter string and replace
references to controls/fields in the form that do not exist with
the appropriate fieldnames in the report. While this could be
complicated with a form with a large number of controls, it would
still constitute a finite set of replace operations. I've never
done this kind of thing with anything but straightforward list
reports, though, so it's always been a simple operation.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

pe*******@comcast.net (Dick Penny) wrote in
<Ut********************@comcast.com>:

I am not a guru, just a finance guy that has had to do a LOT of
data manipulation & conversion over the years. This particular DB
is for internal use of a 5 person placement and consulting agency
so I am not at all worried about MDE/runtime. I only got involved
because the office "stafft" was complaining that they could not
print the results of a search (filter by form). Then I found that
~50% of the DB objects were corrupt (obiviously not being used),
and it was impossible to make any changes without cleaning it up.
After 6 hours on phone with a MS guru in India, I've got a clean
DB. Now I am trying to put in a "few tiny changes."

I would suggest you follow Allen's advice and mine, given in a
reply posted a few minutes ago:

1. in your report, start with the same recordsource as the form.
Add the lookup tables represented by the combo boxes with outer
joins.

2. before sending the form's filter to the report, replace all
references to the combo boxes with the relevant fields in the
target lookup tables as found in your report's recordsource.

The best way to do this is to try one combo box at a time, get it
working, and then move on to the next one.

You don't say what version of Access you're using. If it's A2K or
later, you can use Replace() to do the substitutions. If you're
using A97, then you'll need to find a substitute function -- every
Access developer of longstanding has one of their own.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc