report filtered form fails

  • Thread starter Thread starter dick
  • Start date Start date
D

dick

Am trying to report the records filtered by form. I've
copied lots of posts on this, and it works when the
filtered items exists naturally in the underlying table.
But when the form has combo-box populated by a 2nd table,
and main table contains its index, the ME.filter contains
a "lookup_cboPos3.Positions="accountant"" that cannot be
processed by the report as it filter criteria. What to do?
The filter can be quite complex from the form. Can I
somehow just grab the dynaset the form produced?
 
If you can figure out how to filter your form's data you should be able to
do the same for your report's data. I have a form that has 2 option groups,
several comboboxes and some text boxes that I use to filter the rowsource
for a list box. I have a button on that form that opens a report which
displays the same records that appear in the listbox. I accomplish that by
creating a where clause when the user clicks a command button. That command
button opens my report and uses the where clause as the criteria argument
for the report. It may not be easy and it probably won't be pretty but it's
possible. I think the procedure I use to create the where clause is about 90
lines of code.

So to summarize, use the same recordsource for your report that you use for
your form and then use code behind a button on your form (you would probably
want to create a separate procedure that you can just call from your
button's click event) to create a where clause that will be used to filter
the records in your report. Apply that filter in the openreport method as
follows:

Private Sub cmdOpenReport_Click()
dim strWhere as String

strWhere = "Field1 = " & ctlSomeControl 'Over simplification
DoCmd.OpenReport "rptSomeReport", acPreview, , strWhere

End Sub

Obviously, the trick is in the Where clause. I'm not sure what you mean by
"lookup_cboPos3.Positions="accountant"" but if that can be processed by your
form then it can be processed by code in your form's module and that means
you can create a where clause from it.

With that said, I do agree, however, that it would be a lot easier to just
use the form's current filtered recordset as the recordsource for the report
but I haven't figured out how to do that. Maybe someone else here can show
us.
 
I really have not figured out how to "filter form's
data." Access does it all. We just right-clk on the form,
fill in the various data fields whose records we want to
find, then right-clk "apply filter." My goal is now, to
print/report that sub-set of the entire DB.

The "lookup_cboPos3.Positions="accountant"" is the
contents of the Me.filter property of the form in the case
when it does not work. I guess it is the "where clause"
(maybe??). But feeding this to the report does not work
because (I guess) a combo-box is translating the
string "acountant" into integer 13, and 13 is what is
really in the table. In that spirit, the follow filter
does work as a Me.filter string.
tblMain.Position3=13

To repeat. There is about 50-60 fields on this form that
the user could use to filter data. Several other posts
have said simply use the Me.filter string build by Access
and pass this to the report. It works fine on plain data
fields. But on data translated through a combo-box I get
those lookup statements.

Uggg, the english behind this is so hard, the concepts so
simple.
 
You might want to experiment with creating a query to use as a datasource
for your report. In the criteria under each field in your query you can
refernce the name of the control on your filter form. As long as the form is
open when the report gets opened, the query should be able to resolve the
references to the form's controls.

Use the following syntax in the criteria cell in the query design screen:

[Forms]![NameOfForm]![NameOfControl]

I hope this helps.
 
Understood. Thanks. I will try it on Jan 2nd.

Happy New Year.

Dick
Tom Stoddard said:
You might want to experiment with creating a query to use as a datasource
for your report. In the criteria under each field in your query you can
refernce the name of the control on your filter form. As long as the form is
open when the report gets opened, the query should be able to resolve the
references to the form's controls.

Use the following syntax in the criteria cell in the query design screen:

[Forms]![NameOfForm]![NameOfControl]

I hope this helps.

dick said:
I really have not figured out how to "filter form's
data." Access does it all. We just right-clk on the form,
fill in the various data fields whose records we want to
find, then right-clk "apply filter." My goal is now, to
print/report that sub-set of the entire DB.

The "lookup_cboPos3.Positions="accountant"" is the
contents of the Me.filter property of the form in the case
when it does not work. I guess it is the "where clause"
(maybe??). But feeding this to the report does not work
because (I guess) a combo-box is translating the
string "acountant" into integer 13, and 13 is what is
really in the table. In that spirit, the follow filter
does work as a Me.filter string.
tblMain.Position3=13

To repeat. There is about 50-60 fields on this form that
the user could use to filter data. Several other posts
have said simply use the Me.filter string build by Access
and pass this to the report. It works fine on plain data
fields. But on data translated through a combo-box I get
those lookup statements.

Uggg, the english behind this is so hard, the concepts so
simple.
Click to expand...
Click to expand...
 
Tom,
What with Holidays, etc., I just got around to trying your suggestion,
copied on next 2 lines.
Use the following syntax in the criteria cell in the query design screen:

[Forms]![NameOfForm]![NameOfControl]
Click to expand...
Click to expand...
This returns to the query the current record viewed on the form, NOT the
entire filtered recordset.
Do you know how I can "feed" a recordset at a query? or a table? (say a new
temp one)?

Dick
Dick Penny said:
Understood. Thanks. I will try it on Jan 2nd.

Happy New Year.

Dick
Tom Stoddard said:
You might want to experiment with creating a query to use as a datasource
for your report. In the criteria under each field in your query you can
refernce the name of the control on your filter form. As long as the
Click to expand...
form
is
open when the report gets opened, the query should be able to resolve the
references to the form's controls.

Use the following syntax in the criteria cell in the query design screen:

[Forms]![NameOfForm]![NameOfControl]

I hope this helps.

dick said:
I really have not figured out how to "filter form's
data." Access does it all. We just right-clk on the form,
fill in the various data fields whose records we want to
find, then right-clk "apply filter." My goal is now, to
print/report that sub-set of the entire DB.

The "lookup_cboPos3.Positions="accountant"" is the
contents of the Me.filter property of the form in the case
when it does not work. I guess it is the "where clause"
(maybe??). But feeding this to the report does not work
because (I guess) a combo-box is translating the
string "acountant" into integer 13, and 13 is what is
really in the table. In that spirit, the follow filter
does work as a Me.filter string.
tblMain.Position3=13

To repeat. There is about 50-60 fields on this form that
the user could use to filter data. Several other posts
have said simply use the Me.filter string build by Access
and pass this to the report. It works fine on plain data
fields. But on data translated through a combo-box I get
those lookup statements.

Uggg, the english behind this is so hard, the concepts so
simple.
-----Original Message-----
If you can figure out how to filter your form's data you
should be able to
do the same for your report's data. I have a form that
has 2 option groups,
several comboboxes and some text boxes that I use to
filter the rowsource
for a list box. I have a button on that form that opens a
report which
displays the same records that appear in the listbox. I
accomplish that by
creating a where clause when the user clicks a command
button. That command
button opens my report and uses the where clause as the
criteria argument
for the report. It may not be easy and it probably won't
be pretty but it's
possible. I think the procedure I use to create the where
clause is about 90
lines of code.

So to summarize, use the same recordsource for your
report that you use for
your form and then use code behind a button on your form
(you would probably
want to create a separate procedure that you can just
call from your
button's click event) to create a where clause that will
be used to filter
the records in your report. Apply that filter in the
openreport method as
follows:

Private Sub cmdOpenReport_Click()
dim strWhere as String

strWhere = "Field1 = " & ctlSomeControl 'Over
simplification
DoCmd.OpenReport "rptSomeReport", acPreview, ,
strWhere

End Sub

Obviously, the trick is in the Where clause. I'm not sure
what you mean by
"lookup_cboPos3.Positions="accountant"" but if that can
be processed by your
form then it can be processed by code in your form's
module and that means
you can create a where clause from it.

With that said, I do agree, however, that it would be a
lot easier to just
use the form's current filtered recordset as the
recordsource for the report
but I haven't figured out how to do that. Maybe someone
else here can show
us.

message
Am trying to report the records filtered by form. I've
copied lots of posts on this, and it works when the
filtered items exists naturally in the underlying table.
But when the form has combo-box populated by a 2nd
table,
and main table contains its index, the ME.filter
contains
a "lookup_cboPos3.Positions="accountant"" that cannot be
processed by the report as it filter criteria. What to
do?
The filter can be quite complex from the form. Can I
somehow just grab the dynaset the form produced?


.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top