Hi,

I have form where I have put a combo box and an action button. This form is based on a query.

The combo box and action buttons are made to be able to filter the records. So what I would like to have is that when the user selects an entry in the combo box and press the action button, only the records are shown where the department field on the form equals the combo box selected value.

I have tried to find a way to make this, but I can't seem to find it...

The combo box name is cboFilter, the action button name is cmdFilter and the field on the form where the filter should apply to is txtDepartment.

I'll explain again. The user is working on this form and only want's the have the data shown where the txtDepartment is equal to a certain value. It should then choose a department in the combo box cboFilter and press the action button cmdFilter. After pressing the action button, only the records needs to be shown where the cboFilter equals the txtDepartment.

How do I make this work?

Also, is there a way to add a 'All departments' on the first line in the cboFilter combo box? If the user selects this one, it removes the filter and shows every record again...

Do you want a button to be pressed prior to the filter? I'd normally just put the code in the afterupdate routine of the combo box.

Is txtDepartment pulling from a different table than the bound recordset is or are you changing the record the form itself is on?

I'll assume that you want to change the recordsource of the form itself based on the chosen value.
Therefore you probably want it to limit the recordset based on the field assigned to txtDepartment.

Assuming txtDepartment is also the field name in the table:
------------------------------------------------------------------------------------------------
'Rename MyTable to the table name of the bound table and MyForm to the name of your form
dim sQuery as string
sQuery = "Select * from MyTable"
If cboFilter.value <> "<All Records>" and cboFilter.value <> "" _
  Then sQuery = sQuery & " Where txtDepartment = """ & cboFilter.Value & """"
'NOTE:Change txtDepartment above to the actual field name of the table.
Forms("MyForm").RecordSource = sQuery
------------------------------------------------------------------------------------------------

As for adding <All Records> to the combo box, set the combo box RecordSource property to:
Select Distinct txtDepartment From MyTable Union Select "<All Records>" As txtDepartment from MyTable Order By txtDepartment

Since I put All Records between <> it should sort to the top. Replace MyTable with the source table name and txtDepartment with the actual field name from the table.


I hope this is what you needed or at least helps.

allenm,

thanks for your reply.

Adding the extra line in the combo box works fine.

However, I still have a little problem with the code you gave me. The code I have now on the after update event of the combo box is:

Private Sub cboFilterAfdeling_AfterUpdate()

Dim sQuery As String

sQuery = "SELECT tblOpvolgingTT.Datum, tblOpvolgingTT.Naam, tblOpvolgingTT.Afdeling, tblOpvolgingTT.TelNr, tblOpvolgingTT.NummerTT, tblOpvolgingTT.CodeTT, tblOpvolgingTT.[Gebeld(1)], tblOpvolgingTT.[Gebeld(2)], tblOpvolgingTT.[Gebeld(3)] FROM tblOpvolgingTT WHERE (((tblOpvolgingTT.CodeTT) Is Null Or (tblOpvolgingTT.CodeTT) = 'Onvoldoende ingevuld')) ORDER BY tblOpvolgingTT.Datum, tblOpvolgingTT.Afdeling, tblOpvolgingTT.NummerTT;"

If Me.cboFilterAfdeling.Value <> "<Alles>" And Me.cboFilterAfdeling.Value <> "" Then
    sQuery = sQuery & " Where tblOpvolgingTT.Afdeling = """ & cboFilterAfdeling.Value & """"
    Me.RecordSource = sQuery
Else
    Me.RecordSource = "SELECT tblOpvolgingTT.Datum, tblOpvolgingTT.Naam, tblOpvolgingTT.Afdeling, tblOpvolgingTT.TelNr, tblOpvolgingTT.NummerTT, tblOpvolgingTT.CodeTT, tblOpvolgingTT.[Gebeld(1)], tblOpvolgingTT.[Gebeld(2)], tblOpvolgingTT.[Gebeld(3)] FROM tblOpvolgingTT WHERE (((tblOpvolgingTT.CodeTT) Is Null Or (tblOpvolgingTT.CodeTT) = 'Onvoldoende ingevuld')) ORDER BY tblOpvolgingTT.Datum, tblOpvolgingTT.Afdeling, tblOpvolgingTT.NummerTT;"
End If

End Sub

When I open the form everything is ok. The form opens with the underlying query, but when I change the value in the combo box, I get an error on the line:

    Me.RecordSource = sQuery

I tried to use the forms("FormName").recordsource, but this is the same error...

Any idea if I forgot something?

Your ORDER BY section HAS to come at the end of your query. You should only need the one recordsource too:
--------------------------------------------------------------------------------------------
Dim sQuery As String

sQuery = "SELECT tblOpvolgingTT.Datum, tblOpvolgingTT.Naam, tblOpvolgingTT.Afdeling, tblOpvolgingTT.TelNr, tblOpvolgingTT.NummerTT, tblOpvolgingTT.CodeTT, tblOpvolgingTT.[Gebeld(1)], tblOpvolgingTT.[Gebeld(2)], tblOpvolgingTT.[Gebeld(3)] FROM tblOpvolgingTT WHERE (((tblOpvolgingTT.CodeTT) Is Null Or (tblOpvolgingTT.CodeTT) = 'Onvoldoende ingevuld')) ORDER BY tblOpvolgingTT.Datum, tblOpvolgingTT.Afdeling, tblOpvolgingTT.NummerTT"

If Me.cboFilterAfdeling.Value <> "<Alles>" And Me.cboFilterAfdeling.Value <> "" _
  Then sQuery = sQuery & " Where tblOpvolgingTT.Afdeling = """ & cboFilterAfdeling.Value & """"

sQuery = sQuery & " Order By tblOpvolgingTT.Datum, tblOpvolgingTT.Afdeling, tblOpvolgingTT.NummerTT;"

Me.RecordSource = sQuery
--------------------------------------------------------------------------------------------

If the query in your string is in error you will get an error when you try to set a recordsource property to it.