Filtering by Combo Box

[SteveC24]

Hi,

I know this has probably been asked a million times before, but I have had a look around, and cannot find the answer I am after.

I have a form holding equipment information - one of the fields is Type (as in type of equipment).

I would like to be able to filter the data shown in the form so that only 1 particular Type of equipment is shown.

I cannot work about how to do it. I would prefer to keep from using 2 forms, and just have the user select the value required from a combo box at the top of the form.

Any help greatly appreciated!

 

[Markvand]

I have very similar problem, let me know if you find something interesting,

Cheers

 

[dcx693]

There are many examples of search forms on the boards, but it sounds like you've already looked.

I also keep an equipment database. What I do is put a combo box into my form header with the desired choices. Then I attach code to the combo box's After Update event. Something like this:

 Me.RecordSource = "SELECT * FROM qryEquipment WHERE qryEquipment.Type='" & Me.cboShow & "';"

I almost always put an <<All>> choice at the top of my combo boxes so the user can easily choose to display all the entries, not just those of a particular type. The code becomes:

Dim strWhere As String

    If Me.cboShow = "<<All>>" Then
        strWhere = "WHERE True;"
    Else
        strWhere = "WHERE qryEquipment.Type='" & Me.cboShow & "';"
    End If
    
    Me.RecordSource = "SELECT * FROM qryEquipment " & strWhere

Instead of setting Me.Recordsource, you could also use Me.Filter="your filter text" to set a filter, then Me.FilterOn=True to activate it.

 

[SteveC24]

Thank you very much! I will test it out tomorrow, and let you know how it goes!

Just one thing, will I still be able to have the combo box fed off another table? My reason being that I will provide a list of default equipment types, but I would like the user to be able to add additional ones if they require.

Thanks

 

[dcx693]

Just one thing, will I still be able to have the combo box fed off another table? My reason being that I will provide a list of default equipment types, but I would like the user to be able to add additional ones if they require.

I assume you mean the combo box in the form detail section. The combo box should feed directly off a table of equipment types, and you can set the combo box to accept new entries. After a new entry had been added you should requery the combo box in the form header so that it will now include all valid entries in the equipment types table.