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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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" u58123@uwe wrote in message news:a8fc803873f35@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/For...ports/201006/1 |
#3
|
|||
|
|||
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 | |
|
|