Your users can pick a customer by name while Access stores the matching CustomerID. Put the ID first in the combo box's list, bind to column 1, and hide that column so the name is displayed instead. My Relational Combo video shows how to build this using a table or query and save the selection in a service record.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/1752.
Want people to copy a value from your Access form without changing it? Keep the text box enabled and set Locked to Yes. Disabling it prevents interaction through that control instead. My Gray Out Fields video explains the difference and shows how to disable fields based on another value in your form.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/2333.
Spent time designing a form? You don't have to start over just to show a different set of records. Change its Record Source from the table to a query, and keep the field names the form uses. My Record Source video shows how to switch a customer form to New York customers only.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/RecordSource.
Imported data has a special talent for looking perfectly reasonable right up until you try to use it. Names arrive with extra spaces and tabs. Customer notes contain email addresses buried in a wall of text. Dates show up in American, British, ISO, and written-out formats, sometimes all in the same field. And, of course, somebody eventually imports February 30th.
This is exactly the kind of mess regular expressions can help clean up. In Access Developer 66, we continue working with regular expressions and put them to use on real-world import problems. The goal is not just to find patterns in text, but to build reusable VBA tools that help you clean, extract, interpret, and validate imported information before it causes trouble in your database.
One of the first problems covered is extracting email addresses from large blocks of text. You may have imported customer notes, support messages, old emails, web form submissions, or comments from another system. Somewhere inside that text may be one email address, several addresses, or none at all.
Rather than trying to split the text apart with a pile of string functions and crossed fingers, a regular expression can identify text that follows the expected email-address pattern. The class shows how to turn that into a reusable VBA function that can pull out the addresses it finds.
It also handles a common detail that is easy to overlook: duplicates. If the same email address appears multiple times in a block of text, you generally do not want to return it three times just because someone copied and pasted a message chain. This is where the Scripting.Dictionary comes in handy. It provides a fast, practical way to keep track of values you have already seen, and it is useful for far more than email extraction.
Next comes cleanup of messy imported text. Extra spaces are annoying enough, but imported spreadsheets and copied text often include tabs, carriage returns, line feeds, non-printing characters, and other little gremlins that make records look fine until you try to search, sort, compare, or match them.
The class builds a simple text-cleanup function to normalize that kind of data. Instead of changing every record blindly, you will see how to use an Access query to preview the cleaned result first. That is an important habit. When you are dealing with customer names, addresses, notes, or anything else valuable, it is much better to inspect the proposed change before running an update query and discovering that your cleanup routine cleaned up something it should not have.
The biggest challenge in this lesson is date handling. Dates are one of those things that seem simple until data comes from multiple countries or older systems. A value such as 3/4/26 can be March 4th under the American month/day/year convention or April 3rd under the British day/month/year convention. Both interpretations may be valid. Neither one is automatically correct.
Developer 66 works through a reusable approach for recognizing several types of dates in text. That includes numeric American and British-style dates, ISO dates, and dates written with month names such as November 25th or abbreviated month names. The important part is that the system can interpret the numeric formats according to the convention you specify rather than making a wild guess and hoping your imported records survive the experience.
Recognizing something that looks like a date is only half the job. A regular expression can spot text shaped like a date, but it does not know whether November has 31 days or whether February 30th exists. Spoiler alert: it does not, no matter how badly a spreadsheet insists otherwise.
That is why the class also focuses on calendar validation. Once a possible date has been identified and interpreted, the VBA logic checks whether it represents a real date. This distinction matters whenever you are importing information into a database. You do not want malformed data quietly converted into something unintended, and you definitely do not want invalid date values treated as legitimate records.
By the end of the class, you will have a much better toolkit for dealing with imported text: extracting useful information from unstructured notes, eliminating duplicate values, cleaning up inconsistent whitespace and formatting, recognizing dates in multiple formats, and separating valid dates from nonsense.
If you work with imported data in Microsoft Access, especially data from different countries or legacy systems, these are skills you will use again and again. The embedded video provides an overview of the material, and the complete Access Developer 66 class includes the full VBA implementation, regular-expression patterns, demonstrations, and the handy cheat sheet so you do not have to memorize all of this stuff.
Live long and prosper,
RR
Default value surprises? Check the bound control on your form. Its Default Value can override the table setting, so two forms can start new records with different values. Changing a default won't rewrite existing records. Watch my video for the table, form, and VBA places to check.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/4276.
A long list of orders can hide the pattern you're looking for. A crosstab query puts states down the side, months across the top, and the sum of sales at each intersection. Watch my Crosstab Queries video to see how to build it with the Query Wizard.
For a complete step-by-step video tutorial on this topic, including additional examples and explanations, please visit https://599cd.com/2366.
Once you have your inventory tables set up, the next job is making sure everything stays connected properly. An item should belong to a subdivision, that subdivision should belong to an area, and that area should belong to a location. Otherwise, before long, you will have a database full of mystery items that apparently live nowhere. That may be fine for socks in the dryer, but it is not great for inventory management.
The goal is to turn a collection of tables into a useful inventory system. That means enforcing the hierarchy with relationships, creating an easy item search, and building a convenient data-entry form that lets you browse locations, areas, subdivisions, and items all in one place.
The first thing to do is create the relationships between the tables. In this example, the structure is simple: one Location can have many Areas, one Area can have many Subdivisions, and one Subdivision can have many Items.
In the Relationships window, connect each parent table's primary key to the matching foreign key in the child table. LocationID connects Location to Area. AreaID connects Area to Subdivision. SubdivisionID connects Subdivision to Item.
When Access asks whether you want to enforce referential integrity, the answer is generally yes. Referential integrity prevents orphan records. You cannot create an area that belongs to a nonexistent location, and you cannot add an item to a subdivision that does not exist. It keeps the hierarchy honest.
Without referential integrity, somebody could type a random SubdivisionID into an item record. Access would accept it, and you would eventually be left asking, "Where exactly is this item stored?" The database would have no answer because the item would point to a place that is not actually in your system.
You will also see options for cascade updates and cascade deletes. Cascade updates are rarely a concern when you use AutoNumbers for your primary keys, because those values should not normally change anyway. Cascade deletes are the dangerous one.
If cascade deletes are enabled and you delete a location, Access can delete all of the related areas, subdivisions, and items beneath it. That might sound convenient until someone deletes the wrong location. Suddenly the contents of an entire storage unit have vanished from your database. Poof. I generally recommend leaving cascade deletes off unless you have a very specific reason to use them and fully understand the consequences.
With the relationships in place, the next useful feature is an inventory search query. You want to be able to type part of an item's name, such as "cube" or "optim," and immediately see where it is stored.
Create a query that includes the four tables: Location, Area, Subdivision, and Item. Since the relationships are already defined, Access should display the joins automatically. Bring down the fields you need to identify the complete path: the location name, area name, subdivision name, and item name. Including the ID fields can be helpful too, especially while you are building and troubleshooting the database.
There is no need to bring every field from every table into the query. That just gives you duplicate foreign keys and a lot of visual clutter. This query is meant to answer one question: where is this thing?
To make it searchable, place a parameter criterion under the Item Name field. A plain parameter prompt requires an exact match, which is usually annoying. If you search for "Optimus" but the actual record says "Optimus Prime," you would get nothing back.
Instead, use a wildcard search with the Like operator and asterisks around the parameter value. In other words, the criterion should search for anything containing whatever text the user enters. Type "mop" and find your mop. Type "cube" and find the Rubik's Cube. Type part of a name when you cannot remember whether you called something "Phaser Type A," "Type A Phaser," or "That Star Trek Thing."
Save that query with a meaningful name such as Inventory Search Query. It becomes a quick lookup tool you can run any time somebody asks where an item is located. Access will prompt for the item name, and the results will show the entire storage path from the top-level location down to the item.
Of course, finding items is only half the battle. You also need an easy way to enter and browse the inventory. This is where nested subforms are especially handy.
The basic idea is to create a continuous form for each table. Start with an Item form, then place that Item form inside a Subdivision form. Place the Subdivision form inside an Area form. Finally, place the Area form inside a Location form. The result is a set of forms nested inside each other like little Russian nesting dolls.
Each continuous form only needs to show the fields relevant to that level. The Location form might show LocationID and LocationName. The Area form might show AreaID and AreaName. The Subdivision form might show SubdivisionID and SubdivisionName. The Item form can show ItemID, ItemName, and perhaps Notes.
Do not worry about displaying every foreign key on every form. The parent-child link between the forms handles that for you. When the Item form is placed inside the Subdivision form and linked by SubdivisionID, Access automatically shows only the items belonging to the current subdivision. That is the whole point.
When you add each subform control, make sure its Link Master Fields and Link Child Fields properties are set correctly. The Location form links to the Area form through LocationID. The Area form links to the Subdivision form through AreaID. The Subdivision form links to the Item form through SubdivisionID.
If those links are correct, clicking a location shows its areas. Selecting an area reveals its subdivisions. Selecting a subdivision reveals the items stored there. You can also add new records right in the appropriate level of the hierarchy.
One odd little Access quirk is that when you drop a subform onto a continuous form, Access may complain that a form containing a subform cannot use Continuous Forms view. This is one of those cases where Access is being a bit dramatic. After adding the subform, go back into the parent form's properties and set its Default View back to Continuous Forms. Yes, it works. Yes, Access could handle this more gracefully. No, it does not.
Because multiple continuous forms are nested together, form sizing takes a little trial and error. You have to decide how many child records you want visible at one time. A subdivision with five items needs more vertical space than one with a single item. Dynamic resizing is possible, but that gets into programming. For a basic system, pick reasonable sizes and keep the layout usable.
Color-coding the levels can also help. Give locations one color, areas another, subdivisions another, and items another. This is not required, of course, but when you are looking at several levels of nested forms, a little visual separation makes it much easier to see where one group ends and the next begins.
The finished form gives you a practical inventory browser and data-entry screen without writing any VBA code. You can open a location, see its areas, drill into a storage cabinet or shelf, and add items exactly where they belong. Then, when somebody asks where the item went, run the search query and get the answer immediately.
This is a solid foundation for a simple home, office, workshop, collection, or storage inventory database. From here, you can add dedicated inventory-entry forms, cascading combo boxes, barcode tracking, asset assignments, transaction history, and plenty more. But even this basic setup is already far more useful than a spreadsheet full of vague notes saying "probably in garage somewhere."
Watch the embedded video for the full walkthrough, including the relationship setup, wildcard search query, and the construction of the nested subforms.
Live long and prosper,
RR
