By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,209 Members | 1,100 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,209 IT Pros & Developers. It's quick & easy.

ADODB.Recordset from Forms!SomeForm!ListBox.Recordset?!?!

P: n/a
CFW
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
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.