Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Location
    Tigard, Oregon USA
    Posts
    19

    Question Unanswered: Filtering a table so that only new records are availble in a form, or alternatives?

    Alrighty... I will do my best to explain this so I can get some input from you guys.

    I am re-working an existing, 9 year old Access DB for a medium sized roofing company, adding features that they've wanted for several years.

    Now that 9 years have passed, they have about 47,000 customer records in this particular table. Needless to say, it takes a little while for the drop-down combo boxes in the form to populate with those 47,000 records.

    They need access to those old records every once in a while, but they do not need access to them every time they're looking for a current job.

    So, I'm looking at either making a duplicate copy of the table and the form, and modifying the second form to pull from the duplicate table, so that they can have only the records after 47,000 come up in the main form. If they needed to access the older records, they could use the other form.

    OR, what I would hope would be a much simpler task, filtering the table so that everything after a pre-determined variable number (47,000 in this case) is accessible from the drop-down boxes in the form, but nothing before that, unless they accessed it differently.

    I'm a bit rusty on DB programming, but typing that, it occurred to me that I might be able to accomplish this in a query or two.

    Does anyone have any thoughts, advice, and/or code? I'd really like to get some input on this.

    Thanks,
    Brian

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The drop-down boxes could easily specify a Row Source that is an SQL that restricts the customer records to only show those with an ID over 47,000 ... but what happens when an old customer wants their roof or guttering done again? You might need a command in a menu or button that replaces that SQL to remove the filter... like a "show all data" command. Probably better than having two forms for the same purpose.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Agreed with ST, change the rowsource to be an SQL statement, although I advise a TOP query isntead of "where id > 47K"
    Code:
    SELECT TOP 100 col1, col2, ... , colN
    FROM   suppliers
    ORDER BY date_created DESC
    George
    Home | Blog

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Wink Combo box with thousands of rows

    Allen Browne provides an excellent solution to this issue:-

    http://allenbrowne.com/ser-32.html
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Mar 2008
    Posts
    19
    First of all, I love your name. If you are using a query to limit the records they are viewing, you could use another query with the same criteria to fill the dropdown, except choose the unique records property for the dropdown's query. Another way that is more tedious: I did a similar thing where I didn't want all the customers from the old databse in the dropdown, only those still relevant. So I added a yes/no field where I checked the legacy customers to keep in the drop, then all new customers are defaulted to Yes. Not very slick, but the old data I was given had many duplicates spelled differently etc.

  6. #6
    Join Date
    Mar 2004
    Location
    Tigard, Oregon USA
    Posts
    19
    Wow, some great ideas here... Guess that will keep me busy for a day or two Thanks for all the input. I made the RowSource function work for me, but I have no idea how to make change the RowSource value from code... I am thinking about maybe a checkbox next to the look-up combo boxes, which, when selected, would trigger code that changes the criteria for field name "Lead ID"... I put in ">45000" in the criteria field, and it did exactly what I wanted it to... but I don't know how to do that through code.

    Any gurus with ideas?? Thank you

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    In code, generally speaking, you can just use something like

    Me.<comboboxname>.RowSource = "SQLString"

    But personally, I wouldn't be using a checkbox.

    Quote Originally Posted by garethdart
    Allen Browne provides an excellent solution to this issue...
    I don't like that solution. If the user doesn't know how to spell the item in question then it can become quite annoying, but it's definitely an option to consider
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Allen Browne Technique

    Quote Originally Posted by StarTrekker
    If the user doesn't know how to spell the item in question* then it can become quite annoying, but it's definitely an option to consider
    Certainly one consideration, however works very well in some environments;

    Some advantages are;

    Form can load without ANY recordsource to the combo - not pulling data through unnecessarily - until user types first few characters of customer account / name - Then only likely relavant data is accessed.

    ALL customers (new or old) are always available.

    Another tequnique you could employ to counter the above* is to use a customer search form - I will post details for this when im back in work tomorrow, but essentially a popup "Customer Search" form showing the main customer account fields (Acc number, co. name, address, post/zip-code, tel. no etc.).

    You can change the filter behaviour on this form so that during the on-filter event, the record source is changed to display customers based on what the user has filtered for - i.e. they could right-click > filter co. name for *forums* then recordsource then changes to "SELECT * from tblMyCustomers WHERE tblMyCustomers.strCustomerName Like '*forums*' this would return any customers with 'forums' in the Company name and allow user to identify correct customer account. Again - only relevant information is queried and which can make a great difference in performance particuarly if there are a lot of concurrant users and a large number of rows in the combo. (they can also search on ANY field)

    As I say, I will post link / code for the above when Im back in work tomorrow.

    Take it easy - hope you are having fun playing with the various ideas!
    Last edited by garethdart; 03-30-08 at 09:11.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    Mar 2004
    Location
    Tigard, Oregon USA
    Posts
    19
    Once again, great ideas... I think I've found a good solution though. My customer seems to like it. I have a checkbox at the top of the "Look-up" section, that says "Hide first 45000 records". It's checked by default, and if they need to look up something older, they can uncheck it, which changes the criteria of the RowSource SQL string. They can also just click on the nice little button I made which does a specific Find, searching through all records.

    I like the idea on filtering based on user input, but sometimes they just want to scroll through and find what they're looking for. But I've filed that info away for the next project.

    I think I'm good on this for now. Thanks for all the help everyone!

    Brian

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    At the end of the day, the customer is happy and that's the main thing
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What happens when there are 90000 records? Same performance issues..?
    I'd be more tempted to put something like "Show top 2000 only" ticked as default; but that's because I'm picky
    George
    Home | Blog

  12. #12
    Join Date
    Mar 2004
    Location
    Tigard, Oregon USA
    Posts
    19
    That is a good point and good idea... however, a) How will I make money when they need me to come manually change it to 90,000?? , and b) what would I need to change the criteria to, in order to show the last 2000 records?

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed with George.

    Point A is disturbing. I believe I'd make more money by getting more word of mouth work by providing solutions that DON'T have deliberate time bombs in them!

    The TOP keyword can be added to an SQL statement:

    SELECT TOP 2000 ....

    You can also do this in Design View by using the Top Values Property.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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