Just for my understanding how would you need to work with recordest if there are more tables involved as tblCountry to pick a Country within the form from a combobox. And likewise with Salution.
You use a query that joins the source tables to produce a recordset that has all the data in a single list. OR, since you are writing VBA, you can open multiple recordsets, each based on a single table and somehow with VBA sort and match the separate recordsets to get all the data you need to come together to make your combo/listbox Rowsource entry.
As I mentioned earlier, Access is a RAD tool. That means it does stuff for you that many other environments don't do. One example is having a bound RowSource for these two controls which is not available in many other environments. If they have combo and listbox controls, your code always needs to load them at runtime rather than the Access method of specifiying a table or query name and letting Access do the heavy lifting. What Access does is provide a simple way to provide a table driven solution for your small lists. I can tell you that in 25+ years of using Access, I have never had an occasion to load a combo manually as you are doing. I have had occasion to load listboxes manually though. Listboxes are sometimes used to collect an intermediate set of data. For example, a user wants to send an email to a small group of clients but there is no criteria he can use that would allow a query to do the job. He just needs to select the clients manually, one at a time. The source is typically a combo or other listbox that includes the universe of options. The user scrolls through and clicks on each client and in the click event, the VBA copies the data from the combo/listbox 1 and adds it using the AddItem method to listbox 2. I can't envision a reason to ever do this from a Recordset. If you can make a recordset to populate a RowSource, why would you not just use the query as the RowSource? I get the objective of learning how something works but try to make your task at least meaningful and potentially useful. Because of this, I didn't like the video.
Another possibility is you want to use calculations and generate the data. This calculation might be something like an amortization schedule so you can see the payment amount, interest, and loan balance each month of a 5 year loan. Typically, I would save this to a table because using it then becomes more flexible but if you only wanted to show the data in a listbox and not save it, then your VBA loop could load the listbox as it loops and calculates each month.
You seem to be thinking that it is somehow more efficient to do things with VBA but that is not the case at all. It is ALWAYS better to use a query to process a recordset and avoid VBA loops unless absolutely necessary. SQL Server is so much better at this stuff and the Access environment is optimized to load combos/listboxes as efficiently as possible. You doing it via VBA is the slowest possible method and obviously shouldn't be used unless you cannot create a recordset but have to generate the data using calculations on the fly. So, that goes to your unbound forms also. The only situation where I would even consider using an unbound form is if I had a remote database with a poor connection which caused bound forms to be too slow. But, given that most of my apps use SQL Server or other RDBMS, I have learned other ways to optimize my bound forms. The primary one is to severely limit the number of records a bound form ever shows. The old school Access method for Jet/ACE is to bind your forms to naked tables and load ALL the rows into the RecordSource. Then the user uses the built-in form filtering methods to filter the recordset locally. Well, this is ok for a 5,000 row recordset but not even close for a million row recordset. How many records is the user actually interested in working with anyway? The answer is - one at a time. So, the best option is to let the user specify which record he wants before you open the form. Or, open the form to no record and search from the form itself. This minimizes your network traffic and allows SQL Server to do the heavy lifting of searching through the database to select only the records you want to work with NOW.
Since you are looking at combos and how they are populated, I've included a link to what I call a mini-app. I developed this concept 40+ years ago when working with COBOL and VSAM recordsets as a way to avoid hard-coding value lists in my programs. This was well before RDBMS' became the database of choice and it was way too much trouble to make individual files to manage dozens of small lists. These objects can be added to your applications. Change the color scheme to match your own so they don't stand out like sore thumbs. You can even implement rudimentary security so that you can control whether or not ordinary users are allowed to add/change list items. I use three options IT, Admin, AllUsers. If any list requires special programming, users can't touch the list so only your IT person who is going to write the code to support the new item can manipulate some lists. I find that most users are simply too sloppy to allow them to add/change values in a list (none can be deleted) and so I usually limit the majority of the lists to Admins. Occasionally, you have a list that gets new entries frequently and those you just have to set to allow AllUsers and hope they can spell
When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...
www.access-programmers.co.uk
