Here is a demo I made using the different techniques we've been discussing for people to review or reference.
Some notes:
- The main point was to limit the rows returned during a search. I used CJ London's technique of originally basing the form on a query that returns no records, and then the recordsource gets updates when a name is selected from the search combo box. The add button simply resets the recordsource to the null recordsource
- By default, the form opens to a new record. You can have it open to the record of the client accessed most recently. I demonstrate this with a button on the main form. What happens is when you access a client, it adds it to a table. That way you can load up any client.
- I have two subforms on the main form. There is one on the client's main screen that gets its recordset loaded when a client is accessed. There is another on on a tab that gets loaded dynamically when you change to the tab. This is a demonstration of how to use tabs to further reduce how much data gets loaded during navigation
- Assuming tens of thousands of records, I rigged the combo box to only load the list once a character is entered. You can easily change this to only load after 2, 3, etc characters.
I did a bit of a twist on this. I was thinking that if a user clicked the arrow to drop down the list and saw nothing, they would probably (knowing my clients) go into panic mode. So I loaded it up with JUST the records they expect to see - clients with the last name starting with A. Even though odds are low (1/26 or so) that they happen to want a client with last name A, and even if they do they aren't going to scroll, I figured it was worth the small performance compromise to give them what they expect to see and hope they don't scroll past letter A
Anyway, anyone wanting to see if to comment or use some of the ideas in there is welcome.
SHADOW