Filter Combo Box off of Option Group

[Guest]

Hello,

I want to filter a combo box based off of a user's selection in an option
group.

The 'Option Group' contains a number of different reports (New, RePrice,
Products...) These items are vaules in a column called 'Request Type' and
are members of a client table.

The combo box has query which returns all the clients in the client table.
What I would like to do is, based upon the users entry (New or Reprice
or....) is only display the clients with that value. If a user selects 'New'
they will only see the 5 clients with request type = 'New'

Any help would be much appreciated.

Thanks
Acase

 

[Guest]

In the option group's OnBeforeUpdate( ) event, place a Select Case block that
assigns a different value for the combo box to filter on. For example:

Private Sub optSelect_BeforeUpdate(Cancel As Integer)

On Error GoTo ErrHandler

Dim sType As String

Const NEW_TYPE As Long = 1
Const REPRICE_TYPE As Long = 2
Const PROD_TYPE As Long = 3

Select Case Me!optSelect.Value
Case NEW_TYPE
sType = "New"
Case REPRICE_TYPE
sType = "Reprice"
Case PROD_TYPE
sType = "Products"
Case Else
MsgBox "There is a logic error in the Select Case" & vbCrLf & _
"statement in optSelect_BeforeUpdate( )."
End Select

Me!cboClientRequests.RowSource = "SELECT * " & _
"FROM qryClientRequests " & _
"WHERE [Request Type] = '" & sType & "';"
Me!cboClientRequests.Requery

Exit Sub

ErrHandler:

MsgBox "Error in optSelect_BeforeUpdate( ) in" & Me.Name & _
"form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

.. . . where optSelect is the name of the option group, cboClientRequests is
the name of the combo box, qryClientRequests is the name of the query that
displays the records in the combo box, and Request Type is the name of the
column which contains the type of request.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.