Computer tutorials, tips, and tricks to help you learn Microsoft Excel, Access, Word, and lots more. Visit our full site at www.ComputerLearningZone.com.
Remember when 2 MB of RAM could get you started? Microsoft Access 1.0 brought tables, queries, forms, reports, and Access Basic to a Windows desktop, with a 128 MB maximum MDB size. Take a look back at the original release, 34 years after the October 28, 1992 shipping date recorded on its commemorative plaque. What was your first version of Access?
When VBA adds a new record to your Access database, you will often want to do something with that exact record next. Maybe you need to open the new order, move a continuous form to the newly added row, create related child records, or simply put the user's cursor back where it belongs. The important question is: how do you know which AutoNumber ID your code just created?
The answer is simple: capture the new record's ID at the time you create it. Do not go looking for the highest ID afterward. That may seem to work during testing, especially when you are the only person using the database, but it is not reliable in a multi-user environment. Someone else can add a record between the time your code saves its record and the time you ask, "What's the latest one?" Congratulations, you just opened somebody else's order. Nothing crashes, which is exactly what makes this kind of bug so sneaky.
A common beginner approach is to use something like a maximum ID value, or sort the records descending and grab the first one. That only tells you which record currently has the largest value or appears first in that particular sort order. It does not prove that it is the record your code created.
Instead, have the routine that adds the record return the newly assigned AutoNumber value. In Access VBA, that normally means changing a procedure that adds a record from a Sub into a Function that returns a Long.
For example, suppose you have a function that adds an item to a FoodLog table. The function opens a recordset, uses AddNew, fills in the fields, and then saves the record with Update. In an Access table, the AutoNumber value is available while the record is being created. So, after calling AddNew, you can read the new record's ID field from the recordset and assign it to the function's return value.
The essential idea is tiny: your function returns something like RSLog!FoodLogID. The calling procedure stores that returned value in a variable, perhaps NewID As Long, and can then use that ID for whatever happens next.
In a continuous form, for example, you can use the ID to locate the correct row with the form's recordset. The logic is basically: find the record where FoodLogID equals the new ID you saved. Once Access is sitting on that record, you can set focus to the field you want, clear out a combo box for the next entry, or let the user continue working.
This is useful far beyond a food log. The same pattern works when you create a customer, invoice, order, appointment, payment, contact, support ticket, or any other record with an AutoNumber primary key. Add the record, keep its ID, and pass that ID to the next step. It is one of those small habits that makes Access applications much more dependable.
There is one important detail to keep straight: getting an ID is not the same thing as successfully saving the record. You still need to call Update to commit the new record. If your save fails because of validation rules, required fields, or some other error, do not proceed as though you have a valid new record ready to use.
Also, this particular timing behavior applies to native Access tables used through Access recordsets. Other back ends can behave differently. SQL Server, for example, generally does not hand you the identity value in exactly the same way or at exactly the same point in the process. If you later upsized your application, you would want to adjust the technique accordingly rather than assuming every database engine works like an Access table.
If you are new to VBA recordsets, do not let that scare you off. A recordset is simply VBA's way of working directly with table or query records in code. Once you understand how to open one, use AddNew, assign field values, capture the new key, and call Update, you have a very useful tool in your Access toolbox.
The full walkthrough in the embedded video demonstrates the technique in a Fitness database example, including returning the ID from a function and using it to find the exact newly created record on a form. The database may be tracking food, but the technique works just as well for customers, orders, and just about anything else you build.
Stop guessing which row is yours. Keep the ID when you create the record, and you will always know exactly where to go next.
Need the first or last day of a month? Let DateSerial do the work. Day 1 gives you the start of the month. Day 0 of the next month takes you back to the last day of this one, even in February. Watch the video for the query calculations and the previous and next month variations.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/2442.
What are you looking up, where is it stored, and which record do you want? DLookup brings back one value from a table or query. This example retrieves a sales rep's phone number using a unique ID. Watch the video to see how to display the phone number for the rep selected on your form.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/DLookup.
Ever join two tables and wonder where some of your customers went? An INNER JOIN only shows customers who have a matching contact. A LEFT JOIN keeps every customer, even when there's no related contact to display. Keep this side-by-side example handy, then watch my Outer Joins video to see how to change the join in Access and bring those missing customers back.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/OuterJoin.
Subforms are one of those Access features that seem simple until the wrong customer's contacts start appearing on the screen. Then suddenly Mission Control has a problem. This expert-level quiz covers five important points about working with subforms, including how parent and child records are linked together.
Give yourself a point for each answer you get right. If you want to play along properly, read each question, make your choice, and then check the answer below. No peeking. Access knows.
Question 1: What is the main purpose of a subform in Microsoft Access?
A. Display related records inside another form B. Store a backup copy of the main form's records C. Print the current record as a report D. Replace a table relationship
Answer: A. Display related records inside another form.
A subform is a form embedded inside a main form. It is commonly used when you have a one-to-many relationship and want to display the related records for whichever parent record is currently selected. For example, you might show a customer's contacts, an order's line items, or a student's class registrations right inside the main form.
A subform does not replace proper table relationships. It is simply a convenient interface for viewing and editing related data without making the user bounce between seventeen different forms like they are navigating a spaceship with a broken joystick.
Question 2: A user enters a new related record directly in an editable subform. What normally happens?
A. The entry exists only until the main form is closed B. Access creates a new table for the entry C. The related record is saved to the subform's underlying record source D. The entry is copied into the parent record
Answer: C. The related record is saved to the subform's underlying record source.
An editable subform is a live view of its underlying table or query. Assuming additions are allowed and the record source is updateable, entering a new row in the subform creates a real record in that source. It is not temporary, and Access does not magically create a new table every time someone types a phone number.
When the main form and subform are linked correctly, Access will also fill in the child foreign key for you. So, if you are viewing Customer ID 42 and add a new contact in the subform, that contact is automatically associated with Customer ID 42.
Question 3: Which setup correctly links a CustomerF main form to a ContactF subform so each customer shows only that customer's contacts?
A. Link Master Fields: CustomerID; Link Child Fields: CustomerID B. Link Master Fields: ContactID; Link Child Fields: CustomerID C. Link Master Fields: CustomerName; Link Child Fields: ContactDate D. Set both link properties to the subform's form name
Answer: A. Link Master Fields: CustomerID; Link Child Fields: CustomerID.
The Link Master Fields property identifies the field on the main form. The Link Child Fields property identifies the matching field in the subform's record source. Access compares those values and filters the subform so it displays only the records related to the current main-form record.
In a typical customer-and-contacts setup, CustomerID is the primary key in the customer table and the foreign key in the contacts table. The field names often match, but they do not have to. You could have CustomerID on the main form and ContactCustomerID in the subform's record source, as long as those are the fields that actually connect the records.
Question 4: You want to change the Link Master Fields and Link Child Fields settings. Which object should you select in Design View?
A. A text box inside the subform B. The subform control's border on the main form C. The main form's record selector D. The subform's detail section only
Answer: B. The subform control's border on the main form.
This one catches a lot of people. A subform has two separate layers. There is the actual form object that displays the related records, and then there is the subform control sitting on the main form that contains it.
The subform itself has properties such as Record Source, Allow Additions, navigation buttons, and so on. The subform control has properties related to how that form behaves inside the main form, including Link Master Fields and Link Child Fields. To change those link settings, open the main form in Design View and select the border of the subform control, not a control inside the subform.
Question 5: A subform uses a custom query that displays ContactDate and Notes, but the query does not include CustomerID. You want to link it to CustomerF by CustomerID. What should you do?
A. Add CustomerID to the subform query's record source, even if it is hidden on the form B. Change CustomerID in CustomerT to a Short Text field C. Add CustomerID only to the main form's Record Source D. Turn on cascade delete related records
Answer: A. Add CustomerID to the subform query's record source, even if it is hidden on the form.
The child link field has to be available in the subform's underlying record source. If your subform is based on a query, that query must include CustomerID so Access can compare it to the CustomerID from the main form. You do not have to put CustomerID in a visible text box for the user. It can remain hidden. Access just needs the field available behind the scenes.
This is a common issue with custom subform queries. Someone builds a nice clean query that shows only the fields they want users to see, then wonders why the link properties do not work. The fix is usually simple: include the foreign key in the query, even if it never appears on the form.
So, how did you do? Five out of five means you are ready to establish New Olympus and make Mars Earth's favorite neighbor. Three out of five means you probably got the rover home before the dust storm started. Zero out of five? Please do not unpack your suitcase outside the airlock.
If any of these questions gave you trouble, subforms and their link properties are covered in much more detail in my Microsoft Access Expert Level 3 class, especially Lesson 3. Watch the embedded video for the quiz walkthrough and demonstrations, then check out the full course when you are ready to get your related records under control.
One customer can take several courses, and each course can have several customers. That's where a junction table comes in. Give each customer-course pairing its own row, and store the enrollment and certification dates that belong to that pairing there. Watch my Many-to-Many video to see how two one-to-many relationships connect the whole thing in Access.
