Use combox box to find a record (2000 SR1a)

Topic

New Reply

I have a combo box set up to find records on a form which was set up using the combo box wizard. The form also has on it an option group that filters the records depending on the option chosen: Active Members, Inactive Members or All Members. This is a checkbox field on the form. My problem is how do I get the recordset in the combo box to reflect the filtered recordset in the form since one only wants the drop down list to reflect choices currently available and the combo box is currently showing all records in the underlying query, not the filtered group. Here is the code from the AfterUpdate event of the combo box.

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst “[FAMILYNAME] = ‘” & Me![Combo50] & “‘”
Me.Bookmark = rs.Bookmark

Reply | Quote

Replies

You should put code in the After Update event of the option group to change the Row Source of the combo box, or to requery it, depending on how you have set it up. Here is a fictitious example:

Private Sub grpMembership_AfterUpdate()
Select Case Me.grpMembership
Case 1 ‘ Active
Me.Combo50.RowSource = “SELECT FamilyName FROM tblSomething WHERE Active = True”
Case 2 ‘ Inactive
Me.Combo50.RowSource = “SELECT FamilyName FROM tblSomething WHERE Active = False”
Case 3 ‘ All
Me.Combo50.RowSource = “SELECT FamilyName FROM tblSomething”
End Select
End Sub

Reply | Quote

Thanks Hans. That got me pointed in the right direction and I now have it working. As always, you’ve saved me hours of research. If only I’d writeen before the first two or three hours of trying to figure it out on my own!

Reply | Quote