where clause in a row source query for combo box

0
votes

I'm trying to create a combo box whose row source is a query. I'd like to filter the results of the query based on certain value either in one of the control's on the form (which could change - based on the current record) or a value that is fixed (the username using the application)

Say I want to show only items the user is allowed to see

or

I want to list tasks for the project currently selected.

I have used the where clause as: where cmb_name.colname = me.control this is not working.

I tried inserting this where clause in the query in the combo box's row source as well as inside the query itself that is called by the combo box - DOES NOT work either way.

What is the best practice?

2
comboboxms-access-2010

2 Answers

1
votes

I would move this from a Query to VBA Code. Using the Form current method, you will be able to do this. 

Private Sub Form_Current()
    Me.comboBoxName.RowSource = "SELECT theFieldA, theFieldB FROM theTable " & _
                                "WHERE theConditionFieldName = '" & Me.yourControlName & "'"

    'If the Me.yourcontrolName is a number value, please remove the single quotes in the above statement.

    Me.comboBoxName.ReQuery
End Sub

This will give more control of the choices.

0
votes

Set the form's Filter property to colname = cmb_name, assuming cmb_name is the combobox and colname is the column to filter on. (You don't need a WHERE keyword for filters.)