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

Build Multi-Select Combo Filters with AND/OR Logic in Microsoft Access

Wouldn't it be nice if your users could pick Florida, New York, and Ohio from one filter, choose a couple of last names from another, and then decide whether those filters should work together with AND logic or separately with OR logic? Access does not give you a true multi-select combo box out of the box, because apparently that would be too convenient. But you can build one.

This is the kind of feature that makes a database feel much more polished. Instead of forcing users to run several searches, type complicated criteria, or settle for filtering one value at a time, you can let them choose several values and apply all of those selections at once.

For example, imagine a customer list where someone wants to see every customer in Florida and New York. They select both states, click OK, and the form displays only matching records. Add another filter for last name, select Riker and Ross, and now you can decide whether the records must match both filter groups or either group.

That distinction matters. With AND logic, the result must satisfy every active filter. A customer would need to be in one of the selected states and have one of the selected last names. With OR logic, a record can match either group. It can be someone from Florida or New York, or someone named Riker or Ross.

The trick is not really making a standard combo box suddenly support multi-select behavior. Access combo boxes do not natively work that way. The solution is to create an interface that lets the user build a collection of selected values, then use VBA to turn those selections into filter criteria behind the scenes.

Each filter control represents a field you want to search, such as State, City, LastName, ProductCategory, Employee, or OrderStatus. The selected items are gathered by VBA and converted into a condition appropriate for that field. Multiple state selections become one condition, multiple last-name selections become another condition, and then the final filter combines those conditions using AND or OR logic.

The important part is that the criteria must be built carefully. Text values need to be treated as text, apostrophes inside data need to be handled properly, date values need date delimiters, and numeric values should not be wrapped in quotes. This is one of those areas where dynamic filtering can go from "wow, that worked beautifully" to "why is Access yelling at me?" if the criteria are not constructed correctly.

You also need to account for filters that have no selections. If the user has selected states but left the last-name filter empty, the state condition should be used by itself. Empty controls should not produce broken expressions, extra AND operators, or criteria that accidentally return no records. That is the sort of housekeeping VBA is very good at once you set up the logic properly.

Another nice benefit is that the same general technique can be reused all over an Access application. You are not limited to customer lists. You can use it for product searches, employee assignments, invoices by status, orders by category, cities within selected states, or just about any other field where users might reasonably want more than one choice.

In Access Developer 62, I build this type of multi-select filtering system from scratch. We work with the selected values in VBA, create the filter criteria dynamically, and combine multiple filter groups with AND and OR logic. The goal is not just to make one fancy customer filter, but to give you a technique you can adapt for your own databases.

If your users are constantly asking, "Can I pick more than one?" this is a feature worth adding to your toolbox. Watch the embedded video for a preview of how the finished filters behave, and visit the course page for the complete hands-on lesson and implementation details.

Live long and prosper,
RR

How to Scan VBA for Option Explicit and Create Multi-Select Filters in Microsoft Access

One misspelled VBA variable can waste an embarrassing amount of time. Access will happily create a brand-new Variant variable for you if Option Explicit is missing, and then you get to spend the afternoon wondering why your perfectly good code is acting like it was written by a caffeinated raccoon. Developer Level 62 focuses on finding and fixing that kind of problem across an entire database, along with building a very handy multi-select filtering interface for your forms.

This is a developer-level class for Access users who are already comfortable working with VBA and want some practical tools they can use in real databases. The projects are different on the surface, but they share a useful theme: programmatically inspecting, modifying, and improving the stuff that already exists in your Access application.

The first project is a VBA maintenance tool that scans your database for modules that are missing Option Explicit. That includes standard modules, form modules, and report modules. If you have inherited an older database, or you have been maintaining the same application for years, there is a good chance that some code has accumulated in places you have not looked at recently.

Option Explicit forces every variable to be declared before it can be used. Without it, a typo such as CustomerNmae instead of CustomerName may compile just fine. VBA assumes you intended to create a new variable, usually a Variant, and your code keeps running with bad data or unexpected behavior. Those are the bugs that make you stare at the screen for half an hour before realizing you transposed two letters.

Rather than opening every module by hand and checking declarations one at a time, the class shows how to inspect the VBA project programmatically. The goal is not merely to produce a report saying, "Yep, you've got problems." The tool can identify missing declarations and automatically correct them. That makes it especially useful as a cleanup utility before deploying an older database or handing it off to another developer.

We also take the idea a step further by standardizing both Option Compare and Option Explicit throughout the project. Option Compare can affect how text comparisons behave, so the objective is not to blindly overwrite whatever is there. The important part is preserving each module's existing comparison setting while making sure the declarations are consistently placed and formatted.

This leads into a very useful programming lesson: safely modifying a collection of items when your modification can change their position. Lines of code move when you insert or remove text. If you are scanning through a module line by line and changing the module as you go, you have to account for that movement or you can skip lines, process the wrong line, or otherwise create a mess. It is one of those little details that separates "it worked on my test module" from reliable developer tooling.

The second major project is completely different, but just as practical: creating multi-select filter combo boxes in Microsoft Access. Access does not provide a traditional multi-select combo box control. You can use a list box, of course, but sometimes you want the compact appearance and familiar behavior of a drop-down control.

The technique in this class uses a feature I normally discourage for relational data storage: multi-valued fields. In a properly normalized relational design, multi-valued fields are usually more trouble than they are worth. They complicate queries, reporting, imports, exports, and long-term maintenance. But for a temporary user-interface selection tool, they can be surprisingly useful.

Instead of storing business data in a multi-valued field, we use the field as a convenient way for the user to choose several filter values from a drop-down. Those selections can then be used to build criteria for a form filter. This gives users a much friendlier way to say, "Show me records from these three categories," without requiring a giant list box sitting on the form.

The class also covers combining more than one multi-select filter. That is where the technique becomes especially useful. You can let users select several values from one filter and several values from another, then decide whether the final result should use AND logic, OR logic, or a combination of both. For example, users might select multiple departments and multiple employee statuses, then filter the form based on the relationship between those selections.

The important point is that the filtering controls are for finding records, not for defining your database structure. Used that way, multi-select selections can make a busy form much easier for users to work with while keeping the actual data model sane. That's a compromise I can live with.

Developer Level 62 brings these ideas together into useful real-world skills: scanning and cleaning VBA modules, enforcing better coding standards, safely editing code through automation, and building a slick filtering interface that your users will actually appreciate. If you work with established Access databases, especially ones that have grown organically over the years, these are excellent tools to have in your toolbox.

Watch the embedded video for an overview of the projects and demonstrations of what the finished tools can do. When you're ready for the complete step-by-step training, visit the course page for Microsoft Access Developer Level 62.

Live long and prosper,
RR

Can a Microsoft Access Macro SetValue Use Back Style Text Values? Video Quiz A3.2

Time for a quick Access quiz. These five questions cover a handful of small but important details involving form properties, macros, control events, fonts, and section references. They are exactly the kinds of things that can trip you up when everything looks fine in the Property Sheet, but your macro suddenly decides to throw a tantrum.

Give yourself a few seconds for each question before reading the answer. No cheating. Well, very little cheating. If you get all five, congratulations: your Access skills are operating at peak desktop nirvana.

Question 1: When a macro changes a control's Height or Width property, what unit does Access use?

A. Screen pixels
B. Twips
C. Inches
D. Points

Answer: B. Twips. Access uses twips for measurements such as a control's Height, Width, Top, and Left properties. There are 1,440 twips in one inch. The Property Sheet may display measurements in inches depending on your settings, but the actual property value used in expressions and macros is typically in twips. If you are not sure what number to use, check the current property value and work from there.

Question 2: You want a notes text box to expand while the user is editing it and return to normal afterward. Which event setup is most appropriate?

A. Expand it in On Click and shrink it in On Double Click
B. Expand it in On Load and shrink it in On Close
C. Expand it in On Got Focus and shrink it in On Lost Focus
D. Expand it in Before Update and shrink it in After Update

Answer: C. Expand it in On Got Focus and shrink it in On Lost Focus. Got Focus fires when the user enters the control, whether they click in it or tab into it. Lost Focus fires when they move away. That makes these events perfect for a temporary zoom effect on a notes box or any other field where the user may need a little extra room to work.

Using Click is a common beginner mistake because it seems like it should work. But Click only catches mouse activity. A user who tabs into the control would miss it entirely. Got Focus handles both situations without making you build separate logic for each.

Question 3: Why is it usually best to use a common Windows or Office font when setting a control's Font Name in an Access application?

A. A missing custom font can produce unreliable or substituted results on another computer
B. Access can assign Font Name only to labels, not text boxes
C. Custom fonts can be used only in reports
D. Font names must always be numeric values

Answer: A. Access can request just about any installed font, but that font has to exist on the computer running your database. If it does not, Windows may substitute another font, and suddenly your carefully aligned form looks like it was assembled during a power outage.

For databases that will be shared with other users, stick with common fonts such as Calibri, Arial, Tahoma, or Times New Roman. Tahoma remains one of my personal favorites. It is clean, readable, and unlikely to disappear when your database gets copied to someone else's machine.

Question 4: A macro uses SetValue to change a text box's BackStyle. Why can assigning the text "Normal" cause an error?

A. BackStyle can be changed only in VBA
B. SetValue can modify only bound fields
C. BackStyle must be assigned a color expression such as RGB()
D. BackStyle uses a numeric property value rather than the displayed word

Answer: D. The Property Sheet often displays friendly labels, but not every property is actually stored as text. BackStyle is numeric: Normal is 1 and Transparent is 0. A macro using SetValue needs the underlying numeric value, not the word shown in the drop-down list.

This is one of those Access details worth remembering. The Property Sheet is designed for humans. Macro expressions have to work with the actual value Access expects. If a property setting looks like a word but refuses to cooperate in SetValue, check whether it is really an enumeration or numeric setting hiding behind a friendly label.

Question 5: In a macro, which reference targets the BackColor of the Detail section on an open form named CustomerF?

A. Forms!CustomerF.BackColor
B. Forms!CustomerF!Detail.BackColor
C. CustomerF!BackColor
D. Forms!Detail!CustomerF.BackColor

Answer: B. Forms!CustomerF!Detail.BackColor. You start with the Forms collection, identify the open form, then identify the Detail section, and finally specify the property you want to change. The visible background area of most forms is the Detail section, so that is usually the BackColor property you are looking for.

There are often multiple valid ways to refer to objects in Access, especially when you are working inside a form module or form-level macro. But when you need a fully qualified reference to an open form's Detail section, Forms!CustomerF!Detail.BackColor is the clear and reliable choice.

So, how did you do? If you missed a couple, do not worry. These are not huge concepts individually, but they are the little nuts and bolts that make Access forms and macros behave properly instead of acting like a Windows 95 machine trying to dial into the Internet.

These topics and plenty more are covered in Access Advanced Level 3, Lesson 2. Watch the embedded video for the quiz and explanations, then check out the course if you want to dig deeper into form properties, macros, events, and making your Access applications a lot more polished.

Live long and prosper,
RR