Filter report on a form

[Arlene]

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!

 

[John Spencer]

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]

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?

 

[Dale Fye]

Arlene, I generally create a Reports form, which lists all of the reports,
and which contains all of the various fields that I might want to filter the
report on (usually on several different tabs, as each report may have fields
specific to that report). I fill in the filter parameters on that form, and
hit the "Preview" button to view the report with filters applied.

Having said that, I've been wanting to look into the feasibilty of doing
this since someone asked about having a combo box popup instead of an input
box for a parameter query. So, I created a filtering form (frm_Test) with a
textbox and a command button. In the Click event of the command button, you
need to hide the form (me.visible = false), which will allow the code in the
reports Load event to resume processing. I modified the Reports Load event
as indicated below.

Private Sub Report_Load()

Me.Visible = False
DoCmd.OpenForm "frm_Test", , , , , acDialog
Me.Filter = "Act_FY = """ & Forms!frm_Test.txt_FY & """"
Me.FilterOn = True
DoCmd.Close acForm, "frm_Test"
Me.Visible = True

End Sub

It worked superbly. I then modified the textbox to a combobox and that
worked too.

 

[John Spencer]

== 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]

I tried 2 different ways under the OnOpen on the report and in both I get the
same message to enter parameter value. Can someone please help me figure out
what I am doing wrong. I have no idea what I am doing.

My form name is Select Unit Classification and the combo box name is
UnitClassification.

Here is what I entered in the MVS:



Private Sub UnitClassification_Click()
On Error GoTo UnitClassification_Click_Err

If (IsNull(UnitClassification)) Then
Beep
End If
If (Not IsNull(Callnummer)) Then
DoCmd.OpenForm "[Select Unit Classification]", acNormal, "",
"[cboUnitClassification]=" & UnitClassification
acFormEdit , acWindowNormal
On Error Resume Next
DoCmd.Requery ""
End If

UnitClassification_Click_Exit:
Exit Sub

UnitClassification_Click_Err:
MsgBox Error$
Resume UnitClassification_Click_Exit

End Sub


Second try:

Private Sub Report_Load()

Me.Visible = False
DoCmd.OpenForm "Selec Unit Classification", , , , , acDialog
Me.Filter = "Unitclassification = """ & Forms![Select Unit
Classification] & """"
Me.FilterOn = True
DoCmd.Close acForm, "Select Unit Classification"
Me.Visible = True

End Sub

 

[Arlene]

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

 

[Arlene]

Never mind, I got it!!!!! I had a criteria in my query that I tought I had
delete but I didn't. Thank you John and Dale!

I tried 2 different ways under the OnOpen on the report and in both I get the
same message to enter parameter value. Can someone please help me figure out
what I am doing wrong. I have no idea what I am doing.

My form name is Select Unit Classification and the combo box name is
UnitClassification.

Here is what I entered in the MVS:



Private Sub UnitClassification_Click()
On Error GoTo UnitClassification_Click_Err

If (IsNull(UnitClassification)) Then
Beep
End If
If (Not IsNull(Callnummer)) Then
DoCmd.OpenForm "[Select Unit Classification]", acNormal, "",
"[cboUnitClassification]=" & UnitClassification
acFormEdit , acWindowNormal
On Error Resume Next
DoCmd.Requery ""
End If

UnitClassification_Click_Exit:
Exit Sub

UnitClassification_Click_Err:
MsgBox Error$
Resume UnitClassification_Click_Exit

End Sub


Second try:

Private Sub Report_Load()

Me.Visible = False
DoCmd.OpenForm "Selec Unit Classification", , , , , acDialog
Me.Filter = "Unitclassification = """ & Forms![Select Unit
Classification] & """"
Me.FilterOn = True
DoCmd.Close acForm, "Select Unit Classification"
Me.Visible = True

End Sub

Arlene, I generally create a Reports form, which lists all of the reports,
and which contains all of the various fields that I might want to filter the
report on (usually on several different tabs, as each report may have fields
specific to that report). I fill in the filter parameters on that form, and
hit the "Preview" button to view the report with filters applied.

Having said that, I've been wanting to look into the feasibilty of doing
this since someone asked about having a combo box popup instead of an input
box for a parameter query. So, I created a filtering form (frm_Test) with a
textbox and a command button. In the Click event of the command button, you
need to hide the form (me.visible = false), which will allow the code in the
reports Load event to resume processing. I modified the Reports Load event
as indicated below.

Private Sub Report_Load()

Me.Visible = False
DoCmd.OpenForm "frm_Test", , , , , acDialog
Me.Filter = "Act_FY = """ & Forms!frm_Test.txt_FY & """"
Me.FilterOn = True
DoCmd.Close acForm, "frm_Test"
Me.Visible = True

End Sub

It worked superbly. I then modified the textbox to a combobox and that
worked too.

 

[Arlene]

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]

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

 

[John Spencer]

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]

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.

 

[Arlene]

This is it:

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

 

[John Spencer]

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]

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.

 

[Dale Fye]

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

 

[John Spencer]

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