Since I like teaching so much, here are some good little pieces of knowledge to have, if you don't already have them:
Recordsets
*** When you open a recordset in VBA code, looping cannot be done unless you run the following code on the recordset first:
There is no reason for this, except that it's another oversight by Microsoft that still hasn't been fixed. One of the very few things you can do however, without running the above code, is get the recordcount of the recordset by just opening it. That property is one of few that is correctly returned on open.Code:recordset.movelast recordset.movelast
*** If you want the actual record number from a recordset in vba code, you need to use:
Absoluteposition returns the record's index, not it's sequential numbering assignment. Recordset indexes have a base of 0.Code:recordset.absoluteposition + 1
*** You can do just about anything with recordsets and code. There's always a way to do what you want using these two tools. So it can always be a backup plan. Data can always be manipulated this way (unless other factors are preventing it).
Queries
*** Stacked queries are underrated. As a matter of fact 3 stacked queries that run mathematical operations on 4 joined tables, the largest one having more than 100,000 records, doesn't even increase a database file size by 100KB (tested on my sample databases). It shouldn't really though. Query sizes are simply equivalent to the object sizes. The data that they query has very little effect, if any, on this size of the object (that I know of). Stacked queries can save you time, and more importantly it can save your brain from having to think too much. Personally, I like to make it easy on myself whenever possible.
*** The "Unmatched Query" under the query wizard dialog finds records that are broken by a Left Join. You can use it to produce automatic results for you for any situation that's similar to finding Customers (parent table) that have no Orders (child table) records.
Macros
*** If you want to learn the code that runs when you ask for a macro action, highlight your macro name in the database window and click the "convert macros to visual basic" button in the ribbon/menus. Most macro actions end up being converted to vba one-liners, so it's a really easy way to learn what the program is doing for each macro that you've created (and an easy way to learn some coding).
Miscellaneous
*** There is no point in password-protecting an Access database (that I know of). There are tools on the web to recover passwords. Here is one site: http://www.nirsoft.net/utils/accesspv.html. That only recovers .mdb file passwords, but you can bet there have been updates to this by someone else that extracts the same information from .accdb files and/or the ACE database engine.
*** If you're having a difficult time tracking what queries pull from what tables (and other issues), check out the "Object Dependencies" button in the ribbon (possibly in the menus in pre-07 versions). This shows you a list of your data, where it comes from and what objects in your database depend on the sources of that data to execute themselves.
*** The "Database Documenter" button in the ribbon does some pretty useful things. It can basically give you a print out of object features in your database. It should be recognized as a useful tool.
Forms
*** Unlike stacked queries, forms are extremely overrated (IMO). Personally, if Access had no form objects I would be in heaven. These are the most tedious objects to work with and develop. There really is no way around it. You can't automate it (without many headaches) or speed up the process of developing them. The only other interface forms out there (that I know of) that are easier to work with are perhaps internet forms as they only require HTML knowledge to develop them, and they certainly don't have as many complications and trinkets accompanying them as Access forms do!
*** Developing your own forms does take a lot of time, and there are endless amounts of templates out there (designs). It might be worth the time to use someone else's ideas for your database instead of wasting time reinventing the wheel. Or even semi-reinventing the wheel.
Lookup Functions
*** These are extremely slow. Avoid them at all costs. I'm not really sure why functions like DLookup() are so slow but I would venture to guess that it's due to the fact that more resources are required by the database engine. For instance, DLookup() requires it to request a full domain (a dataset) and then find a specification in that domain. So that's like running a full query through the interface.