filrter several reports

  • Thread starter JOSELUIS via AccessMonster.com
  • Start date
J

JOSELUIS via AccessMonster.com

I create an unbound form to filter a report and i paste the following code in
the filter button of the form, and it works perfectly; but my problem is that
I need to build this filter to most of my reports so what I don´t want is to
create a form of this type for each report. My question is: Is There an easy
way to use this form to all these reports? Thank you in anticipation

Private Sub cmdSearch_Click()
Dim varWhere As Variant
Dim rst As DAO.Recordset

' Initialize to Null
varWhere = Null

' OK, start building the filter
If Not IsNothing(Me.cmbCompanyID) Then
' .. build the predicate
' Must use a subquery here because the value is in a linking table...
varWhere = (varWhere + " AND ") & _
"([ContactID] IN (SELECT ContactID FROM qryCompanyContacts " & _
"WHERE qryCompanyContacts.CompanyID = " & Me.cmbCompanyID & "))"
End If


' Check to see that we built a filter
If IsNothing(varWhere) Then
MsgBox "Debe introducir al menos un criterio de busqueda.",
vbInformation, gstrAppTitle
Exit Sub
End If

' Open a recordset to see if any rows returned with this filter
Set rst = CurrentDb.OpenRecordset("SELECT tblContacts.* FROM tblContacts
WHERE " & varWhere)
' See if found none
If rst.RecordCount = 0 Then
MsgBox "Ninguna persona aparece con este criterio.", vbInformation,
gstrAppTitle
' Clean up recordset
rst.Close
Set rst = Nothing
Exit Sub
End If

' Hide me to fix later focus problems
Me.Visible = False

' Show the full contacts info filtered
DoCmd.OpenReport "rptContactList", acViewPreview, WhereCondition:
=varWhere
DoCmd.Maximize

' Done
DoCmd.Close acForm, Me.Name
' Clean up recordset
rst.Close
Set rst = Nothing

End Sub
 
A

Allen Browne

Yes: you can use one form to filter several reports.

You need a control where the user selects which report they want. That could
be a option group, or a combo or list box. Use the AfterUpdate event of that
control to show the filter boxes that apply to that report and hide the
boxes that don't apply.

Then in the Click event of the command button that actually opens the
report, you build the filter string from the controls that are Visible and
Not Null, and finally OpenReport with that WhereCondition.

The example below assumes an option group where each report is one of the
buttons. In the AfterUpdate of the option group (named grpReport), you call
it like this so that the combo for filtering on a client (cblClientID) is
shown if the chose option button optJob or optClient or optClientList:

Dim bShow As Boolean
bShow = GroupMatchesButtons(Me.grpReport, Me.optJob, Me.optClient,
Me.optClientList)
With Me.cboClientID
If .Visible <> bShow Then
.Visible = bShow
End If
End With

Here's the function:

Private Function GroupMatchesButtons(grp As OptionGroup, ParamArray
aoptButtons()) As Boolean
On Error GoTo Err_Handler
'Purpose: Return True if the option group value matches on one of the
option buttons named in the array.
Dim i As Integer

For i = LBound(aoptButtons) To UBound(aoptButtons)
If aoptButtons(i).OptionValue = grp.Value Then
GroupMatchesButtons = True
Exit For
End If
Next

Exit_Handler:
Exit Function

Err_Handler:
Call LogError(Err.Number, Err.Description, conMod &
".MatchesOptionButtons")
Resume Exit_Handler
End Function

HTH
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


JOSELUIS via AccessMonster.com said:
I create an unbound form to filter a report and i paste the following code
in
the filter button of the form, and it works perfectly; but my problem is
that
I need to build this filter to most of my reports so what I don´t want is
to
create a form of this type for each report. My question is: Is There an
easy
way to use this form to all these reports? Thank you in anticipation

Private Sub cmdSearch_Click()
Dim varWhere As Variant
Dim rst As DAO.Recordset

' Initialize to Null
varWhere = Null

' OK, start building the filter
If Not IsNothing(Me.cmbCompanyID) Then
' .. build the predicate
' Must use a subquery here because the value is in a linking
table...
varWhere = (varWhere + " AND ") & _
"([ContactID] IN (SELECT ContactID FROM qryCompanyContacts " &
_
"WHERE qryCompanyContacts.CompanyID = " & Me.cmbCompanyID &
"))"
End If


' Check to see that we built a filter
If IsNothing(varWhere) Then
MsgBox "Debe introducir al menos un criterio de busqueda.",
vbInformation, gstrAppTitle
Exit Sub
End If

' Open a recordset to see if any rows returned with this filter
Set rst = CurrentDb.OpenRecordset("SELECT tblContacts.* FROM
tblContacts
WHERE " & varWhere)
' See if found none
If rst.RecordCount = 0 Then
MsgBox "Ninguna persona aparece con este criterio.", vbInformation,
gstrAppTitle
' Clean up recordset
rst.Close
Set rst = Nothing
Exit Sub
End If

' Hide me to fix later focus problems
Me.Visible = False

' Show the full contacts info filtered
DoCmd.OpenReport "rptContactList", acViewPreview,
WhereCondition:
=varWhere
DoCmd.Maximize

' Done
DoCmd.Close acForm, Me.Name
' Clean up recordset
rst.Close
Set rst = Nothing

End Sub
 
J

JOSELUIS via AccessMonster.com

Thank you very much.

Allen said:
Yes: you can use one form to filter several reports.

You need a control where the user selects which report they want. That could
be a option group, or a combo or list box. Use the AfterUpdate event of that
control to show the filter boxes that apply to that report and hide the
boxes that don't apply.

Then in the Click event of the command button that actually opens the
report, you build the filter string from the controls that are Visible and
Not Null, and finally OpenReport with that WhereCondition.

The example below assumes an option group where each report is one of the
buttons. In the AfterUpdate of the option group (named grpReport), you call
it like this so that the combo for filtering on a client (cblClientID) is
shown if the chose option button optJob or optClient or optClientList:

Dim bShow As Boolean
bShow = GroupMatchesButtons(Me.grpReport, Me.optJob, Me.optClient,
Me.optClientList)
With Me.cboClientID
If .Visible <> bShow Then
.Visible = bShow
End If
End With

Here's the function:

Private Function GroupMatchesButtons(grp As OptionGroup, ParamArray
aoptButtons()) As Boolean
On Error GoTo Err_Handler
'Purpose: Return True if the option group value matches on one of the
option buttons named in the array.
Dim i As Integer

For i = LBound(aoptButtons) To UBound(aoptButtons)
If aoptButtons(i).OptionValue = grp.Value Then
GroupMatchesButtons = True
Exit For
End If
Next

Exit_Handler:
Exit Function

Err_Handler:
Call LogError(Err.Number, Err.Description, conMod &
".MatchesOptionButtons")
Resume Exit_Handler
End Function

HTH
I create an unbound form to filter a report and i paste the following code
in
[quoted text clipped - 62 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top