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?
8  6501 
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?
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?
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?
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?
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?
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?
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 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Robin Cull |
last post by:
Imagine I have a dict looking something like this:
myDict = {"key 1": , "key
2": , "key 3": ,
"key 4": }
That is, a set of keys which have a variable length list of associated
values after...
|
by: Robert Neville |
last post by:
I would like to add filter functionality to my database whether
through the Main form or the subform. This question may be
rudimentary, yet I have not less experience with filtering data
outside...
|
by: CSDunn |
last post by:
Hello,
I have a problem with field filtering between an Access 2000 Project form
(the application is called CELDT), and the report that shows the results of
the filter. Both the form and the...
|
by: Richard |
last post by:
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...
|
by: Salad |
last post by:
I have a log file with a list of records. The log file can be
unfiltered or filtered.
I have a command button to call a data entry form from the log. At
first I was only going to present the...
|
by: Patrick Gonzalez |
last post by:
Is it possible to use the built-in filter functions (ie. "Filter by
Selection", etc..) when a form's recordsource has been set to a
recordset object during the load event. When I try to do this, I...
|
by: Mike Sweetman |
last post by:
I have a form Form1 which when the Advanced Filter/Sort is used
creates a form(maybe) with a title 'Form1Filter1 : Filter'. When I
apply the filter to Form1 it is applied, but the value of...
|
by: marcus.kwok |
last post by:
I am having a weird problem and I have can't figure out why it is
happening. I create an OpenFileDialog and set a filename filter. When
the dialog first opens, the filter works correctly, and...
|
by: Briansmi1116 |
last post by:
I created two buttons, that filter my form, They filter in different Fields, and if one is filtered, and the other is not there is a certain amount of record, if they are both pushed, then there is...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
