Goal: I have two tables: 1.) Customers, 2.) Orders. I want to make a form in MS Access where the user can filter (by combobox) on the "Customer" and sees all orders of that customer in table form. When a new product is entered into the Order form, the row needs to auto populate/update the CustomerId in the Order table which was filtered by the combobox.
Real database design: Customers (5 records) and Orders table (160 records) are linked tables from a SQL Server. In the Orders table, I have only placed the CustomerID not the name, since I want to maintain the Customer table on its own and not change all records. By query in Access I search the Customer name so I can use it in the form.
Problem: When I use an unbound combobox of the table Customers the form is really slow. When I remove the join (Customers.CustomerId on Orders.CustomerId) from the query to retreive the customer name, the form works fast enough.
Question: what would be the best way to design such a simple form without losing performance. I have created a demo databases with local tables, where the performance is perfect. However, when I change it to linked sql tables performance is decreasing.
SampleAccessFile: 
Search for LastName : _______________
{hit enter, or tab...match list is displayed}
Search for FirstName: _______________
The above approach is good for the typical small files you have with ms-access applications. By small, I mean in the few hundred thousand range.
But, with sql server, then even 1 million rows works fantastic.
We are going to search for a person named Jonah Smith. You can see above that the user only typed in Smi (the first 3 characters). However, with our good search screen that is all he needs to type. The user then hit Enter. You can see how there are 20 matches. While this file has 30 thousands names, the only reason why we see only 20 names is that the default radio button of "this season" restricts the search to people who only took a trip this tour season. Now the user will probably type in jo in a attempt to match Jonah. Hence, the next screen show two important things.
- The matches that now starts with jo is now displayed.
- The cursor is now in the pick list. The user is free to use the arrow keys and hit Enter
so, we now have this:
At this point the user can use the down arrow once, and hit Enter key to bring up our name of Jonah Smith. I of course DO accommodate the mouse users. The little set of glasses icon REALLY SHOULD be a non graphical with the simple text of View/Edit button. While a View/Edit text button is better from a user learning and training point of view, I simply like the glasses and it is my program! Since I use the little glasses virtually everywhere in my application to bring up details, then the user only has to click on them once to discover and learn what they accomplish. In addition to the user hitting the Enter key, or clicking on the little glasses, you can also double click anywhere on the detail line. This double click is again often used and seen in applications, and thus I threw that in also.
You will also notice that there is a Add button. That means if the name you are looking for is not found, then you can easily add a new record. Since the "Add grouping" button is also Hot keyed, then Alt-A key will add a new record. This means that users can still search, and edit, or add names 100% via the keyboard.
ALSO VERY important?
We talking about human engineering here. (user habits) If you make the search screen VERY easy to use?
Then users by habit will search FIRST!!!
I seen so many systems, where searching is a pain - users don't bother, and now you have boatloads of duplication in the system - and its the developer fault, since they made searching too hard, and thus users just enter the name or customer or whatever again!!!!
So make your search screen FRONT and center. And better yet?
Well, it not only makes your software a joy to use, but ALSO encourages users to search first.
After all, users can't do ANY work until they found the record to work on then right???
Even better?
This setup encourages good work flow. You find what you need to work on. You edit, do your work, and THEN CLOSE the form!!!
That means if 2 minutes later they phone the office back, then ANY operator of your software can now search and bring up their name.
If you load up a combo box, and other users on the system JUST added new information, then that combo box now does not have those new records!
But then again, why would you load a huge massive list into a combo box? No one would do that, right??? Not only is such a setup slow, it also not multi-user friendly.
and by having users ALWAYS by habit close the form with the ONE main record? Then you encourage them to close the form, which also means they just saved their edits. And when they close that form, they return right back to the search screen ready to do battle with the next customer or task at hand.
So, in summary:
Don't load a huge list of choices into a combo box. It is too slow, consumes too much network bandwidth, is not user friendly, and also does not really give your applcation a nice work flow.
be it using web based search, a accounting package or anything?
provide a search form, then users can use the resulting pick list to pick a customer.
On that search form, I have that one row button to click on that launches the ONE form to the ONE record.
It is simple, and does this:
docmd.OpenForm "frmBooking",,,"ID = " & ME!ID
And now the access form loads ONE main record, and displays say this:
It really don't matter how large the database is, since the ONE main form record is filter to ONE record, and that going to load fast no matter how many rows in the system.
So, save world suffering, and save your computer bandwidth. Don't EVER load up a huge combo box list - they are too slow, not work flow friendly at all.
A combo box works for about 100 rows, and after that, you just beating your users with a club and going to cause pain and suffering.
build a great search form. Not only will users love you, your performance of the applcation will be the same with 2000 rows, or 2 million rows.
And the search code for above?
It looks like this:
Dim myconds as string
Dim MySQL as string
myconds = ""
If Len(Nz(txtSFirstName, "")) > 0 Then
myconds = myconds & " FirstName like '" & txtSFirstName & "*'"
End if
If Len(txtSLastName) > 0 Then
If myconds <> "" Then myconds = myconds & " and "
myconds = myconds & "( tblMainClient.LastName like '" & txtSLastName & "*') "
End if
MySQL = "SELECT * FROM qryMainClient WHERE " & _
myconds & " ORDER BY LastName"
Me.tblMainClient_subform.Form.RecordSource = MySql
So, we simple shove the sql string into the sub form in that search page and display the results (our pick list).