Microsoft Access Error 9 Subscript Out of Range Causes and Fixes

Run-time error 9, "Subscript out of range," is one of those VBA errors that sounds more intimidating than it really is. In plain English, your code asked for something that does not exist. Usually it is an array element, but it can also be a collection item or a key that is missing. It is like reaching for the sixth donut when the box only had five. Disappointing, yes, but at least the problem is fairly straightforward once you know where to look.

A subscript is simply the index or key VBA uses to identify one item in a group. If you have an array with five positions, numbered 1 through 5, then asking for item 3 is perfectly fine. Asking for item 6 is not. VBA cannot retrieve, read, or write an item that was never created, so it stops the code and gives you error 9.

Arrays are probably the most common place Access developers run into this error. An array lets you store related values under one variable name. Instead of separate variables like Color1, Color2, and Color3, you might use one array called Colors with several indexed positions.

For example, if an array is declared with indexes from 1 to 3, the valid positions are Colors(1), Colors(2), and Colors(3). The first valid index is called the lower bound, and the last valid index is called the upper bound. Anything outside those boundaries is out of range.

So if your array runs from 1 through 5, X(5) is valid. X(6) is too high and causes error 9. But do not forget about the other end of the range. X(0) is also invalid because it falls below the lower bound. A lot of people assume their loop counter got too large, and sometimes it did. But it can just as easily be zero, negative one, or some other value below the first valid position.

One of the most common beginner traps involves VBA's Array() function. Arrays created with Array() are normally zero-based. That means an array containing Red, Green, and Blue uses indexes 0, 1, and 2. It has three values, but its highest valid index is 2, not 3.

This is where the classic off-by-one error comes in. You see three items and write a loop that runs from 0 to 3. It works for indexes 0, 1, and 2, then makes one extra trip when the counter reaches 3. At that point, VBA is looking for a fourth item that does not exist. Boom. Error 9.

The best habit you can develop is to stop guessing where an array starts and ends. VBA gives you two functions that do the work for you: LBound returns the lower bound, and UBound returns the upper bound. When looping through an array, use those functions instead of hard-coding values such as 0 to 2 or 1 to 10.

A loop based on LBound(MyArray) To UBound(MyArray) automatically processes every valid item in the array, regardless of whether it starts at zero or one. It also keeps working if you add more values later. Today you may have three colors. Tomorrow you add purple and orange. Let VBA figure out the boundaries instead of trusting your memory, which is usually the least reliable variable in the whole project.

When error 9 appears, click Debug. VBA will normally highlight the exact line that failed. That highlighted line is your best clue. Look for the array index, collection index, or key being requested. Then compare that value with the valid range.

If you are working with an array, the Immediate Window is very handy for this. You can inspect the lower bound, upper bound, and current loop counter to see what is happening. If the array's valid range is 0 through 2 but your loop variable is already 3, you have found the smoking gun.

Dynamic arrays can cause a similar problem if they have not been dimensioned yet. Collections can also produce error 9 when you request an item number or key that is not present. The investigation is still basically the same: what did your code ask for, and does that item actually exist?

One thing error 9 is not is a good excuse to slap On Error Resume Next at the top of the procedure and hope for the best. That is like putting duct tape over the check-engine light. The error may disappear, but the bug is still sitting there waiting to cause trouble somewhere else. Find the bad request and fix the logic that generated it.

So the short version is this: error 9 means VBA was asked for an item outside the available range. Check both the lower and upper bounds, remember that Array() normally starts at zero, and use LBound and UBound so your loops adapt automatically when the array changes.

Once you get used to comparing the requested index with the actual valid range, "Subscript out of range" becomes one of the easier VBA errors to diagnose. Watch the embedded video for the full walkthrough and demonstrations in the VBA editor.

Live long and prosper,
RR

Highlight All Matching Words for a Search in a Text Box in Microsoft Access

Access conditional formatting is great when you want to flag an entire field that contains your search text. But if you are dealing with a long Notes field full of paragraphs, highlighting the whole box is not especially helpful. You still have to play detective and hunt through a wall of text to find the one word you wanted. A better solution is to highlight the actual matching word or phrase inside the text box.

The trick is to display the text in a Rich Text text box and add a little HTML formatting around each match. Your original Notes field remains untouched. Access simply creates a highlighted display version of the text for the current record, which is exactly what we want for a search tool.

Start with your normal long-text Notes field. It can be plain text, which is actually the easiest situation for this technique. Then create an additional unbound text box on your form to display the search result. Set the new text box's Text Format property to Rich Text. This is the box that will show your notes with the matching words highlighted.

You will also need an unbound search box where the user can type the word or phrase they want to find, plus a Search button. Keeping the display box unbound is important. If you bind it directly to your Notes field and start inserting formatting tags, you risk changing the data in your table. We are not doing that. The whole point is to make a temporary, highlighted version for display only.

Rich Text controls in Access use HTML-like markup behind the scenes. If you manually highlight a word in a Rich Text box and then look at that same value in a plain-text control, you can see the formatting tags Access inserted. Those tags include a background-color setting and a closing font tag. That is the formatting we can add ourselves with VBA.

The VBA logic is pleasantly simple: take the contents of the original Notes field, use the Replace function to locate the search text, and replace each occurrence with the same search text wrapped in the Rich Text highlighting tags. Then assign that resulting value to the unbound Rich Text display box.

In plain English, the code says: "Find whatever is in my Search box, and replace it with that same text surrounded by yellow-highlight formatting." Since the Replace function handles every occurrence, all matching words are highlighted at once. Search for "Florida," and every Florida gets highlighted. Search for a short word like "he," and Access will find every occurrence of those letters too, including ones inside larger words. That may be useful or annoying depending on what you are trying to find, so choose your search terms accordingly.

One little VBA gotcha is that the Rich Text formatting contains quotation marks. In a VBA string, a literal quotation mark has to be written as two quotation marks. Yes, it looks ridiculous at first. Yes, everybody gets it wrong occasionally. The full implementation is demonstrated in the embedded video, including how to build the Replace expression without turning your code into a punctuation crime scene.

Because the matching text is being replaced with the value typed into the Search box, the displayed capitalization can follow the user's search entry. For example, if the original text says "Spock" and the user searches for "spock," the highlighted display version may show the searched-for capitalization. That is usually fine for a search display because the source Notes field is not being modified.

This works especially well when your stored notes are plain text. It can also work with Rich Text source data, but there is an important caveat. If the original Notes field already contains HTML formatting, and your search phrase crosses an existing formatting boundary, the simple Replace approach can break the markup or fail to find the text as expected. For example, if part of a phrase is already bold, colored, or highlighted, the HTML tags may be sitting in the middle of the words you are trying to locate.

Could that be handled? Sure. You could build more advanced code that strips formatting, parses HTML, or otherwise accounts for the existing tags. But for a normal Notes field that is plain text, the straightforward approach is fast, reliable, and much easier to maintain. Sometimes "good enough" is not an insult. It is a feature.

There is another limitation worth knowing about: this basic version is intended for a single form. It does not work properly in a continuous form when the highlighted text box is unbound. In a continuous form, Access reuses the same controls for multiple rows, so assigning a value to that unbound display control can make every visible record appear to have the same highlighted notes.

For a continuous form, you need a different approach so each record calculates and displays its own highlighted value. You can also expand the idea to support multiple search terms, allowing users to highlight several words or phrases at the same time. Those enhancements are covered in the extended material, but the basic single-record version is a very useful tool all by itself.

Once you understand that a Rich Text text box can render formatting tags, this opens up a lot of possibilities beyond searching. You can emphasize warnings, color-code keywords, highlight overdue information, or generate more readable display text without permanently altering the stored data.

Watch the embedded video for the complete walkthrough, including the Rich Text setup, the VBA Replace logic, the quotation-mark issue, and a live demonstration of the limitations with existing Rich Text and continuous forms.

Live long and prosper,
RR

How Can Validation Rules Prevent Invalid Table Dates in Microsoft Access? Video Quiz B1.7

Ready for a quick Microsoft Access table basics quiz? These five questions cover some of the little details that can make a big difference when you are entering data, designing tables, and trying to keep your database from turning into a time-travel paradox.

Try answering each question before reading the answer. If you miss one, no worries. That is exactly why quizzes exist. Well, that and to remind us that pressing Delete is sometimes a lot more permanent than people expect.

{{YOUTUBE_EMBED_PLACEHOLDER}}

Question 1: When you resize or rearrange columns in a table's Datasheet view, what are you changing?

A. The table's layout
B. The data type of each field
C. The values stored in the records
D. The table's primary key

Answer: A. The table's layout.

Changing the width of a column, moving it left or right, or adjusting how the datasheet looks affects the layout of the table. It does not change the actual field type, the data stored in the table, or the primary key. You are just making the table easier to view while you work with it.

Question 2: You have started entering a new record but have not saved it yet. What can you do to cancel the new record?

A. Press Ctrl+S
B. Press Escape
C. Click the record selector and press Delete
D. Close Access without saving

Answer: B. Press Escape.

If you are in the middle of entering a new record and decide, "Nope, I did not mean to do that," press Escape. This cancels the current edit or abandons the new record before it is saved. It is one of those handy little Access habits that can save you from entering accidental junk data.

Once the record has been saved, Escape will not magically make it disappear. At that point, you are dealing with an existing record and need to delete it carefully if it truly should not be there.

Question 3: Why is it often better to leave a non-critical field, such as a phone number, optional?

A. External fields cannot be searched later
B. Access automatically fills optional fields with correct values
C. A blank value is often more useful than made-up or incorrect data
D. Optional fields are automatically removed from forms

Answer: C. A blank value is often more useful than made-up or incorrect data.

This is an important database principle: no data is better than bad data. If you do not know a customer's phone number, do not make one up just to fill in the field. Incorrect information looks valid, gets trusted, and can cause problems later when someone tries to call that number, run a report, or make a decision based on it.

Make fields required only when the information is truly necessary. A customer name may be essential. A phone number may be helpful, but not always mandatory. Let your table design reflect what your business actually needs.

Question 4: Which Access feature is best for preventing a customer's sent state from being earlier than their company's founding date or later than today?

A. A validation rule
B. A record selector
C. An AutoNumber field
D. A column layout change

Answer: A. A validation rule.

A validation rule lets you tell Access what values are acceptable. For date fields, this is especially useful because dates can be entered incorrectly without looking obviously wrong. Somebody might type a date ten years before the company existed, or accidentally enter a future date when that should not be possible.

You can use a validation rule to restrict a date so that it must fall within a sensible range. For example, a sent date could be required to fall on or after the company's founding date and on or before today's date. That way, Access stops bad dates at the table level before they can spread into forms, queries, reports, and whatever else you build later.

Validation rules are not just for dates, either. You can use them to limit quantities, prices, percentages, ages, and many other values. The goal is simple: prevent invalid data from getting into the table in the first place. Cleaning up bad data later is never as much fun as preventing it now. Shocking, I know.

Question 5: What is the safest way to remove a record that has already been saved in an Access table?

A. Press Escape until the record disappears
B. Clear each field individually and leave the record blank
C. Select the record with its record selector, press Delete, and confirm carefully
D. Change the AutoNumber value to zero

Answer: C. Select the record with its record selector, press Delete, and confirm carefully.

To delete a saved record in Datasheet view, click the record selector at the far left of the row, press Delete, and carefully confirm the deletion. The record selector is the gray box or bar beside the record. Selecting it makes sure you are deleting the entire record, not merely clearing the contents of one field.

Be careful here. Deleting a record is not the same as canceling an unsaved edit. Once you confirm the deletion, that record is gone. Also, clearing every field and leaving a blank record behind is generally not a good substitute for deletion. You would still have a record taking up space in your table, possibly with an AutoNumber value and no useful information attached to it.

If you got all five right, congratulations, your database has survived another trip through the timeline. If not, review the questions and watch the embedded video for the full quiz walkthrough. These are beginner-level concepts, but they are the kind of basics that keep an Access database clean, usable, and much less likely to cause headaches later.

Live long and prosper,
RR

Build an Orphan VBA Code Detector for Access Forms and Reports

How much old VBA code is hiding in your Access database? If you have been maintaining the same application for a few years, probably more than you think. Delete a command button, rename a text box, replace a combo box, and the associated event procedure can quietly remain behind in the form or report module. It usually does not break anything, but it certainly makes your code harder to search, read, and maintain. Eventually you are digging through procedures that have not been called since the Clinton administration.

That is the problem addressed in Access Developer 61. In this class, we build an orphan VBA code detector that scans forms and reports for event procedures tied to controls that no longer exist. It is a practical database maintenance tool, but it is also a great look at a powerful side of Access VBA: using VBA to inspect the structure and code of the database itself.

Most developers are familiar with event procedures such as CustomerName_AfterUpdate or btnPrint_Click. Access creates these procedures when you attach an event to a control, and it does not necessarily remove the procedure later if that control gets deleted. The result is perfectly valid VBA that simply has no way to run anymore.

The first part of the class focuses on examining the VBA module behind a form or report. This is where things get interesting. Instead of merely running code, VBA can inspect the code module itself, identify procedures, and determine which event procedure names are present. Once you understand that capability, you can start building all kinds of useful developer utilities.

For example, an event procedure name contains valuable information. A procedure called txtLastName_AfterUpdate tells us that the control name should be txtLastName and that the event is After Update. The detector pulls apart that procedure name, then checks the form's Controls collection to see whether a control by that name is still there. If the control is gone, the procedure is a candidate for cleanup.

Of course, Click events are only the beginning. Real applications can have Before Update, After Update, Double Click, Key Down, Key Press, Key Up, Got Focus, Lost Focus, and plenty of other events. In the class, we create a table of recognized event types and use a recordset to work through them. That makes the utility far more flexible than hard-coding a few event names and hoping you remembered everything.

That table-driven approach is useful well beyond this project. Whenever you find yourself writing a long string of repetitive If statements, there is often a good chance that the information belongs in a table. It is easier to maintain, easier to expand, and much less likely to turn into one of those "why did I do this to myself?" chunks of code six months later.

Once the core detection logic is working, the class turns it into a real maintenance utility. You will build a selector that lets you scan a specific form or report when you are cleaning up one object. Then we add a Scan All option that loops through every form and report in the database, checking each module for event procedures whose corresponding controls no longer exist.

Reports are included because they can accumulate the same kind of leftovers as forms. A report might have old formatting code, click events, or other control-level procedures that survived several redesigns. If you are maintaining a large database, especially one that has been passed around between developers over the years, these little remnants can add up quickly.

The finished tool gives you a much cleaner way to audit your application. Rather than manually opening every form, reading every module, and trying to remember whether a control used to exist, you can let the scanner identify suspicious procedures for you. You still decide what to delete, of course. A good maintenance tool should report what it finds, not go on a rogue code-deletion spree while you are out getting coffee.

The bigger lesson in Developer 61 is learning how to work with Access objects programmatically. You will see how forms, reports, controls, modules, procedures, and recordsets can all work together to create tools that help maintain the database itself. Once you get comfortable with those techniques, an orphan-code detector is just one possibility. You can build code auditors, documentation tools, object inventory utilities, standards checkers, and much more.

If you have a long-running Access application and your VBA modules have started looking like an archaeological dig, this class is for you. Watch the embedded video for an overview, and visit the course page for the complete Access Developer 61 training and full implementation details.

Live long and prosper,
RR

When Should You Use Cascade Delete Related Records in Microsoft Access? Video Quiz X2.3

Relationships are one of the things that keep an Access database from turning into a warp core-level mess. This quiz covers cascade updates, cascade deletes, relationship cleanup, the Relationships window, and subdatasheets. See how many you get right before checking the answers.

These are expert-level relationship questions, but the underlying ideas are important for any Access developer. Cascade delete can be useful, but it is not something you should turn on everywhere just because Access gives you a checkbox. That checkbox can delete a whole lot more than you intended.

{{YOUTUBE_EMBED_PLACEHOLDER}}

Question 1: What does cascade update related fields do in a relationship?

A. Updates every field in the related table.
B. Updates matching foreign key values when the parent key changes.
C. Creates a new child record whenever a parent changes.
D. Changes AutoNumber values into Short Text.

Answer: B. Cascade update updates matching foreign key values in the child table when the parent key changes. For example, if you have a parent record with a manually assigned key and that key changes, Access can automatically update the matching foreign key values in related records.

In practice, I almost never use cascade update because well-designed tables usually use an AutoNumber primary key, and AutoNumber values should not be changed. Still, it can be useful occasionally when you are using a natural key or another editable value as the parent key.

Question 2: Which relationship is usually the best candidate for cascade delete related records?

A. CustomerT to OrderT, so accounting history disappears with the customer.
B. EmployeeT to PayrollT, so payroll history is erased when an employee leaves.
C. OrderT to OrderDetailT, so an abandoned order removes its line items.
D. CustomerT to ContactT because every related table should always cascade delete.

Answer: C. An OrderT to OrderDetailT relationship is a classic example of when cascade delete can make sense. Order detail records have no real purpose without their parent order. If you delete a temporary, test, or abandoned order, deleting its line items along with it is usually exactly what you want.

On the other hand, be very careful with customers, orders, employees, payroll records, invoices, payments, and anything involving business history. Deleting a customer should not automatically erase their order history. Deleting an employee should definitely not make payroll history vanish into the Holodeck. In many cases, it is better to mark records inactive rather than delete them at all.

Question 3: What must happen before you can delete a table that participates in a saved Access relationship?

A. The relationship must be removed first, either manually or through Access's prompt.
B. The table's primary key must be changed to an AutoNumber.
C. Every query that uses the table must be converted to a report.
D. Referential integrity must be turned on for every other table.

Answer: A. Access will not let you casually delete a table that is part of a saved relationship. You need to remove the relationship first. Depending on what you do, Access may prompt you to remove the relationship, or you can open the Relationships window and delete the relationship line yourself.

This protection is a good thing. Without it, you could delete a table and leave the rest of your database pointing into empty space.

Question 4: In the Relationships window, what happens if you remove a table box from the layout without deleting its relationship line?

A. The underlying table is deleted from the database.
B. Referential integrity is automatically disabled.
C. All related child records become orphaned.
D. The table is only hidden from that window; the relationship still exists.

Answer: D. The Relationships window is partly a layout tool. Removing a table box from the window does not delete the actual table, disable referential integrity, or remove the relationship. It simply hides that table from the current relationship layout.

If you want to remove the actual relationship, delete the relationship line. If you just want to make the diagram less cluttered, removing the table box is perfectly fine. Think of it as cleaning up your map, not demolishing the building.

Question 5: CustomerT has related ContactT and OrderT records. You want its Datasheet view to show a custom query containing only selected contact fields as the subdatasheet. What should you configure?

A. Set CustomerT's subdatasheet name to the custom query and specify the master and child fields.
B. Change CustomerT's primary key to match the query name.
C. Turn on cascade delete related records for ContactT.
D. Create an AutoNumber field in the custom query.

Answer: A. You can use a query as a subdatasheet instead of displaying the entire related table. Set the parent table's Subdatasheet Name property to your query, then specify the appropriate Link Master Fields and Link Child Fields.

That gives you control over what users see when they expand a customer record in Datasheet view. Instead of showing every field in ContactT, you can show only the useful contact information. Just make sure the query includes the field needed to link it back to the parent customer record.

The big takeaway is that cascade delete is best reserved for true dependent records, such as order details belonging to an order. Do not use it as a shortcut for cleaning up important historical data. Referential integrity should protect your data, not help it disappear faster.

If you want to see these relationship settings and examples in action, watch the embedded video. It covers the answers and the reasoning behind each one.

Live long and prosper,
RR

How Do You Include All the End Date Times in Microsoft Access Queries? Video Quiz X0.1

Timekeeping queries in Access can seem simple until dates and times get involved. Then somebody runs a report for a date range, notices that all of the late-afternoon shifts on the ending date are missing, and suddenly payroll looks a little suspicious. This quiz covers a few important Access date, time, totals, and overtime concepts.

Give yourself a few seconds to answer each question before checking the answer. No peeking. Access may not have a lie detector built in, but your conscience probably does.

Question 1: An employee clocks in at 8:00 AM and clocks out at 4:30 PM. In Access, subtracting Time In from Time Out returns a value measured primarily in what unit: hours, minutes, days, or seconds?

The answer is days. Access stores Date/Time values as numbers. One full day equals 1, so one hour is 1/24 of a day. If an employee works 8.5 hours, the raw subtraction result is actually a fraction of a day. To display or calculate hours, you generally multiply that result by 24.

This catches a lot of people the first time they build a timekeeping query. They subtract two times, see a decimal value such as 0.354166..., and wonder what kind of alien math Access is using. It is not alien math. It is just days.

Question 2: You have several work log records for the same employee because they clocked out for lunch. Which type of query is best for calculating that employee's total hours for the day: crosstab, aggregate, delete, or make-table?

The answer is an aggregate query, also commonly called a totals query. If an employee has multiple work periods in one day, calculate the duration of each period and then use the Sum row in the query totals to add those durations together.

A crosstab query is useful when you want to rearrange data into a spreadsheet-like summary. A delete query removes records, which is generally not the preferred payroll calculation method. A make-table query creates a new table. None of those are what you need just to total someone's daily work time.

Question 3: Your employee time query uses Between Start Date And End Date, but shifts later in the day on the end date are missing. Which criteria correctly includes the entire ending date?

The correct criteria is Greater Than or Equal To Start Date And Less Than End Date Plus 1.

In a query criteria expression, that logic looks like this: >= StartDate And < EndDate+1. The important part is using less than the day after your end date, rather than less than or equal to the end date itself.

Why? Because a date entered without a time is treated as midnight. If you search through September 6 using a criterion such as Between #9/1/2026# And #9/6/2026#, Access interprets that ending value as September 6 at 12:00:00 AM. You get records at midnight, but you miss records from 8:00 AM, 4:30 PM, or 11:59 PM that same day. Not exactly what most people mean by "include September 6."

Using a less-than comparison against the next day solves the problem neatly. It includes every possible time on the ending date, without requiring you to type 11:59:59 PM or play games with fractional seconds. This is one of the best habits you can develop when filtering date ranges in Access.

Question 4: A company pays overtime for hours worked beyond eight hours in a day. Which Access function is commonly used in a calculated query field to split regular hours from overtime hours: IIf, DLookup, Replace, or Date?

The answer is IIf, short for Immediate If. It lets you test a condition and return one value if the condition is true and another if it is false.

For example, if total hours are greater than 8, an IIf expression can return 8 regular hours. Otherwise, it returns the actual total hours worked. A similar expression can calculate overtime by returning total hours minus 8 when the employee worked more than 8 hours, and zero otherwise.

DLookup retrieves a value from another table or query, Replace works with text, and Date returns the current date. Useful tools in the right situation, sure, but they are not your go-to functions for separating regular time from overtime.

Question 5: An employee earns $20 per hour and works two overtime hours at time and a half. What should the overtime portion of the payroll calculation return: $20, $40, $60, or $80?

The answer is $60. Time and a half means the overtime rate is $30 per hour. Two overtime hours at $30 each equals $60. Yes, you do occasionally have to do regular old math when working with Access. The database has not yet figured out how to negotiate your union contract for you.

When building payroll calculations, keep the pieces separate: calculate total hours, determine regular and overtime hours, calculate the overtime rate, and then multiply. Trying to cram all of that into one giant expression can work, but it becomes much harder to troubleshoot when somebody's paycheck is off by $60.

If you missed any of these, do not worry. Date and time calculations are one of those Access topics that become much easier once you understand how Access stores values and how query criteria handle the hidden time portion of a date. Watch the embedded video for the full quiz walkthrough and a little more explanation.

Live long and prosper,
RR

Why Text Boxes Concatenate Numbers Instead of Adding in Microsoft Access Video Quiz D2.1

Time for a quick Access and VBA calculator quiz. These are the kinds of little details that can make a form calculator work beautifully, or make it display something like 54 when you were expecting 9. Access is helpful right up until it decides your numbers are actually text. Then it gets creative.

See how many of these you can answer before reading the explanations. If you need more than a few seconds, no worries. This is not a timed final exam, and nobody is going to take away the one printer in the office that still works.

Question 1: Why can adding values from two Access text boxes sometimes produce 54 instead of 9?

Answer: The control values are being treated as text and concatenated.

If one text box contains 5 and another contains 4, VBA may see them as the strings "5" and "4" rather than the numeric values 5 and 4. When text is combined, the result is 54. That is concatenation, not arithmetic. Before performing calculations, make sure you are working with numeric values. Functions such as Val can convert appropriate text input into a number, although you should still validate the input first. Garbage in, garbage out, as they say. Or in Access terms, garbage in, mysterious runtime error at 4:57 PM.

Question 2: In VBA, which expression calculates the nth root of a positive number?

Answer: Use an exponent of 1/n. For example, the nth root of a positive value can be calculated by raising that value to the power of 1 divided by n. The caret character (^) is VBA's exponent operator.

A square root is simply a special case of this idea: the second root. So a value raised to the power of 1/2 gives its square root. Just remember that ordinary real-number roots have limitations with negative values, especially when you are dealing with even roots.

Question 3: What does On Error Resume Next do when VBA encounters a runtime error?

Answer: It ignores the error and continues with the next statement.

This can be useful in very limited, carefully controlled situations, but it is not a magic "make my program work" command. If you use it across an entire procedure, VBA can quietly skip over an important error and leave you wondering why your calculation is wrong. That is generally worse than getting an error message, because now the bug is hiding under the couch.

Question 4: What is the best way for a calculated routine to handle an attempted division by zero?

Answer: Test the divisor first, then skip the calculation and show a useful message.

Do not change the zero to one just to avoid the error. That prevents the crash, sure, but it also gives the user a mathematically incorrect answer. Instead, use a simple If Then test to see whether the divisor is zero before performing the division. If it is, explain the problem and let the user correct the input.

Question 5: A square root button uses only one input control. Which validation approach is most appropriate before converting values with Val?

Answer: Check only the input control required by the square root operation for Null.

Validate the controls that matter for the operation the user selected. There is no reason to require every text box on a calculator form to contain a value when the user only clicked the square root button. Checking unrelated controls creates unnecessary errors and frustrates users. Good validation is specific: check the needed value, make sure it is valid, then perform the calculation.

These are small concepts, but they are the foundation of building reliable calculator routines in Access VBA. Handle text-versus-number conversions correctly, validate only what you need, and prevent predictable errors before they happen. Watch the embedded video for the full quiz walkthrough and demonstrations.

Live long and prosper,
RR