Prev: Firstname plus Middlename??
Next: Prevent skip to new page after last page prints
From: JOSELUIS via AccessMonster.com on 3 Jun 2010 14:51
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201006/1

From: Allen Browne on 3 Jun 2010 21:06
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"JOSELUIS via AccessMonster.com" <u58123(a)uwe> wrote in message
news:a8fc803873f35(a)uwe...
> 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
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201006/1
>
From: JOSELUIS via AccessMonster.com on 4 Jun 2010 06:38
Thank you very much.

Allen Browne wrote:
>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]
>>
>> End Sub

--
Message posted via http://www.accessmonster.com

 | 
Pages: 1
Prev: Firstname plus Middlename??
Next: Prevent skip to new page after last page prints