Change combo box to match filtered results

[Guest]

Hi

I have a membership database where my membership data entry form includes a
combo box with all members shown in alphabetical orderwhich when clicked goes
to that record. This works fine, however I have now added an otion group with
three buttons to filter according to membership status, active and lapsed
(the first button is selected by default and is for all members ie not
filtered.

Is it possible to have my combo box reflect my filter choices. eg I click
the button for Active and the list in the combo box changes to show just
those names. I guess I need to add some more code to the after update event
of my option group but am having trouble working out what it should be. This
is the existing code:

Private Sub FilterMembers_AfterUpdate()
If FilterMembers = 2 Then
Me.Filter = "membershipstatus = 'Active'"
Me.FilterOn = True

ElseIf FilterClients = 3 Then
Me.Filter = "membershipstatus = 'Lapsed'"
Me.FilterOn = True
Else
Me.FilterOn = False
End If

End Sub

I would be very grateful for any advice or suggestions

Thanks
Chris

 

[Allen Browne]

Assuming that the MembershipStatus field exists in the combo's RowSource,
you can change its RowSource like this:

Private Sub FilterMembers_AfterUpdate()
Dim strSql As String
strSql = "SELECT MemberID, MemberName FROM tblMember "

If FilterMembers = 2 Then
Me.Filter = "membershipstatus = 'Active'"
Me.FilterOn = True
strSql = strSql & "WHERE membershipstatus = 'Active'"
ElseIf FilterClients = 3 Then
Me.Filter = "membershipstatus = 'Lapsed'"
Me.FilterOn = True
strSql = strSql & "WHERE membershipstatus = 'Lapsed'"
Else
Me.FilterOn = False
End If

strSql = strSql & " ORDER BY MemberName;"
Me.[NameOfYourComboHere].RowSource = strSql
End Sub

 

[Guest]

Allen

Many, many thanks

Works like a charm!

Chris

Assuming that the MembershipStatus field exists in the combo's RowSource,
you can change its RowSource like this:

Private Sub FilterMembers_AfterUpdate()
Dim strSql As String
strSql = "SELECT MemberID, MemberName FROM tblMember "

If FilterMembers = 2 Then
Me.Filter = "membershipstatus = 'Active'"
Me.FilterOn = True
strSql = strSql & "WHERE membershipstatus = 'Active'"
ElseIf FilterClients = 3 Then
Me.Filter = "membershipstatus = 'Lapsed'"
Me.FilterOn = True
strSql = strSql & "WHERE membershipstatus = 'Lapsed'"
Else
Me.FilterOn = False
End If

strSql = strSql & " ORDER BY MemberName;"
Me.[NameOfYourComboHere].RowSource = strSql
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a membership database where my membership data entry form includes
a
combo box with all members shown in alphabetical orderwhich when clicked
goes
to that record. This works fine, however I have now added an otion group
with
three buttons to filter according to membership status, active and lapsed
(the first button is selected by default and is for all members ie not
filtered.

Is it possible to have my combo box reflect my filter choices. eg I click
the button for Active and the list in the combo box changes to show just
those names. I guess I need to add some more code to the after update
event
of my option group but am having trouble working out what it should be.
This
is the existing code:

Private Sub FilterMembers_AfterUpdate()
If FilterMembers = 2 Then
Me.Filter = "membershipstatus = 'Active'"
Me.FilterOn = True

ElseIf FilterClients = 3 Then
Me.Filter = "membershipstatus = 'Lapsed'"
Me.FilterOn = True
Else
Me.FilterOn = False
End If

End Sub

I would be very grateful for any advice or suggestions

Thanks
Chris