Looking for Form Filtering advice

[eepok]

[NOTE: I'm no programmer]

Hey, long time no see. The info I've received from these boards have been able to last me a few months but now I have another request from my users.

I made a database a while back that stores all the courses my department's faculty teaches, when they teach them, if they take any extra compensation for going over courseload, etc. I have input a good deal of the archived information on hand so I have about 8 academic years of information with about 35 faculty per year. The information will only grow and I need a way to filter out particular records quickly.

A couple of the fields in my form are [AcademicYear] and [Instructor]. I would like to be able to filter the form by these two fields. Even just AcademicYear would be nice as I fear I may be treading into "cascading combo boxes" territory which I am just not ready to get into.

Button, combo box, list box -- my users know these things. I know Access 2007 has a built in filtering function on the ribbon, but my users just can't get used to it.

 

[georgedwilkinson]

Sorry, I don't know Access 2007. But I'm wondering how much easier filtering a form could be than using Access' built in filtering...i.e. right click on the field and type in the filtering criteria in the box on the menu? Then right click and clear the filter when done.

Even my group of non-professors here can do that with little to no training.

 

[eepok]

Sorry, I don't know Access 2007. But I'm wondering how much easier filtering a form could be than using Access' built in filtering...i.e. right click on the field and type in the filtering criteria in the box on the menu? Then right click and clear the filter when done.

Even my group of non-professors here can do that with little to no training.

In my 6 months of experience with Access 2003/2007, I have not right-clicked on a field until you mentioned it. I think my staff can live with that. It's pretty darn simple.

Thanks!

 

[eepok]

Even my group of non-professors here can do that with little to no training.

And you'd be pretty surprised how helpless my actual professors can be.

No, you still have to dial 9 to get out. No, nothing's changed. Yes, even though it's only a fax machine.

 

[cardgunner]

What about if they are filtering a subform in the form?

It filters the subform yes. But how can you tell the program to filter the main form by the filtered records in the subform?

 

[georgedwilkinson]

You must write code.

Put the lookup boxes for the sub-form filtering on the main form and then supply new sql to the main form to perform the filtering for you.

strSQL = "select ...fields on my main form... where ...criteria = criteria from my lookup fields"
Me.RecordSource = strSQL

Obviously, you need to build strSQL first based on data the user has typed into your lookup fields.

It would be cool if there were a better way to do this. Any suggestions?

 

[cardgunner]

Do I need to add look up boxes?

I have a earlier post on this and I didn' get an answer.

http://www.access-programmers.co.uk/forums/showthread.php?t=145843

There is no way of finding the records he selected while in the filter by form state and use that in your select statement somehow?

 

[georgedwilkinson]

Sorry I didn't answer right away, I've been doing a rather large data conversion.

Yes, you need to add lookup boxes on your custom filter for the form. I think I figured out how to do this by looking up "Access custom subform filter" in Google and taking one of the many fine examples there.

I have a working database that has applied this concept but it was built for a very large company (one we all know and love!) and is highly confidential. If you need it, I'll see if I can pull out part of it and post it, though I'm pretty sure you can find the answer more quickly.