Form Filtering Help

[Golfinray]

I have a report that is fine and set up the way I want it. Now I need to
create a form to filter or select records in my report. I am struggling to
write that code. What I would like to do is have a list (is it better to use
a listbox or combo?) on my report that users can scroll down and select their
school district. Then click on the chosen district and the report opens and
goes to that district. There are about 250 districts to choose from. The
districts are in a separate query with just districts listed. So, it would be
like having a search function on a report. The primary key on all of the
tables used by the report is district. The form name is Project Report.

 

[Allen Browne]

So you need a form with a combo or list box where the user selects a
district, and a command button to click to open the report to that district?

The command button's Click event procedure will use OpenReport, with a
WhereCondition to filter the report to the chosen district.

Here's a basic example:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

If you want to choose multiple districts, that's possible:
http://allenbrowne.com/ser-50.html

And if you need to offer them lots of choices to use in combination, you can
do that too:
http://allenbrowne.com/ser-62.html

 

[Golfinray]

I have done the following:
1. I built the a form with combo called FindDistrict. The rowsource is my
district query, and it works fine. It opens the query which asks you what
district you want.
In the query I have a criteria that says forms!myform!finddistrict That
appears to be working. I have a command button on the form that says in
onclick me.visible=false
2. In my reports open event I have docmd.openform, "myform" and in the on
close event I have docmd.close acform, "myform"
3 It does not work. What am I doing wrong? Thanks so much!!!!

 

[fredg]

I have done the following:
1. I built the a form with combo called FindDistrict. The rowsource is my
district query, and it works fine. It opens the query which asks you what
district you want.
In the query I have a criteria that says forms!myform!finddistrict That
appears to be working. I have a command button on the form that says in
onclick me.visible=false
2. In my reports open event I have docmd.openform, "myform" and in the on
close event I have docmd.close acform, "myform"
3 It does not work. What am I doing wrong? Thanks so much!!!!

It would have been better if you had kept this message in the same
thread as your original post yesterday. That way others will have an
opportunity to see what it is you are trying to do and comment upon
it.

1) If you re-read the reply I sent you yesterday you will see that the
way to open the form to select the district was to open it in
acDialog.

DoCmd.OpenForm "ParamForm", , , , , acDialog

I don't see where your code
docmd.openform, "myform"
opens the form in dialog. Those commas and acDialog are important.

2) The form does not (nor should it) open and run the query, so I have
no idea what else you are doing when you write
"It opens the query which asks you what district you want."

3) Words like "it does not work" gives us no idea what is wrong.
You have to tell us, more specifically 'what does not work'.
The form doesn't open? The report is not filtered (well if you haven't
opened the form in dialog the report will not wait for you to enter
District name)? The computer crashes? What?

 

[Golfinray]

Sorry, but I tried to do everything you told me to do to the letter,
including making a query and putting form!paramform!finddistrict in the query
criteria field. I followed all the other instructions and what I got was when
I push the command button it said there is no macro named Me. I can't get any
farther than that. I am just struggling with this but I need a form to open
my report to the correct district. Thanks so much for the help!!!

 

[fredg]

Sorry, but I tried to do everything you told me to do to the letter,
including making a query and putting form!paramform!finddistrict in the query
criteria field. I followed all the other instructions and what I got was when
I push the command button it said there is no macro named Me. I can't get any
farther than that. I am just struggling with this but I need a form to open
my report to the correct district. Thanks so much for the help!!!

*** snipped ***

Aha!
Now you have given a bit more information which, I believe, has solved
the problem.

when I push the command button it said there is no macro named Me. <

That error message usually occurs when the code is written directly on
the event line, rather than in the event code window.

Here is how to write code.

In Report Design View, on the Report's Open event line, write:

[Event Procedure]

Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.OpenForm "ParamForm", , , , , acDialog

Close the window.

Next, do the same thing on the Report's Close event line:

[Event Procedure]

Click on the dots, write

DoCmd.Close acForm, "ParamForm"

Close the window. Save the report.

Open the ParamForm in Design View.
Follow the above procedure to code the form's command button click
event:

Me.Visible = False

Save the changes.

Once again, when you open the report, it will open the form. The
report will wait for you to select the District. when you click on the
form's command button, the form will hide and the report will run,
displaying just the records for the district selected on the form.
When you close the report, it will close the form.
You never 'see' the query itself.

Let us know if this resolves the problem.

 

[Duane Hookom]

If you get an error like "macro" then I expect you have entered something
into the event "property" rather than opening the code window and entering
some line or lines of code.

 

[Golfinray]

Thanks Fred. Now I get the message "the command or action apply filtersort
isn't available now. Please help. Thanks!!!!

Sorry, but I tried to do everything you told me to do to the letter,
including making a query and putting form!paramform!finddistrict in the query
criteria field. I followed all the other instructions and what I got was when
I push the command button it said there is no macro named Me. I can't get any
farther than that. I am just struggling with this but I need a form to open
my report to the correct district. Thanks so much for the help!!!

*** snipped ***

Aha!
Now you have given a bit more information which, I believe, has solved
the problem.

when I push the command button it said there is no macro named Me. <

That error message usually occurs when the code is written directly on
the event line, rather than in the event code window.

Here is how to write code.

In Report Design View, on the Report's Open event line, write:

[Event Procedure]

Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.OpenForm "ParamForm", , , , , acDialog

Close the window.

Next, do the same thing on the Report's Close event line:

[Event Procedure]

Click on the dots, write

DoCmd.Close acForm, "ParamForm"

Close the window. Save the report.

Open the ParamForm in Design View.
Follow the above procedure to code the form's command button click
event:

Me.Visible = False

Save the changes.

Once again, when you open the report, it will open the form. The
report will wait for you to select the District. when you click on the
form's command button, the form will hide and the report will run,
displaying just the records for the district selected on the form.
When you close the report, it will close the form.
You never 'see' the query itself.

Let us know if this resolves the problem.

 

[fredg]

Thanks Fred. Now I get the message "the command or action apply filtersort
isn't available now. Please help. Thanks!!!!

:

*** snipped ***

Sorry, I haven't a clue as to what you are doing wrong.