Use filters on a subform like in excel.

[tacieslik]

Hello, I want to use a filtering system like in excel on a sub form. Is it possible?

 

[charityg]

Yes it's possible using comboboxes.

Create comboboxes for each field in your subform. The rowsource for each combobox would be

"Select Distinct YourFieldName from YourTable"

This will add each unique value from the table to your combobox list.

In the after update event of the combo box, you need to change the recordsource for the subform to "Select * from YourTable where YourField='" & YourComboBox & "'"

Let me know if you don't understand this and I'll send you an example.

 

[charityg]

Here's an example DB

 

[tacieslik]

Hello charityg,

I don't know why I never replied to this, but thankyou anyway.
I treid to open the sample to sent, but it says that I need to upgrade Access. I'm running Access 2000 Pro. Is it possible to save it in an earlier version?

Thanks for your time.
Tim

 

[charityg]

Try this one

I converted the db from 2002 to 2000, hopefully this will work for you.

 

[tacieslik]

Thanks a lot. I'll have a look at it.

Tim

 

[tacieslik]

The sample is a great starting point. I need to be a bit cleverer though.
I have a table called tblHistory. I have a form called frmSearch and a sub form within called sfrmSearch. I have 4 combos on my main form namely:

cmbName
cmbDrug
cmbTimeA
cmbTimeB

I have a query that will display data based on cmbName OR cmbDrug And Between cmbTimeA and cmbTimeB.

Is it possible to only choose say cmbDrug and display all drugs and then if they use cmbTimeA & B limit the results further? At the moment, I have to choose all 3 combos for the query to work.

My query has the following criteria:

For Drugs:
[Forms]![frmSearchHistory]![cmbDrugsName]
AND
For Dispensed Time:
Between [Forms]![frmSearchHistory]![cmbTimeA] And [Forms]![frmSearchHistory]![cmbTimeB]

OR
For Name:
[Forms]![frmSearchHistory]![cmbPatientsName]

I wondered if it would be possible to use check boxes and run a query based on which check boxes were checked. Has anyone got any ideas?

TAC

 

[charityg]

I have an idea that I will work into the sample. I'll try to get it posted today for you to take a look at.

 

[charityg]

Try this one:

The user selects the Filter type (By Patient or By Drug)

If "by Drug" is selected, the user can then select a drugname.
The records are filtered by drug name, and the user then has the option of selecting a "from" date.

If the "from" date is selected the records are filtered to show only those with the drug name and a date greater than or equal to the "From date".

The user can then select a "To" date, which filters the records further.

 

[tacieslik]

Thank you very much charityg,

This helps me very much. Samples are always a great way to learn how to do new things. I'll keep you posted.

Regards,
TAC

 

[charityg]

Your welcome.

~Charity