Filter report on a form

Does your record source for the report reference the values on the controls on
the form to filter the records?

Post the SQL view of the record source.

Or are you trying to build a filter based on the form's control values?


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
John, I tought I was done, but I am not! . I followed your steps here and
they worked very nice, the problem is that now the reports opens up showing
the same information no matter what is selected from the combo box. What am I
missing?

"John Spencer" wrote:

It can be done, but I would advise against it.

In the REPORTS open event, you would need to open the form in dialog mode
(that halts all processing except in the opened form), set the values, and use
the button to set the form's visible property to false. At this point the
report can grab the values from the form.

Then in the Report's close event, use code to close the form.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote

I have that on the button on the form to open the report based on the
criteria I selected from the combo box to open the report and close the form
once the report is open.

"John Spencer" wrote:

What are you attempting to do with these lines?
DoCmd.SelectObject acForm, "Select Unit Classification"
DoCmd.Close

If the form you want to use is Select Unit Classification then you cannot do
it from this button after you have already opened the report. TOO LATE.

Also using DoCmd.Close without specifying the object type and specific object
name is asking for trouble. DoCmd.Close will close whatever object currently
has the focus. That code be the form you are on, the report you just opened,
or the form you just opened.

That code does not look like what I suggested. Are you trying to open a form
to get parameters and then open the report?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
Thank you John, with this the reports opens the form, but when I select the
criteria from the form another window pops up asking for the parameter value.
I am guessing I need to change something on the form. This is what I have on
the MSV for the button on my form:

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String

stDocName = "Rpt Position Summary by Unit Class"
DoCmd.OpenReport stDocName, acPreview

DoCmd.SelectObject acForm, "Select Unit Classification"
DoCmd.Close

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub


"John Spencer" wrote:

== Open the report in design view
== Click in the little square at the top left of the window (where the rulers
intersect)
== If the properties pane is not showing, right-click on the square and select
properties.
== Click on the EVENT TAB
== Click in the ON Open
== Select the Event Procedure from the drop down
== Click on the ... button

The procedure would be something like
Private Sub Report_Open(Cancel As Integer)
'Open a form and stop all processing outside the form
'until the form is closed or made invisible
Docmd.OpenForm "MyParameterForm",,,,,acdialog
End Sub

That should open the form.
Now you need to code the button on the form to set the form's visible property
to False instead of closing the form.

Me.Visible = False

At this point the form is still available and the recordsource for your report
can get information from the form.

Why do I advise against it? It just seems to be more complex then opening the
form, getting the parameters, and executing the report from a button on the
form. It does have the advantage of being able to use the form for input on
multiple reports without having to have the user specify which report to open
from the parameter form.

It does not allow the user to cancel the report without more code. And that
introduces the need to trap for errors when you cancel the report based on the
trapping whether or not the form is open. If you close the form instead of
hiding the visibility, you can then add code to the open event of the report
to check if the form is open or not. If the form is closed, then you cancel
the report (Cancel = True)

Also, you now need to add code to the Close event to close the parameter form.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
How can I do that? I tried to create the open event but I guess I am not
doing it correctly. Why do you advise against it?

"John Spencer" wrote:

It can be done, but I would advise against it.

In the REPORTS open event, you would need to open the form in dialog mode
(that halts all processing except in the opened form), set the values, and use
the button to set the form's visible property to false. At this point the
report can grab the values from the form.

Then in the Report's close event, use code to close the form.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
Hi,

I have a report and form that are "connected" to each other, meaning that I
select the cirteria on the form (combo box) and the reports opens based on
that criteria. What I want is to do somewhat of the opposite. I want that
when I click on the report, the form opens for the user to select the
criteria and then the reports will open based on that. I hope I am making
sense!

This is it:

SELECT [UnitClassification], [UnitClassification] FROM tblUnitClassification
ORDER BY [UnitClassification];

"John Spencer" wrote:

Does your record source for the report reference the values on the controls on
the form to filter the records?

Post the SQL view of the record source.

Or are you trying to build a filter based on the form's control values?


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
John, I tought I was done, but I am not! . I followed your steps here and
they worked very nice, the problem is that now the reports opens up showing
the same information no matter what is selected from the combo box. What am I
missing?

"John Spencer" wrote:

It can be done, but I would advise against it.

In the REPORTS open event, you would need to open the form in dialog mode
(that halts all processing except in the opened form), set the values, and use
the button to set the form's visible property to false. At this point the
report can grab the values from the form.

Then in the Report's close event, use code to close the form.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote

You can't close the form. If you do it won't be available for the report to
get values from. set the form's visible property to false.

Forms("Select Unit Classification").Visible = False

Did you try my suggested version for opening the report and then having the
report open the form. If so, did it fail and how did it fail?



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
I have that on the button on the form to open the report based on the
criteria I selected from the combo box to open the report and close the form
once the report is open.

"John Spencer" wrote:

What are you attempting to do with these lines?
DoCmd.SelectObject acForm, "Select Unit Classification"
DoCmd.Close

If the form you want to use is Select Unit Classification then you cannot do
it from this button after you have already opened the report. TOO LATE.

Also using DoCmd.Close without specifying the object type and specific object
name is asking for trouble. DoCmd.Close will close whatever object currently
has the focus. That code be the form you are on, the report you just opened,
or the form you just opened.

That code does not look like what I suggested. Are you trying to open a form
to get parameters and then open the report?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
Thank you John, with this the reports opens the form, but when I select the
criteria from the form another window pops up asking for the parameter value.
I am guessing I need to change something on the form. This is what I have on
the MSV for the button on my form:

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String

stDocName = "Rpt Position Summary by Unit Class"
DoCmd.OpenReport stDocName, acPreview

DoCmd.SelectObject acForm, "Select Unit Classification"
DoCmd.Close

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub


"John Spencer" wrote:

== Open the report in design view
== Click in the little square at the top left of the window (where the rulers
intersect)
== If the properties pane is not showing, right-click on the square and select
properties.
== Click on the EVENT TAB
== Click in the ON Open
== Select the Event Procedure from the drop down
== Click on the ... button

The procedure would be something like
Private Sub Report_Open(Cancel As Integer)
'Open a form and stop all processing outside the form
'until the form is closed or made invisible
Docmd.OpenForm "MyParameterForm",,,,,acdialog
End Sub

That should open the form.
Now you need to code the button on the form to set the form's visible property
to False instead of closing the form.

Me.Visible = False

At this point the form is still available and the recordsource for your report
can get information from the form.

Why do I advise against it? It just seems to be more complex then opening the
form, getting the parameters, and executing the report from a button on the
form. It does have the advantage of being able to use the form for input on
multiple reports without having to have the user specify which report to open
from the parameter form.

It does not allow the user to cancel the report without more code. And that
introduces the need to trap for errors when you cancel the report based on the
trapping whether or not the form is open. If you close the form instead of
hiding the visibility, you can then add code to the open event of the report
to check if the form is open or not. If the form is closed, then you cancel
the report (Cancel = True)

Also, you now need to add code to the Close event to close the parameter form.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
How can I do that? I tried to create the open event but I guess I am not
doing it correctly. Why do you advise against it?

"John Spencer" wrote:

It can be done, but I would advise against it.

In the REPORTS open event, you would need to open the form in dialog mode
(that halts all processing except in the opened form), set the values, and use
the button to set the form's visible property to false. At this point the
report can grab the values from the form.

Then in the Report's close event, use code to close the form.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
Hi,

I have a report and form that are "connected" to each other, meaning that I
select the cirteria on the form (combo box) and the reports opens based on
that criteria. What I want is to do somewhat of the opposite. I want that
when I click on the report, the form opens for the user to select the
criteria and then the reports will open based on that. I hope I am making
sense!

I did used your suggestions for the report to open the form and that worked
wonderfully , but I still can't get the form to pull the criteria according
to what is selected. It always gives me the same info. I changed the close
form for the visible=false and nothing.

"John Spencer" wrote:

You can't close the form. If you do it won't be available for the report to
get values from. set the form's visible property to false.

Forms("Select Unit Classification").Visible = False

Did you try my suggested version for opening the report and then having the
report open the form. If so, did it fail and how did it fail?



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
I have that on the button on the form to open the report based on the
criteria I selected from the combo box to open the report and close the form
once the report is open.

"John Spencer" wrote:

What are you attempting to do with these lines?
DoCmd.SelectObject acForm, "Select Unit Classification"
DoCmd.Close

If the form you want to use is Select Unit Classification then you cannot do
it from this button after you have already opened the report. TOO LATE.

Also using DoCmd.Close without specifying the object type and specific object
name is asking for trouble. DoCmd.Close will close whatever object currently
has the focus. That code be the form you are on, the report you just opened,
or the form you just opened.

That code does not look like what I suggested. Are you trying to open a form
to get parameters and then open the report?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
Thank you John, with this the reports opens the form, but when I select the
criteria from the form another window pops up asking for the parameter value.
I am guessing I need to change something on the form. This is what I have on
the MSV for the button on my form:

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String

stDocName = "Rpt Position Summary by Unit Class"
DoCmd.OpenReport stDocName, acPreview

DoCmd.SelectObject acForm, "Select Unit Classification"
DoCmd.Close

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub


"John Spencer" wrote:

== Open the report in design view
== Click in the little square at the top left of the window (where the rulers
intersect)
== If the properties pane is not showing, right-click on the square and select
properties.
== Click on the EVENT TAB
== Click in the ON Open
== Select the Event Procedure from the drop down
== Click on the ... button

The procedure would be something like
Private Sub Report_Open(Cancel As Integer)
'Open a form and stop all processing outside the form
'until the form is closed or made invisible
Docmd.OpenForm "MyParameterForm",,,,,acdialog
End Sub

That should open the form.
Now you need to code the button on the form to set the form's visible property
to False instead of closing the form.

Me.Visible = False

At this point the form is still available and the recordsource for your report
can get information from the form.

Why do I advise against it? It just seems to be more complex then opening the
form, getting the parameters, and executing the report from a button on the
form. It does have the advantage of being able to use the form for input on
multiple reports without having to have the user specify which report to open
from the parameter form.

It does not allow the user to cancel the report without more code. And that
introduces the need to trap for errors when you cancel the report based on the
trapping whether or not the form is open. If you close the form instead of
hiding the visibility, you can then add code to the open event of the report
to check if the form is open or not. If the form is closed, then you cancel
the report (Cancel = True)

Also, you now need to add code to the Close event to close the parameter form.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
How can I do that? I tried to create the open event but I guess I am not
doing it correctly. Why do you advise against it?

"John Spencer" wrote:

It can be done, but I would advise against it.

In the REPORTS open event, you would need to open the form in dialog mode
(that halts all processing except in the opened form), set the values, and use
the button to set the form's visible property to false. At this point the
report can grab the values from the form.

Then in the Report's close event, use code to close the form.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
Hi,

I have a report and form that are "connected" to each other, meaning that I
select the cirteria on the form (combo box) and the reports opens based on
that criteria. What I want is to do somewhat of the opposite. I want that
when I click on the report, the form opens for the user to select the
criteria and then the reports will open based on that. I hope I am making
sense!

Where is Command9? Is it a command button on the report, or in another form
(it looks like a form).

What does your code look like in the Reports Open or Load event?

By failing to include the acDialog as a parameter in the OpenReport method,
you are allowing the lines of code in the Command9_Click event to continue,
which closes the "Select Unit Classification" form. The line that opens the
report should read:

docmd.OpenReport stDocName, acPreview, , , acDialog

I would then remove all of the lines after that one from the Command9_Click
event, and add a single line of code to the REPORTs close event

Private Sub Report_Close

docmd.close acform, "Select Unit Classification"

End Sub

HTH

Dale

"Arlene" wrote in message
...

Thank you John, with this the reports opens the form, but when I select
the
criteria from the form another window pops up asking for the parameter
value.
I am guessing I need to change something on the form. This is what I have
on
the MSV for the button on my form:

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String

stDocName = "Rpt Position Summary by Unit Class"
DoCmd.OpenReport stDocName, acPreview

DoCmd.SelectObject acForm, "Select Unit Classification"
DoCmd.Close

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub


"John Spencer" wrote:

== Open the report in design view
== Click in the little square at the top left of the window (where the
rulers
intersect)
== If the properties pane is not showing, right-click on the square and
select
properties.
== Click on the EVENT TAB
== Click in the ON Open
== Select the Event Procedure from the drop down
== Click on the ... button

The procedure would be something like
Private Sub Report_Open(Cancel As Integer)
'Open a form and stop all processing outside the form
'until the form is closed or made invisible
Docmd.OpenForm "MyParameterForm",,,,,acdialog
End Sub

That should open the form.
Now you need to code the button on the form to set the form's visible
property
to False instead of closing the form.

Me.Visible = False

At this point the form is still available and the recordsource for your
report
can get information from the form.

Why do I advise against it? It just seems to be more complex then
opening the
form, getting the parameters, and executing the report from a button on
the
form. It does have the advantage of being able to use the form for input
on
multiple reports without having to have the user specify which report to
open
from the parameter form.

It does not allow the user to cancel the report without more code. And
that
introduces the need to trap for errors when you cancel the report based
on the
trapping whether or not the form is open. If you close the form
instead of
hiding the visibility, you can then add code to the open event of the
report
to check if the form is open or not. If the form is closed, then you
cancel
the report (Cancel = True)

Also, you now need to add code to the Close event to close the parameter
form.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
How can I do that? I tried to create the open event but I guess I am
not
doing it correctly. Why do you advise against it?

"John Spencer" wrote:

It can be done, but I would advise against it.

In the REPORTS open event, you would need to open the form in dialog
mode
(that halts all processing except in the opened form), set the values,
and use
the button to set the form's visible property to false. At this point
the
report can grab the values from the form.

Then in the Report's close event, use code to close the form.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
Hi,

I have a report and form that are "connected" to each other, meaning
that I
select the cirteria on the form (combo box) and the reports opens
based on
that criteria. What I want is to do somewhat of the opposite. I want
that
when I click on the report, the form opens for the user to select the
criteria and then the reports will open based on that. I hope I am
making
sense!

In another thread, you posted that the source for the report was

This is it:

SELECT [UnitClassification], [UnitClassification] FROM tblUnitClassification
ORDER BY [UnitClassification];

Notice that there is no where clause in that.

You would need something like
This is it:

SELECT [UnitClassification], [UnitClassification]
FROM tblUnitClassification
WHERE [NameOfSomeField] =
Forms![Name Of the Form]![Select Unit Classification]
ORDER BY [UnitClassification]

Or you might be able to create a filter, by using code immediately after you
call the form. The following is speculation on my part and I can't test the
syntax of this right now.

Me.Filter = "[Name of Some Field] =""" &
Forms![Name Of the Form]![Select Unit Classification] & """"

Me.FilterOn = True


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
I did used your suggestions for the report to open the form and that worked
wonderfully , but I still can't get the form to pull the criteria according
to what is selected. It always gives me the same info. I changed the close
form for the visible=false and nothing.

"John Spencer" wrote:

You can't close the form. If you do it won't be available for the report to
get values from. set the form's visible property to false.

Forms("Select Unit Classification").Visible = False

Did you try my suggested version for opening the report and then having the
report open the form. If so, did it fail and how did it fail?



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
I have that on the button on the form to open the report based on the
criteria I selected from the combo box to open the report and close the form
once the report is open.

"John Spencer" wrote:

What are you attempting to do with these lines?
DoCmd.SelectObject acForm, "Select Unit Classification"
DoCmd.Close

If the form you want to use is Select Unit Classification then you cannot do
it from this button after you have already opened the report. TOO LATE.

Also using DoCmd.Close without specifying the object type and specific object
name is asking for trouble. DoCmd.Close will close whatever object currently
has the focus. That code be the form you are on, the report you just opened,
or the form you just opened.

That code does not look like what I suggested. Are you trying to open a form
to get parameters and then open the report?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
Thank you John, with this the reports opens the form, but when I select the
criteria from the form another window pops up asking for the parameter value.
I am guessing I need to change something on the form. This is what I have on
the MSV for the button on my form:

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String

stDocName = "Rpt Position Summary by Unit Class"
DoCmd.OpenReport stDocName, acPreview

DoCmd.SelectObject acForm, "Select Unit Classification"
DoCmd.Close

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub


"John Spencer" wrote:

== Open the report in design view
== Click in the little square at the top left of the window (where the rulers
intersect)
== If the properties pane is not showing, right-click on the square and select
properties.
== Click on the EVENT TAB
== Click in the ON Open
== Select the Event Procedure from the drop down
== Click on the ... button

The procedure would be something like
Private Sub Report_Open(Cancel As Integer)
'Open a form and stop all processing outside the form
'until the form is closed or made invisible
Docmd.OpenForm "MyParameterForm",,,,,acdialog
End Sub

That should open the form.
Now you need to code the button on the form to set the form's visible property
to False instead of closing the form.

Me.Visible = False

At this point the form is still available and the recordsource for your report
can get information from the form.

Why do I advise against it? It just seems to be more complex then opening the
form, getting the parameters, and executing the report from a button on the
form. It does have the advantage of being able to use the form for input on
multiple reports without having to have the user specify which report to open
from the parameter form.

It does not allow the user to cancel the report without more code. And that
introduces the need to trap for errors when you cancel the report based on the
trapping whether or not the form is open. If you close the form instead of
hiding the visibility, you can then add code to the open event of the report
to check if the form is open or not. If the form is closed, then you cancel
the report (Cancel = True)

Also, you now need to add code to the Close event to close the parameter form.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
How can I do that? I tried to create the open event but I guess I am not
doing it correctly. Why do you advise against it?

"John Spencer" wrote:

It can be done, but I would advise against it.

In the REPORTS open event, you would need to open the form in dialog mode
(that halts all processing except in the opened form), set the values, and use
the button to set the form's visible property to false. At this point the
report can grab the values from the form.

Then in the Report's close event, use code to close the form.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Arlene wrote:
Hi,

I have a report and form that are "connected" to each other, meaning that I
select the cirteria on the form (combo box) and the reports opens based on
that criteria. What I want is to do somewhat of the opposite. I want that
when I click on the report, the form opens for the user to select the
criteria and then the reports will open based on that. I hope I am making
sense!