Thread: Query/lookup problem
06-05-06, 07:04 #1Registered User
- Join Date
- Apr 2003
Unanswered: Query/lookup problem
This is driving me up the wall. I'm using Access 97 on Windows XP.
I have a form FormPatents based on a table TableFiles. TableFiles contains a field called FileClient which is a lookup to a table called TableClients, which includes a ClientID and ClientCode. FileClient stores ClientID and displays ClientCode. This field is used as a control on FormPatents. The default filter on the form sorts the records in ascending order by, among other things, ClientCode - the advanced filter/sort window includes a link to a query based on TableClients. So far so good.
When I try to apply a filter using the Filter by Form, if I put something in the FileClient control it works fine. If I don't, then I lose the link to TableClients in my advanced filter/sort window and I get an input box popping up asking for input in Lookup_clientcode.
How can I stop the filter losing the link to TableClients? Nothing else in the default filter is overwritten so I don't understand why this happens. (I hope this all makes sense, it's hard to explain sometimes!) I'm actually having a lot of problems with my filters so I think I'm missing some fairly fundamental piece of knowledge - I'm not an Access newbie but I haven't really worked with filters before.
06-05-06, 08:35 #2Registered User
- Join Date
- Jul 2004
- Blackburn, UK
Hi, firstly, i have not a clue what this all means (I understand Access but this is crazy!) Filters, Advanced Filters, argh!
If you have used VBA before i suggest that to do any sorting etc on your forms.
If you like you can up your db and ill take a glance at it, if i believe there will be a more effecient way of doing your forms then ill make the change and explain what i have done.
You are best to use queries to gather data for your forms for this reason, filters take all your database information and then play with it, if you use SQL it only grabs the information you need and then you can play with that. I know in the case of a sort this is different because they both have the same amount of information to work with but in the case of an actual filter i.e. just selecting one record then SQL queries or just a normal query would work better.
I would definately be better to see the forms though as its hard to get a picture of what you mean just by stating the form names and the controls on themBased on DAO 3.6 and Access 2000 + 2003
VB.Net 2005 + ADO.Net