A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

filrter several reports



 
 
Thread Tools Display Modes
  #1  
Old June 3rd, 2010, 07:51 PM posted to microsoft.public.access.reports
JOSELUIS via AccessMonster.com
external usenet poster
 
Posts: 26
Default filrter several reports

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/For...ports/201006/1

Ads
  #2  
Old June 4th, 2010, 02:06 AM posted to microsoft.public.access.reports
Allen Browne
external usenet poster
 
Posts: 11,706
Default filrter several reports

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" [email protected] wrote in message
news:[email protected]
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/For...ports/201006/1

  #3  
Old June 4th, 2010, 11:38 AM posted to microsoft.public.access.reports
JOSELUIS via AccessMonster.com
external usenet poster
 
Posts: 26
Default filrter several reports

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:32 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.