Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    59

    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.

    Thanks, Katherine

  2. #2
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    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 them
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •