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" <AllenBrowne@SeeSig.Invalid> wrote in message
news:3ff2692b$0$1753$5a62ac22@freenews.iinet.net.a u...[color=blue]
> 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[/color]
the[color=blue]
> 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[/color]
easy[color=blue]
> to use.
>
> As for your specific questions, the RecordSource of the form should not[/color]
have[color=blue]
> 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" <penny1482@comcast.net> wrote in message
> news:0oydnfM7jc1xwW-iRVn-sw@comcast.com...[color=green]
> > Allen,
> > You are *exactly* right. Today I found this *exact* Q answered by you in[/color][/color]
a[color=blue][color=green]
> > Google newsgroup with a guy in Sweden. But I still do not know what to[/color][/color]
do[color=blue][color=green]
> > because:
> > 1) there are approx 15 controls, combo-boxes, on the form (maybe 40[/color]
> controls[color=green]
> > in all) of which the user can choose ANY combination to perform a[/color][/color]
filter.[color=blue][color=green]
> > 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[/color][/color]
2[color=blue][color=green]
> > table sources with a join?
> >
> > Given that a recordset has been produced by the filter by form action,[/color]
> isn't[color=green]
> > there some way I can pass this dynaset to the report? Why force the[/color][/color]
whole[color=blue]
> DB[color=green]
> > apparatus to do the query a 2nd time? Can I create a temporary table[/color][/color]
from[color=blue][color=green]
> > the recordcource, and pass the temp tbl to the report?
> > Dick
> >
> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> > news:3ff22a50$0$1757$5a62ac22@freenews.iinet.net.a u...[color=darkred]
> > > Sometimes Access tries to be too smart.
> > >
> > > You may be able to solve the problem by using a query as the source[/color][/color][/color]
for[color=blue][color=green]
> > your[color=darkred]
> > > report. Include the lookup table in the query. Use an outer join if[/color][/color][/color]
some[color=blue][color=green][color=darkred]
> > > 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_penny@usa.net> wrote in message
> > > news:42c7627b.0312301221.39661f6e@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[/color][/color][/color]
values,[color=blue][color=green][color=darkred]
> > > > and "applying the filter."
> > > > I have searched the newsgroups, and there are many examples. BUT,[/color][/color][/color]
they[color=blue][color=green][color=darkred]
> > > > fail sometimes.
> > > >
> > > > The techique is to pass the form's Me.filter as the "where[/color][/color][/color]
condition"[color=blue][color=green][color=darkred]
> > > > 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[/color][/color][/color]
table[color=blue][color=green][color=darkred]
> > > > 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[/color][/color][/color]
string.[color=blue][color=green][color=darkred]
> > > >
> > > > Access created these "filter strings", not me. What to do?[/color][/color]
>
>[/color]
