Connecting Tech Pros Worldwide Help | Site Map

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

 
LinkBack Thread Tools Search this Thread
  #1  
Old November 12th, 2005, 06:14 PM
CFW
Guest
 
Posts: n/a
Default ADODB.Recordset from Forms!SomeForm!ListBox.Recordset?!?!

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

 

Bookmarks

Thread Tools Search this Thread
Search this Thread:

Advanced Search

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

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Popular Articles

What is Bytes?

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 220,662 network members.