View Single Post
  #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