|
I thought this was going to be easy but I'm missing something . . . I
need to open an ADODB recordset using the recordset source for a list
box on my for. When my form opens, the list box ADODB recordset is
established and set during On Open. Next I want to populate a
recordset from that list box so I can filter it on a single field
using the value of a combo box for the filter string. I have a second
combo box that i woul like to use to FURTHER fillter the first filter
results. The procedure below works GREAT for one combo OR the other
but I want the second one selected to further filter the recordset -
not just filter the original recordset. THANKS!!
Dim rstWorkItems As ADODB.Recordset
Dim rstFiltered As ADODB.Recordset
Dim intWorkItemsCount As Integer
Dim strFilterStatus As String
Dim intMsg As Integer
' Open recordset with data from Work Items list box.
Set rstWorkItems = New ADODB.Recordset
rstWorkItems.CursorType = adOpenStatic
rstWorkItems.Open "qry_lstWorkItemDefault",
CurrentProject.Connection, , , adCmdTable
' Populate the Recordset.
intWorkItemsCount = rstWorkItems.RecordCount
' Get status filter
strFilterStatus = Me.cboFilterByStatus.Value
If strFilterStatus <> "" Then
' Open a filtered Recordset object.
Set rstFiltered = FilterField(rstWorkItems, "Status",
strFilterStatus)
Set Forms!frmList!cboWorkItem.Recordset = rstFiltered
If rstFiltered.RecordCount = 0 Then
intMsg = MsgBox("No records found for filter criteria
selected.", vbInformation, "No Records Found")
intWorkItemsCount = 0
Else
intWorkItemsCount = rstFiltered.RecordCount
End If
Else
intWorkItemsCount = 0
End If
Me.NumberListed = intWorkItemsCount
rstFiltered.Close
Public Function FilterField(rstTemp As ADODB.Recordset, _
strField As String, strFilter As String) As ADODB.Recordset
rstTemp.Filter = strField & " = '" & strFilter & "'"
Set FilterField = rstTemp
End Function
| 
