Form Control-Filter (1 Viewer)

How can I set a form control to filter a record set to pre-defined filter. I created a query to do what I want, see it below.


I just don't know how to add this to a form control, such as list box or drop-box.


Any help I appreciate...



Archive

SELECT [Application Log 1].ID, [Application Log 1].Status, [Application Log 1].[Position Title], [Application Log 1].CompanyName
FROM [Application Log 1]
WHERE ((([Application Log 1].Status)="Archive"));

Application
SELECT [Application Log 1].ID, [Application Log 1].Status, [Application Log 1].[Position Title], [Application Log 1].CompanyName
FROM [Application Log 1]
WHERE ((([Application Log 1].Status)="Application"));

Follow-Up
SELECT [Application Log 1].ID, [Application Log 1].Status, [Application Log 1].[Position Title], [Application Log 1].CompanyName
FROM [Application Log 1]
WHERE ((([Application Log 1].Status)="Follow-Up"));

Hi. Have you tried plugging in those SQL statements in the RowSource property of the List or Combobox?

Tinker, there are bunches of ways to do this. First, let's look at your statements. In essence, they are exactly the same EXCEPT for the subject in the WHERE clause. Right? So the fastest way to do that MIGHT be to diddle with that statement.


Now, before I go there, I'm going to give you a bit of advice to make your life easier. This is not a criticism - it's a tip. You have only one table in the FROM clause, so there is no other place from which data can come. Therefore, you can shorten what you wrote like this:


Now, when you do this, you have only two things that need bracketing. If you were to change [Position Title] to be run together (like you did for company name), you could drop the brackets and use PositionTitle. And a similar action would work for the table name so that you don't need the brackets all the time. That makes you have to type fewer characters, leaves you with no less mnemonic value, and simplifies the complexity of what you still have to type.

OK, now let's look at how you would make this change. The syntax to reference the value of a control depends on the form name. So... if the form is called frmAppLog and you make a combo box that can have the three possible subjects as its values (and call that SelectedSubject), then


So you would make the combo box have a default value of one of the possible subjects so that it will come up normally. BUT if you want to change it, you simply pick something else than the default from the drop-down. When you do that, you can put code in the Selected_Subject.Click event to do a Me.Requery. And that will change the records selected to those with the newly selected subject.

Thank you one and all. I will start tinkering with those suggestions and post a response in a couple of days.


Regards,
Tinkerer...

Are you filtering a form or something else? As Doc_Man said there are lots of ways to do this. You can change the recordsource, use a parameter query, or filter the form.

If this is a form assume the recordsource is this without any filter

Then you could have code to filter the form

me.filter = "status = 'Application'"
me.filterOn = true

If your combobox Actually had the values "Application", "Archive", "Follow-Up" then in the combos after update you could have

me.filter = "status = '" & me.ComboName & "'"
me.filteron = true