Prevent Duplicate Combinations of Fields in Microsoft Access

An AutoNumber primary key guarantees that every record has its own unique ID. It does not guarantee that the information in that record makes sense. You can still accidentally register the same employee for the same training session twice, add the same product to an order twice, or assign the same warehouse item to the same location more than once. The trick is to define which combination of fields must be unique.

In Access, the usual solution is a unique multi-field index, sometimes called a composite unique index. It lets each individual value repeat where it should, while preventing the specific combination of values that represents a duplicate. No VBA is required, and you do not have to get rid of your AutoNumber primary key.

Consider a registration table. You might have a RegistrationID AutoNumber field as the primary key, plus EmployeeID and SessionID fields. One employee can attend many sessions, so EmployeeID must be allowed to repeat. One session can have many employees, so SessionID must also be allowed to repeat.

What you do not want is the same EmployeeID and SessionID appearing together twice. If employee 7 is registered for session 101, that exact pair should only exist once. Employee 7 can still register for session 102, and employee 8 can still register for session 101. Those are valid combinations.

A common beginner mistake is setting EmployeeID to Indexed: Yes (No Duplicates). That certainly prevents duplicates, but it prevents far too much. Now each employee can only appear once in the entire table. Congratulations, your employees are allowed one training class for the rest of their careers. Probably not the business rule you had in mind.

The same problem occurs if you make SessionID unique by itself. You would only be able to register one employee in each session, which makes for a very quiet classroom.

Instead, leave your AutoNumber field as the primary key and create a separate index that includes both fields. Open the table in Design View, then click the Indexes button on the ribbon. Access will show the existing primary-key index and any other indexes already defined for the table.

On a new row, enter a meaningful index name, such as EmployeeSession. In the Field Name column on that same row, select EmployeeID. On the row directly underneath it, leave the Index Name column blank and select SessionID in the Field Name column. That blank index-name cell is important. It tells Access that SessionID belongs to the same index as EmployeeID, rather than starting a brand-new index.

With the first row of that index selected, set the Unique property to Yes. Save the table. From that point forward, Access will reject a record that repeats the same EmployeeID and SessionID pair.

When a user tries to enter the duplicate, Access will display its standard message indicating that the change would create duplicate values in an index, primary key, or relationship. It is not the friendliest message in the universe, but it does enforce the rule at the table level. That means the protection applies whether records are entered through a form, datasheet, query, import, or some other part of the database.

This is sometimes described as a composite key because the rule is made from multiple fields. A composite primary key is also possible, where the combined fields become the table's actual primary key. That can be a perfectly valid design in some databases. In many Access applications, however, keeping a simple AutoNumber primary key and adding a separate unique composite index is easier to work with, especially when other tables need to refer to one specific record.

The important part is deciding exactly what constitutes a duplicate in your application. Sometimes two fields are enough. Sometimes they are not. Suppose an employee can be assigned to the Navigation duty on Mission 501 and also to Navigation on Mission 502. A unique index on EmployeeID and DutyID would incorrectly block the second assignment. In that case, the real rule is the combination of EmployeeID, DutyID, and MissionID.

The same technique works with three or more fields. Give the index a name on the first row, add the first field, then place each additional participating field on the rows below it with the index name left blank. Set Unique to Yes, and Access will make sure that the complete combination is not repeated.

Before adding a unique index to an existing table, make a backup and work on a test copy if possible. If duplicate combinations already exist, Access will not be able to create the unique index until you clean them up. You may need to run a Find Duplicates query first, review the old records, and decide which ones should remain.

Also remember that uniqueness and required data are two different rules. If every registration must include both an employee and a session, set the participating foreign-key fields to Required: Yes as appropriate. Do not assume that making an index unique automatically means users must fill in every field.

Once the index is in place, test both sides of the rule. Try entering the same employee and same session twice. That should fail. Then try the same employee in a different session, and a different employee in the original session. Those should work normally. The goal is not to block everything. The goal is to block the mistake while allowing legitimate work to continue.

The embedded video walks through the Indexes window in Access and shows the setup in action. Once you understand unique multi-field indexes, they become one of those simple little database tools that can save you from a lot of cleanup later.

Live long and prosper,
RR

Build Shared VBA Libraries, Reusable Forms & Reports for Multiple Access Databases

If you maintain more than one Access database, you have probably copied the same VBA function, utility form, or report from one file to another. It works... until you improve something and suddenly have six old versions floating around. That is how duplicated code turns into a maintenance nightmare, usually right when you have better things to do.

Access Developer 63 is all about avoiding that mess by building a shared library database. Instead of treating every Access application as its own little island, you can create one database that holds common code, forms, and reports, then make multiple applications use those shared objects.

The starting point is shared VBA. A library database can contain common routines that you use across several applications: formatting functions, validation tools, logging routines, user-interface helpers, and all those little procedures you seem to rebuild over and over again because "this will only take five minutes." Sure it will.

Once the library is connected properly, your other Access databases can call the shared routines rather than maintaining their own copies. When you fix a bug or add an improvement, you update the code in the library. The databases that rely on it can benefit from that update without you having to manually paste revised code into every project.

Of course, shared libraries come with a few things to understand before you turn your entire application collection into one big web of references. The class covers how Access references library databases, what can cause a reference to become broken, and why file locations matter. A perfectly good library is not much help if Access cannot find it anymore because somebody moved it into a mysterious folder named "Old Stuff Do Not Delete."

We also look at what happens when the shared library is already open or being used by another database. That matters in real-world multi-user and multi-application environments, especially when you need to deploy updates. Shared code is wonderful, but it needs to be managed deliberately.

The really interesting part is that a library database does not have to contain only VBA modules. You can place forms and reports in the library too. That means a customer lookup form, a settings window, a notification form, or another common interface can live in one place and be opened from different Access applications.

Think about the maintenance benefit. Instead of having a dozen nearly identical customer forms scattered across several databases, you can maintain one shared form. Change its layout, improve its validation, or fix an annoying little issue once, and the applications using that form can all take advantage of the update.

Sharing forms raises some good questions, naturally. What data does the form use? Can it work with tables stored in a different database? How do you open it to a specific record? Can the calling application work with controls on the shared form? Developer 63 walks through those practical details and the little Access quirks that tend to appear once you begin crossing project boundaries.

The same idea applies to reports. If multiple databases need the same invoice layout, customer summary, notification report, or other common output, a shared report can eliminate duplicate design work. The class demonstrates how reports can be stored in the library and used by other databases, including how information can be passed between the application and the shared object.

This is not necessarily something every beginner database needs on day one. But if you build Access applications for clients, maintain several internal systems, or have a collection of databases that all use the same business rules and interface components, shared libraries can save a tremendous amount of time. More importantly, they help keep your applications consistent.

By the end of Access Developer 63, you will understand how to organize reusable VBA code, forms, and reports into a shared Access library and connect multiple databases to it safely. You will also have a better handle on the reference and deployment issues that can otherwise make shared-library projects frustrating.

Watch the embedded video for an overview of what is covered, and visit the course page for the complete hands-on training and demonstrations.

Live long and prosper,
RR

How to Return Multiple Values from a Function in Microsoft Access VBA

Sometimes one little customer lookup turns into a whole pile of values you need in your code: address, city, state, ZIP code, country, phone number, whatever. You can certainly run a separate DLookup for every field, and for a quick one-time job that is perfectly fine. But once you need the same lookup in several forms or procedures, copying that logic everywhere gets messy fast.

A handy VBA pattern is to let a Function return one clear success or failure value, while using ByRef parameters to send several related values back to the procedure that called it. Think of the Function as requesting a customer information packet. The Boolean result tells you whether the packet arrived, and the ByRef variables contain the address pieces inside it.

A VBA Function technically has one normal return value. In this situation, that return value is best used as a Boolean. Your Function can return True when it successfully finds the customer and fills in the requested information, or False if the customer cannot be found.

The additional values are passed into the Function using parameters declared ByRef. ByRef means "by reference." Instead of giving the Function a copy of a variable, you give it access to the actual variable from the calling procedure. If the Function changes that variable, the calling code sees the changed value as soon as the Function is finished.

So conceptually, you might have a Function named GetCustomerInfo. It receives a CustomerID and several String variables for Address, City, State, ZIP, and Country. The Function looks up the customer record, fills those variables, and returns True if it found a matching record.

That means the calling code can do something like this: declare a few local String variables, call the Function, test the Boolean result, and then place the returned values into the controls on the form. You get one clean call instead of five separate DLookups scattered around the database like sticky notes on the coffee maker.

The lookup routine itself should start by clearing the output variables and setting its own return value to False. This is important. If the lookup fails, you do not want old values hanging around in those variables and making it look like the lookup worked when it did not. Nothing like showing the last customer's address on a new order to make everybody have a bad day.

Inside the Function, a DAO recordset is a good choice when you need several fields from the same record. Open a recordset for the selected CustomerID, check whether it contains a record, and then copy the address fields into the ByRef output variables. Use Nz when appropriate so Null values from the table do not cause problems when assigning text values.

If a record is found, set the Function result to True. If no record is found, leave the output strings blank and return False. The calling procedure can then decide what to do. On an Order form, you might populate the shipping address fields when the lookup succeeds, or display a "Customer not found" message if it fails.

One little detail worth mentioning: do not name your local variables exactly the same as the controls or fields on your form if it makes the code confusing. Instead of using names like Address and City for everything, use something like AddressString, CityString, StateString, and ZipString for the local variables. Then it is obvious when you are working with a variable versus a form control.

This approach is especially useful when the same customer information is needed in more than one place. Maybe you need it on an Order form, a Quote form, a shipping label routine, and a button that creates a packing slip. Put the lookup logic in one public Function in a standard module, and every part of the database can use the same routine.

If the Customer table changes later, you update one Function instead of trying to remember where you copied and pasted those five DLookups three years ago. Future-you will appreciate it. Present-you might not remember writing it, but future-you will still appreciate it.

Are multiple DLookups wrong? Nope. Not at all. If you have a simple one-off task and only need a field or two, DLookup is often quick and easy. The ByRef pattern is not about declaring war on DLookup. It is about keeping related work together when the same lookup is used repeatedly.

You could also use global variables or TempVars to share information across your Access application. Those have their place, especially when values truly need to be available across forms, reports, macros, and procedures. But they are shared state, which means another procedure can change them, overwrite them, or leave old values behind.

ByRef output parameters are more self-contained. The inputs, outputs, and success test are all visible right in the Function call. That makes the code easier to read, easier to debug, and less likely to turn into a mystery later on.

The basic pattern is simple: use the Function's direct return value for success or failure, use ByRef parameters for the related data, initialize everything safely, and keep the lookup logic in one reusable place. It is a useful little VBA Lego to keep in your toolbox.

The embedded video includes the full Access walkthrough, including building the recordset lookup, creating the public Function, and calling it from an Order form.

Live long and prosper,
RR

Does Microsoft Access Save Form Edits When You Move to Another Record? Video Quiz B1.10

Ready for a quick Microsoft Access forms quiz? These five questions cover some of the little details that trip up beginners all the time: labels versus field names, hiding sensitive information, form views, saving edits, and continuous forms. Keep score if you want. No cape required.

Try to answer each question before reading the answer. If you miss one, no worries. That is why we practice this stuff before Lex Luthor gets access to the customer table.

Question 1: You change the label beside a text box from "FirstName" to "First Name." What changes?

A. The underlying table field is renamed
B. Only the label displayed to the user changes
C. Access creates a second First Name field
D. The text box becomes unbound

Answer: B. Only the label displayed to the user changes.

The label is just the caption the user sees on the form. Changing it can make your form more readable without changing the actual field name stored in the table. Your field can remain FirstName, while the form politely displays First Name. That is usually a good thing. Friendly captions on forms, sensible field names in tables. Everybody wins.

Question 2: Staff members need to enter customer information, but they should not see customers' credit limits. What is the best form design choice?

A. Delete the CreditLimit field from the table
B. Change CreditLimit to an AutoNumber field
C. Leave CreditLimit off the staff form
D. Put CreditLimit in the form header

Answer: C. Leave CreditLimit off the staff form.

Do not delete useful data from your table just because one group of users should not see it. Keep the CreditLimit field in the table, but simply do not put a control for it on the staff form. You can build a separate management form for users who are allowed to view or edit that information.

One important real-world note: leaving a field off a form is good interface design, but it is not ironclad security by itself. If users can open tables, queries, or other forms that expose the field, they may still be able to see it. Still, for normal day-to-day form design, keeping irrelevant or sensitive fields off the form is exactly the right move.

Question 3: After saving and reopening a bound form, which view is normally used to enter and edit its records?

A. Form View
B. Layout View
C. Design View
D. Print Preview

Answer: A. Form View.

Form View is where normal users work with the data. It is where they enter customers, update addresses, select values from combo boxes, and generally do their daily database stuff.

Layout View and Design View are for changing the form itself. That is where you move controls around, resize text boxes, change colors, add buttons, and make the form look less like something built during the Clinton administration. Print Preview is for viewing printed output, not editing records.

Question 4: A user edits a record on a bound form, and the pencil icon appears. What normally happens when the user moves to another record?

A. The edit is discarded automatically
B. Access saves the edit only when Ctrl+S is pressed
C. The form becomes read-only until reopened
D. Access saves the changed record to its underlying table

Answer: D. Access saves the changed record to its underlying table.

This is one of the most important beginner concepts in Access. The pencil icon indicates that the current record is dirty, meaning it has been changed but has not yet been committed to the underlying table.

When you move to another record, Access normally saves the current record automatically. Closing the form will normally save it too. You do not have to press Ctrl+S to save the data record. Ctrl+S is primarily for saving changes to the form design, not for saving edits someone made to a customer's phone number.

If the data fails validation, violates a required-field rule, or otherwise cannot be saved, Access will stop you and display an error. But under normal circumstances, moving off the record commits the changes.

Question 5: You want to display many records at once, with the same custom set of controls repeated for every record. Which type of form is designed for that?

A. A single-record form
B. A continuous form
C. A split form
D. A modal dialog form

Answer: B. A continuous form.

A continuous form repeats its Detail section for each record. Think of it as a list of records where each row uses the controls and formatting you designed on the form. It is great when you want to see lots of customers, contacts, orders, or other records at the same time without being stuck with the plain datasheet look.

Access also has a Multiple Items form option in the Form Wizard, which generally creates this kind of continuous-form layout. A single form shows one record at a time, while a continuous form gives you that repeated record display.

So, how did you do? If you got all five, congratulations, Metropolis is safe and your records are probably in decent shape. If you missed a few, these are all core concepts covered in Access Beginner Level 1, Lesson 10, where we build a customer form and dig into how bound forms actually behave.

Watch the embedded video for the quiz and explanations, and remember: when you move off a dirty record on a bound form, Access normally saves that edit to the underlying table. No Ctrl+S panic required.

Live long and prosper,
RR

How to Create a Navigation Form in Microsoft Access

When someone opens your Access database and immediately sees a giant list of tables, queries, forms, and reports, they may not know where to start. Worse yet, they may start opening things they should probably leave alone. A Navigation Form gives your database a proper front door: one clean screen where users can choose the task they need and get to work.

Microsoft Access Navigation Forms are one of the quickest ways to build a simple menu system without writing VBA code. They are built into Access, mostly drag and drop, and they work well for small databases or beginner projects where you want to make the database feel more like an application and less like a filing cabinet full of mysterious parts.

A Navigation Form is essentially a container form with a navigation control and a large display area. The navigation control provides tabs or menu items, and when the user clicks one, Access displays the selected form or report inside the main area of the Navigation Form.

Think of it like the table of contents in a book. Your users do not need to know where every chapter is stored or how the book was assembled. They just choose Customers, Orders, Reports, or whatever makes sense for their job, and Access takes them there.

To create one, go to the Create tab in Access. In the Forms section, click Navigation. Access will give you several different layout choices, including horizontal tabs, vertical tabs on the left or right, and two-level navigation layouts.

If you only have a few choices with short names, horizontal tabs across the top can work nicely. If you have longer labels or a larger number of items, vertical tabs may be easier to read. There is no universal best choice. Pick the layout that helps your users find what they need without making them stop and think too hard. If your menu starts looking like a packet of Chiclets, it is probably time to organize it a little better.

One of the more practical layouts is Vertical Tabs, Left. This gives you a menu area down the left side and a large workspace on the right. It is a familiar arrangement for most users, especially if you have forms with names that are longer than a couple of words.

Once the Navigation Form is open in Layout View, adding forms and reports is easy. Find the form or report you want in the Navigation Pane, then click and drag it onto the area labeled Add New in the navigation control. Access creates a navigation item for it automatically.

For example, you might drag in a Customer Form, a Contact Form, an Order Form, and a few reports. Each one becomes a selectable navigation item. Click Customer Form, and the customer form appears in the main display area. Click Contact Form, and that form replaces it.

You can also rearrange the navigation items by dragging them into a different order. This is a good time to think like your users instead of like the person who built the database. Put the things they use every day near the top. Put administrative tools, maintenance forms, and less common reports farther down the menu.

Do not be afraid to rename navigation items so they make sense to normal human beings. Your underlying object might be named something like frmCustomerList, which is fine for you, but your staff probably does not need to see that. A friendly label such as Customers or Customer List is much better.

If your database is getting larger, Access also offers two-level Navigation Forms. These let you create broad categories across the top and place related forms or reports underneath each category.

For instance, you could create a Customer Stuff category with Customer Form, Contact Form, Customer Letters, and Customer History beneath it. A separate Manager Stuff category could contain reports, order history, employee tools, or administrative forms. This can prevent a large database from turning into one long, cluttered list of tabs.

Be a little careful when dragging objects into a two-level layout. You need to drop the form or report in the correct lower navigation area for the category you want. It is easy to drop it in the wrong place and wonder why it ended up somewhere strange. Access is helpful, but it cannot read your mind. At least not yet.

After you save the Navigation Form, you can open it like any other form. Better yet, you can make it your startup form so it opens automatically when the database starts. That way, users begin with the menu instead of being dropped into the Navigation Pane with all of your tables, queries, forms, reports, and other plumbing exposed.

Keep one important point in mind: a Navigation Form is not security. Hiding the Navigation Pane and giving users a pretty menu does not stop a determined user from getting into your database objects if they have the appropriate permissions and know what they are doing. It is user-interface cleanup, not a security system. Hiding the kitchen door does not make the restaurant secure.

Navigation Forms are excellent for getting a simple interface up and running quickly. They are particularly good for beginners, small business databases, and projects where you want a clear starting point without investing time in VBA or custom menu design.

However, they do have limitations. The forms displayed in a Navigation Form are hosted inside the navigation control. From an advanced development standpoint, that means they behave somewhat like subforms. If you later start writing VBA code that refers to controls on those forms, the reference paths can become more complicated than they would be with ordinary standalone forms.

That is one reason I often prefer custom menu forms for larger or more advanced databases. A blank form with command buttons gives you much more control over the appearance, behavior, automation, and form interactions in your application. But that does not mean a custom menu is automatically better for every situation. Sometimes the simple built-in tool is exactly the right tool.

If you are building your first database interface, start with a Navigation Form. Get your forms and reports organized, make the labels friendly, and give your users one obvious place to begin. You can always replace it with a custom menu later if the database grows into something more complicated.

Watch the embedded video for a full walkthrough of creating Navigation Forms, adding objects with drag and drop, rearranging navigation items, and setting up a two-level menu layout.

Live long and prosper,
RR

How To Make Property Sheet Text Easier To Read With Larger Font in Microsoft Access

If you spend much time designing forms and reports in Microsoft Access, you have probably noticed that the Property Sheet text can be ridiculously small. On a high-resolution monitor, the old default font size can make reading property names and values feel like an eye exam. Fortunately, newer versions of Access finally include a setting to make that text larger.

This is a simple change, but it can make a huge difference if you are working in Design View all day. It is especially helpful when you are adjusting lots of control properties, working with detailed forms, or, like many of us, discovering that your eyes are not quite as young as they used to be.

To change the Property Sheet font size, open any Access database and go to File > Options. In the Access Options window, select Object Designers from the menu on the left.

Look for the setting labeled Property Sheet Font Size. Access used to use a very small default size, commonly 8 point. Click the drop-down box and select a larger size. I find 12 point to be a comfortable choice, but pick whatever works best for your monitor, screen resolution, and eyesight.

After selecting your preferred size, click OK. Access will let you know that you need to close and reopen the database before the change takes effect. Go ahead and do that. When you open the database again and switch to Form Design View or Report Design View, bring up the Property Sheet and you should see the larger text immediately.

This setting affects the Property Sheet itself, including the property names and the values you edit. It does not make your form controls larger, change the fonts on printed reports, or magically improve the rest of the Access interface. It is specifically there to make the design-time Property Sheet easier to read.

One thing Access still could use is proper zooming in Design View. Access now has useful zoom options when viewing forms, and Ctrl plus the mouse wheel can be very handy there. But when you are placing controls precisely in Design View, especially on a large high-resolution display, it would be nice to have that same kind of zoom control. Maybe someday. We can dream.

Until then, Windows includes a built-in workaround: Magnifier. Press the Windows key and the plus key to turn it on and zoom in. Press the Windows key and the minus key to zoom back out. This is useful when you need to line up controls, resize something by a few pixels, or read a tiny bit of text without changing your Access settings permanently.

If you use Windows Magnifier often, consider assigning those keyboard shortcuts to a programmable keypad or Stream Deck. It is not required, of course, but having one button to zoom in and another to zoom out is a lot easier than remembering keyboard combinations while you are trying to nudge a textbox into the exact right spot.

The larger Property Sheet font setting is one of those small quality-of-life improvements that can make working in Access much less frustrating. If you have been squinting at 8-point property text for years, give 12 point a try. Your eyes will thank you.

Watch the embedded video for a quick visual walkthrough of the setting and a demonstration of Windows Magnifier in action.

Live long and prosper,
RR

Which Combo Box Column Gets Selected Commission Rate in Microsoft Access? Video Quiz D2.3

Combo boxes are one of those Access controls that seem simple right up until you need to pull a value from a column that is not the bound column. Then suddenly you are staring at Column(3), wondering whether Access counts like a normal human being. Spoiler alert: it does not. Combo box columns are zero-based.

This Developer Level quiz covers five useful VBA and form-validation concepts: retrieving the correct combo box column, converting text to numbers, distinguishing Null from zero, opening a combo box when validation fails, and returning validation results from a reusable function. Give yourself a few seconds to answer each question before checking the answer.

Question 1: A combo box Row Source contains ID, FirstName, LastName, and CommissionRate, in that order. Which expression retrieves the CommissionRate from the selected row?

A. cboEmployee.Column(4)
B. cboEmployee.Column(2)
C. cboEmployee.Column(3)
D. cboEmployee.Column("CommissionRate")

Answer: C. cboEmployee.Column(3)

Access combo box columns start counting at zero. That means ID is Column(0), FirstName is Column(1), LastName is Column(2), and CommissionRate is Column(3). The fourth field is therefore Column(3). This catches almost everybody at least once, usually while they are wondering why they are getting a last name instead of a commission percentage.

Question 2: A numeric value from a combo box column is being treated as text. Which VBA function can commonly convert a numeric string into a number before performing calculations?

A. Val()
B. Format()
C. CStr()
D. MsgBox()

Answer: A. Val()

The Val() function takes the numeric portion of a text value and converts it into a number. That can be helpful when a combo box column gives you something that looks like a number but VBA insists on treating it as text. On the other hand, Format() and CStr() are generally used when you want to create text, which is exactly the opposite direction from where you are trying to go.

Question 3: Why should validation code test whether a numeric input is Null instead of automatically rejecting zero?

A. Zero cannot be stored in a numeric Access control.
B. Null means no value was entered, while zero may be a legitimate entered value.
C. Null and zero always mean the same thing in VBA.
D. Testing for zero prevents the control from receiving focus.

Answer: B. Null means no value was entered, while zero may be a legitimate entered value.

Null means no value has been supplied. Zero is an actual numeric value. Whether zero is valid depends on your business rules. A quantity of zero, a discount of zero percent, or even a commission rate of zero may be perfectly acceptable. Your validation routine should reject missing information when information is required, not reject a valid number just because it happens to be zero.

Question 4: VBA needs to open a combo box drop-down after detecting that the user has not selected a value. What should the code normally do before calling the combo box's DropDown method?

A. Save the current record with DoCmd.RunCommand.
B. Requery the combo box Row Source.
C. Set focus to the combo box.
D. Change the combo box Bound Column to zero.

Answer: C. Set focus to the combo box.

A control generally needs to have the focus before VBA can use certain methods on it, including DropDown. So the usual logic is to move the focus to the combo box and then open its list. That gives the user a helpful little nudge toward fixing the problem instead of merely throwing a message at them and leaving them to hunt around the form.

Question 5: A form has several required controls that must be validated before calculating a result. Which design best lets one reusable VBA routine tell the calling code whether every validation test passed?

A. Use a Sub procedure that displays messages but returns nothing.
B. Use a Function As Boolean that returns False when a test fails and True only after all tests pass.
C. Put every validation rule in a table Validation Rule property.
D. Use On Error Resume Next and calculate the result anyway.

Answer: B. Use a Function As Boolean.

A Boolean validation function gives the rest of your code a simple yes-or-no answer. If a required field is missing, the function can display the appropriate message, set focus to the control, and return False. If every check passes, it returns True. Then your calculation code can continue only when validation succeeds. It keeps your forms cleaner, makes the validation routine reusable, and prevents your button-click event from turning into a 200-line bowl of spaghetti.

If you missed any of these, do not put yourself on trial for crimes against VBA. These are all common Access development issues, especially when you start using combo boxes for lookup values and calculations. Watch the embedded video for the quiz and a quick review of each answer.

Live long and prosper,
RR