Filter records using selection from a combo box

[SteveL]

I have a form and the form's control source is "tblProjects". By default the records are shown in ascending order by "ProjectID" from the table. But I'd like to add a combo box to the form which when the user clicks the drop down arrow, "ProjectOwner" from the table appears. I know how to create the combo box. But what I want to happen is for the records to be shown are ONLY the records that the selected ProjectOwner has. I also want to provide an "ALL" choice in the drop-down and then have the records appear again for all records sorted by "ProjectID" as they are when the form is first opened. How can I do this?

 

[Kiwiman]

Your record source for the combo box will need to be something like:

Select tblProjectOwner.ProjectOwner,tblProjectOwner.ProjectOwner from tblProjectOwner; UNION Select "*","(All)" from tblProjectOwner

Your column count will be two, and the widths 0 and 2.5 (eg)

The part of the union stmt inserts a wildcard and (All) - this is just to put the all at the top of the combo selection.

Where tblProjectOwner holds your projectowners??

Then an expression on the after update of your combobox like the below

dim strSQL as string

strSql = Select tblProjects.* from tblProjects
strSQL = strSQL & "Where tblProjects,ProjectOwner like '" & me.yourcombobox & "'"

me.recordsource = strSQL
me.requery