Filtering a search combo box

T

Tal

I need some help filtering the results displayed in a combo box.
I have a combo box "SelectCampaign" that enables the user to choose the
campaign, which then limits the list that can be chosen in the SelectFund
combo box, which then displays a record and limits the list of Fund
Recipients.
That all works great.
Here is the issue:

Funds can be open or closed. I have a Yes/No field set up to define that.
I would like to have command buttons that will filter the SelectFund combo
box to either Show Only Open Funds or Show All Funds.
I have no earthly idea how to add the code for that.
Many thanks for your help.


Here is the current code on my form.


Private Sub cboSelectCampaign_AfterUpdate()
Dim SFundSource As String
SFundSource = "SELECT [tblFunds].[keyFund], [tblFunds].[txtFundName] " & _
"FROM tblFunds " & _
"WHERE [tblFunds].[keyCampaign] = " &
Me.cboSelectCampaign.Value
Me.cboSelectFund.RowSource = SFundSource
Me.cboSelectFund.Requery

End Sub

Private Sub cboSelectFund_AfterUpdate()
Dim strWhere As String
Const strStub = "SELECT * FROM qryClientandFundJunction "

If Not IsNull(Me.cboSelectFund) Then
strWhere = "WHERE (keyFund = " & Me.cboSelectFund & ")"
End If
Forms![frmEditFunds].RecordSource = strStub & strWhere
End Sub

Private Sub cboSelectFund_LostFocus()
Dim SFundRecipientSource As String
SFundRecipientSource = "SELECT DISTINCT
[qryClientandFundJunction].[keyFundRecipient],
[qryClientandFundJunction].[compFileAs],
[qryClientandFundJunction].[txtFundRecipientRelationship] " & _
"FROM qryClientandFundJunction " & _
"WHERE [qryClientandFundJunction].[keyFund] = "
& Me.cboSelectFund.Value
Me.lisSelectRecipient.RowSource = SFundRecipientSource
Me.lisSelectRecipient.Requery

End Sub

Private Sub cboSelectRecipient_LostFocus()
Me!cboSelectRecipient.Value = ""
End Sub


Private Sub Form_Load()
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

Private Sub lisSelectRecipient_AfterUpdate()
Dim strWhere As String
Const strStub = "SELECT * FROM qryClientandFundJunction "

If Not IsNull(Me.lisSelectRecipient) Then
strWhere = "WHERE (keyFundRecipient = " & Me.lisSelectRecipient & ")"
End If
Forms![frmEditFunds].RecordSource = strStub & strWhere

Me!cboSelectFund.Value = ""
Me!cboSelectCampaign.Value = ""
End Sub
 
M

Mark A. Sam

Tal,

I'm not sure (an I think no one else was) what you are asking. You seem to
grasp the need to change the Rowsource of the combo boxes. Is you code not
doing that? This looks like a problem to me:

Private Sub cboSelectCampaign_AfterUpdate()
Dim SFundSource As String
SFundSource = "SELECT [tblFunds].[keyFund], [tblFunds].[txtFundName] " &
_
"FROM tblFunds " & _
"WHERE [tblFunds].[keyCampaign] = " &
Me.cboSelectCampaign.Value
Me.cboSelectFund.RowSource = SFundSource
Me.cboSelectFund.Requery

End Sub


You are setting the combo box rowsource with a string variable. When the
event procedure terminates execution, the variable is no longer available.
Try this:

Private Sub cboSelectCampaign_AfterUpdate()

Me.cboSelectFund.RowSource = "SELECT [tblFunds].[keyFund],
[tblFunds].[txtFundName] " & _
"FROM tblFunds " & _
"WHERE [tblFunds].[keyCampaign] = " &
Me.cboSelectCampaign.Value

Me.cboSelectFund.Requery

End Sub

Just set the Rowsource property to the sql string.

God Bless,

Mark A. Sam








Tal said:
I need some help filtering the results displayed in a combo box.
I have a combo box "SelectCampaign" that enables the user to choose the
campaign, which then limits the list that can be chosen in the SelectFund
combo box, which then displays a record and limits the list of Fund
Recipients.
That all works great.
Here is the issue:

Funds can be open or closed. I have a Yes/No field set up to define that.
I would like to have command buttons that will filter the SelectFund combo
box to either Show Only Open Funds or Show All Funds.
I have no earthly idea how to add the code for that.
Many thanks for your help.


Here is the current code on my form.


Private Sub cboSelectCampaign_AfterUpdate()
Dim SFundSource As String
SFundSource = "SELECT [tblFunds].[keyFund], [tblFunds].[txtFundName] "
& _
"FROM tblFunds " & _
"WHERE [tblFunds].[keyCampaign] = " &
Me.cboSelectCampaign.Value
Me.cboSelectFund.RowSource = SFundSource
Me.cboSelectFund.Requery

End Sub

Private Sub cboSelectFund_AfterUpdate()
Dim strWhere As String
Const strStub = "SELECT * FROM qryClientandFundJunction "

If Not IsNull(Me.cboSelectFund) Then
strWhere = "WHERE (keyFund = " & Me.cboSelectFund & ")"
End If
Forms![frmEditFunds].RecordSource = strStub & strWhere
End Sub

Private Sub cboSelectFund_LostFocus()
Dim SFundRecipientSource As String
SFundRecipientSource = "SELECT DISTINCT
[qryClientandFundJunction].[keyFundRecipient],
[qryClientandFundJunction].[compFileAs],
[qryClientandFundJunction].[txtFundRecipientRelationship] " & _
"FROM qryClientandFundJunction " & _
"WHERE [qryClientandFundJunction].[keyFund] = "
& Me.cboSelectFund.Value
Me.lisSelectRecipient.RowSource = SFundRecipientSource
Me.lisSelectRecipient.Requery

End Sub

Private Sub cboSelectRecipient_LostFocus()
Me!cboSelectRecipient.Value = ""
End Sub


Private Sub Form_Load()
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

Private Sub lisSelectRecipient_AfterUpdate()
Dim strWhere As String
Const strStub = "SELECT * FROM qryClientandFundJunction "

If Not IsNull(Me.lisSelectRecipient) Then
strWhere = "WHERE (keyFundRecipient = " & Me.lisSelectRecipient & ")"
End If
Forms![frmEditFunds].RecordSource = strStub & strWhere

Me!cboSelectFund.Value = ""
Me!cboSelectCampaign.Value = ""
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Quick syntax help in a combo box 5
Combo box doesn't search last names with apostrophe 2
Combine combo boxes with date range 15
Combo Box Filtering Issues 2
MultiList Select Box Search Form 7
strSQL in Search Form 4
Create Two Filters in Form to Execute Report. 6
Multiselect List Box 4

Top