Option Group changing Combo Box

[Guest]

Does anyone know how make an option group affect the filter of a combo box?
I have a combo box that has DR and CR, and an option group 100 and 200. If a
user selects option 100 I would like to see only items associated with CR and
if the user selects 200, then only items associated with DR would appear in
the combo box. Below is what I have so far.....but it just doesn't work.
Any help is very much appreciated. Thanks!

Private Sub Frame309_AfterUpdate()
Dim strSql As String
strSql = "SELECT qcatsub1.CategoryName, qcatsub1.frombankname,
qcatsub1.frombankacctnumber FROM qcatsub1"
If Frame309 = 100 Then
Me.Filter = "qcatsub1.transtype = 'CR'"
Me.FilterOn = True
strSql = strSql & "WHERE qcatsub1.transtype = 'CR'"
ElseIf Frame309 = 200 Then
Me.Filter = "qcatsub1.transtype = 'DR'"
Me.FilterOn = True
strSql = strSql & "WHERE qcatsub1.transtype = 'DR'"
End If
strSql = strSql & " ORDER BY [CategoryName], [frombankacctnumber],
[frombankname];"
Me.Combo276.RowSource = strSql
End Sub

 

[Ken Snell \(MVP\)]

You're very close... no need to use Filter property, though. Just
concatenate the WHERE clause into the SQL string (you also need a space in
front of the WHERE word in the string):

Private Sub Frame309_AfterUpdate()
Dim strSql As String
strSql = "SELECT qcatsub1.CategoryName, qcatsub1.frombankname,
qcatsub1.frombankacctnumber FROM qcatsub1"
If Frame309 = 100 Then
strSql = strSql & " WHERE qcatsub1.transtype = 'CR'"
ElseIf Frame309 = 200 Then
strSql = strSql & " WHERE qcatsub1.transtype = 'DR'"
End If
strSql = strSql & " ORDER BY [CategoryName], [frombankacctnumber],
[frombankname];"
Me.Combo276.RowSource = strSql
End Sub

 

[Guest]

Ken, it worked!! I just could find the problem myself...thanks for being
there and helping out!

You're very close... no need to use Filter property, though. Just
concatenate the WHERE clause into the SQL string (you also need a space in
front of the WHERE word in the string):

Private Sub Frame309_AfterUpdate()
Dim strSql As String
strSql = "SELECT qcatsub1.CategoryName, qcatsub1.frombankname,
qcatsub1.frombankacctnumber FROM qcatsub1"
If Frame309 = 100 Then
strSql = strSql & " WHERE qcatsub1.transtype = 'CR'"
ElseIf Frame309 = 200 Then
strSql = strSql & " WHERE qcatsub1.transtype = 'DR'"
End If
strSql = strSql & " ORDER BY [CategoryName], [frombankacctnumber],
[frombankname];"
Me.Combo276.RowSource = strSql
End Sub

--

Ken Snell
<MS ACCESS MVP>

Does anyone know how make an option group affect the filter of a combo
box?
I have a combo box that has DR and CR, and an option group 100 and 200.
If a
user selects option 100 I would like to see only items associated with CR
and
if the user selects 200, then only items associated with DR would appear
in
the combo box. Below is what I have so far.....but it just doesn't work.
Any help is very much appreciated. Thanks!

Private Sub Frame309_AfterUpdate()
Dim strSql As String
strSql = "SELECT qcatsub1.CategoryName, qcatsub1.frombankname,
qcatsub1.frombankacctnumber FROM qcatsub1"
If Frame309 = 100 Then
Me.Filter = "qcatsub1.transtype = 'CR'"
Me.FilterOn = True
strSql = strSql & "WHERE qcatsub1.transtype = 'CR'"
ElseIf Frame309 = 200 Then
Me.Filter = "qcatsub1.transtype = 'DR'"
Me.FilterOn = True
strSql = strSql & "WHERE qcatsub1.transtype = 'DR'"
End If
strSql = strSql & " ORDER BY [CategoryName], [frombankacctnumber],
[frombankname];"
Me.Combo276.RowSource = strSql
End Sub

 

[Ken Snell \(MVP\)]

You're welcome.