Advanced Data Validation
By Richard Rost
7 months ago
Advanced Data Validation Techniques for MS Access
In this Microsoft Access tutorial, I will show you how to use advanced data validation techniques, including input masks, field- and table-level validation rules, and VBA with the Before Update event to prevent duplicate entries and ensure your data stays accurate. You will also learn practical examples of using the DLookup and NZ functions to compare data across records.
Members
There is no extended cut, but here is the file download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, data validation, required property, input mask, validation rules, field-level validation, table-level validation, before update event, after update event, VBA data validation, DLookup function, NZ function, cancel save in VBA, prevent duplicate records, order date validation, If Then statement, form-level validation, field-level before update, form-level before update, compare multiple fields, conditional data entry, null handling VBA, debug compile VBA, design view properties, error message VBA, customer order restriction, date validation VBA
Intro
In this video, we will talk about advanced data validation techniques in Microsoft Access. We will look at why you should use the Required property with caution, how input masks work, and how field-level and table-level validation rules help keep data accurate. I will also show you how to use VBA with the Before Update event to prevent users from creating duplicate orders on the same date, using functions like DLookup and NZ, custom error messages with MsgBox, and Cancel = True to stop unwanted updates.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today we are going to talk about data validation. There are lots of different ways to validate data in Microsoft Access. Of course, garbage in, garbage out. So you want to prevent garbage from getting into your tables in the first place.
The very first thing I like to bring up when it comes to data validation, even for advanced students, is to be careful using the Required property. I know it sounds like a good idea to force the user to have to enter a value. But if they do not have that information, they are likely to just make something up. No data is better than bad data. I teach this in Access Beginner 1.
Do not force your users to have to put stuff in. If you require users to type in a phone number and they do not have the customer's phone number and the customer has already left, they are just going to type in 867-5309. Now you have no idea how to generate a list of customers whose actual phone numbers you do not have. I would rather deal with nulls than bad data.
Next is input masks. Input masks are great for forcing users to enter data in a particular format. Social security numbers, phone numbers, dates, you name it. I have a whole separate video on input masks. You can go watch that one.
Next up, you have validation rules. These are built into tables. You can also use them in your forms. There are two kinds of validation rules. There are field-level validation rules that only apply to one field. These are good for making sure someone does not enter something like a negative number for the number of children someone has, for example. That would be kind of weird. In this video, I go through a whole bunch of different examples.
Then you have table-level validation rules, which let you compare values across multiple fields in the same record. This is where you can do things like make sure the end date comes after the start date, that kind of stuff.
Once you get past these basic-level validation techniques, we get into some VBA stuff. The best way I think to handle validating data is in the Before Update event. This is the event that runs before the data is saved. It allows you to cancel the save if the user enters something that they are not supposed to enter.
There are two different kinds of Before Update events. There is a field-level Before Update event that happens before you save that value from that field. Then there is the form-level Before Update event, which you can use to verify the data across all of the fields in that record, kind of like validation rules. You have field-level validation rules and table-level validation rules. Well, that is how Before Update works. There are two different ones there.
You can even mix Before Update and After Update together and do some really cool stuff. I have a whole separate video on that. Go watch that one.
Let me show you an example of something that you can only do with the Before Update event for new data validation. You cannot do it with any of the other techniques. Let us say you want to make it so that your customers cannot have two orders placed on the same date. I know it is a silly example, but let us say you have a membership plan. You can only take out one movie a day or maybe a boat rental where you can only do one a week. That kind of stuff. You have to compare this record with other records in the same table or even different tables.
If the user tries to enter in two orders with the same date, it will display a message saying you cannot do that. It is just one example that I picked, but there are lots of different businesses that have lots of different rules. If you have a rule like that, you will see how this is beneficial.
Before we get started, this is developer-level material. If you have never done any VBA programming before, go watch this video. It will teach you everything you need to know to get started in about 20 minutes. We are also going to use an If Then statement, the DLookup function, and we will use NZ to convert null values to zero. These are all free videos. They are on my website and my YouTube channel. Go watch those and come on back.
If you have never watched any of my videos before, this is the TechHelp free template. It is a free database you can grab off my website if you want to. It has basic stuff in it that I use for my videos. It has a customer list and a customer form. Customers can have orders and so on.
Let us say that we do not want a customer to have two orders on the same date. For example, he has one from, these are old, from 2023. We are going to use a field-level Before Update event for the order date. If it involved multiple fields, like if you could not have two with the same date and the same description, you could use a form-level Before Update event and check both fields. It is the same basic idea.
I am going to go into Design View. I am going to go into the order date's property event, Before Update, and hit the builder button. That will bring up the code builder. Let me resize it. There we go.
We are going to look up in the Orders table and try to see if this customer has an order on the same date. We need to store that value somewhere. Let us put a little comment in here: see if this customer has another order on the same date. We are going to assume that the orders in our table only have dates and not dates with times because then it gets more complicated. If your order dates have times on them, then you have to use inequalities. You have to say greater than or equal to this date and less than the date next to it. We are assuming we are just dealing with straight dates here. I have a separate video on dealing with dates with times.
I am going to Dim an OrderID as Long. I will say OrderID equals DLookup. DLookup what? OrderID from the Orders table.
Now we need criteria. First, it has to be this customer. On this particular form, CustomerID is actually stored in a field called CustomerCombo. You can use CustomerID if you want. I like to use the name of the field just to make sure. So we are going to say where CustomerID equals CustomerCombo.
What other criteria do we have? We have to match the OrderDate. So the OrderDate has to be equal to, and then dates have to go inside pound signs, right? OrderDate is the field on the form, then close the pound signs. Now we have matched up the customer as this customer and the order date as the order date on this order.
But we are not done yet because we also want to make sure it is a different order. So we have to say and OrderID is not the OrderID on this order. If you could have two orders in the system and then just go in and change the second one, it would have a different OrderID.
If that value exists, it will return the ID. If it does not exist, this will return a null, which we do not want because we cannot stuff a null into a Long. That is where the NZ function comes in. NZ: if that is null, give me back a zero.
Now we can check for that zero. If OrderID is not zero, then that means we have another order that matches those criteria. So, message box: Customer has another order on this date. That is bad, so we are going to set Cancel = True. Yes, I know Cancel is an integer technically, but I always use Cancel = True because it just reads better and it works. It checks for zero or not zero. False is zero. True is technically negative one, but that is fine.
When we are done, we are going to Exit Sub and End If and we are done. Why do I put the Exit Sub there even though we are going to exit the sub down here anyway? That is in case later on I decide to put more stuff down here, maybe another condition. At this point, I have enough to exit the sub. I always try to think about what I am going to do in two or three days or two or three years, which is why I started putting comments on everything too. Me three years from now can just look at that and go, oh, okay, I get it now. I will reread everything and try to make sense of it.
Ready? Save it. Control-S. Debug, compile once in a while. All of our code is good. Let us exit out of here. Close it. Close it.
All right, open it. I am just going to change this one to today's date. Cool. I put in today's date, six four. I am going to go to the next one and change this guy's date. Same thing. I am going to hit Tab. Customers have another order on this date. You did not say the magic word. Now I hit OK and I have no choice but to either hit Escape and cancel that edit, or I can change it to something else.
If I go to another new record and try to put today's date in there, it is going to say, you did not say the magic word. Great movie.
That is that. That is an example of something you cannot do with the other basic validation rules, required, input mask, or regular validation rules, table-level validation rules. You need VBA to check against other records like that.
Do you like this kind of stuff? Do you like learning with me? Check out my developer lessons. I have tons of them on my website. I teach everything you need to know about VBA, and this is really everything you need to know about VBA to be a good developer. I am up to Developer 52, I think, which is my latest one. There is lots and lots of stuff on my website. If you want to keep learning, check it out. Link is down below.
That is going to do it, folks. That is it for advanced data validation. If you have other types of data validation you want to see examples for, send them in, post a comment down below, drop me an email, or post it in the forums. Let me know what you want to see.
I am always looking for great ideas for new videos. If you are looking for something Access related and you do not find it, I want to know about it. I will make you a video.
That is your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you next time.
TOPICS:
Required property and its pitfalls
Field-level validation rules in tables and forms
Table-level validation rules and cross-field validation
Using the Before Update event for data validation
Field-level Before Update event in forms
Form-level Before Update event in forms
Using DLookup and NZ for validation in VBA
Preventing duplicate orders on the same date
Custom error messages with MsgBox in VBA
Canceling record updates using Cancel = True
Criteria building in DLookup with dates and IDs
Validating data against other records using VBA
COMMERCIAL:
In today's video, we're learning about data validation in Microsoft Access. You will see the pros and cons of the Required property, how input masks can force a specific format, and how field-level and table-level validation rules work to keep your data clean. Then, we'll get into developer-level VBA techniques like the Before Update event to prevent users from creating duplicate orders on the same date, using functions like DLookup, If Then, and NZ. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the main reason Richard Rost cautions against using the Required property for fields in Access tables?
A. Users may enter incorrect data just to satisfy the requirement
B. The Required property slows down data entry
C. It makes it impossible to leave fields empty
D. It always leads to database corruption
Q2. Why does the video suggest that "no data is better than bad data" in a database?
A. Bad data can be difficult to detect and may cause reporting errors
B. Null values cannot be used in calculations
C. Databases do not accept empty fields
D. Good data will automatically replace bad data
Q3. What is the purpose of an input mask in Access?
A. To force users to enter data in a specific format
B. To restrict access to database tables
C. To highlight errors automatically
D. To encrypt sensitive data
Q4. Which kind of validation rule compares values across multiple fields in a single record?
A. Table-level validation rule
B. Field-level validation rule
C. Input mask
D. Required property
Q5. What is a field-level validation rule most suitable for?
A. Ensuring a single field has valid data, like preventing negative numbers
B. Comparing two fields in a record
C. Formatting fields as currency
D. Restricting access to certain tables
Q6. What type of event is best for validating data before it is saved to the database using VBA?
A. Before Update event
B. On Click event
C. After Delete event
D. On Open event
Q7. What is the key benefit of using the Before Update event on a form?
A. It allows validation and cancellation of saving invalid data
B. It automatically formats all data on the form
C. It creates new records upon validation
D. It deletes records that do not match validation rules
Q8. In the video example, what is being prevented using the Before Update event?
A. A customer having two orders on the same date
B. Entering alphabetic characters into a number field
C. Required fields being left blank
D. Saving a record with a missing customer ID
Q9. Why is the NZ function used in the VBA code example?
A. To convert null values returned by DLookup to zero
B. To normalize zip code entries
C. To zap invalid data from tables
D. To network related queries more efficiently
Q10. If you need to check validation rules involving more than one field on a form (for example, date and description), which event would you typically use?
A. Form-level Before Update event
B. Field-level Before Update event
C. After Insert event
D. Required property
Q11. Why is an Exit Sub statement used after setting Cancel = True in the event procedure?
A. To prevent subsequent code from running if validation fails
B. To exit Access when done
C. To automatically restart the application
D. To undo changes to the record
Q12. Why can the validation in this example not be done using only table-level validation rules?
A. It requires checking against other records in the table
B. Table-level validation cannot enforce required fields
C. Table-level validation only works for text fields
D. The Required property is needed to do this
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone is all about data validation in Microsoft Access. As your instructor, I want to stress how crucial it is to prevent bad data from making its way into your tables. As the saying goes, garbage in, garbage out. So let's focus on effective strategies to ensure data accuracy from the start.
The first point I always emphasize, even with advanced users, is to be cautious when using the Required property in your tables. It might seem wise to force users to enter data into certain fields, but this can backfire if they don't have the information handy. Users will often just make up data to bypass the requirement, which gives you inaccurate entries. For example, if someone's required to input a phone number they don't have, they might just enter something random like 867-5309. In this situation, it's better to have a blank field rather than incorrect information. I cover this concept early on in my Beginner 1 class.
Input masks are another useful tool. They help ensure that users enter data in a specific format, which is great for fields like social security numbers, phone numbers, or dates. I actually have an entire video dedicated to input masks, so if you're interested, check that out for more details.
Validation rules are an additional method to control data entry. These can be set up either at the field or the table level. Field-level validation rules apply to just one field and are great for things like ensuring that no one enters a negative number for the "number of children" field. I provide several examples of these types of rules in this tutorial. Table-level validation rules, on the other hand, allow you to compare values across multiple fields within the same record. For instance, you might want to ensure that an end date always comes after a start date.
Once you move beyond these basic techniques, you'll encounter scenarios where the built-in validation options aren't sufficient. That's where VBA comes into play. I find that the Before Update event is especially powerful for data validation. This event lets you intercept the data just before it gets saved, giving you an opportunity to cancel the save if something isn't right.
There are two versions of the Before Update event to be aware of. The field-level Before Update event fires when a single field's value is being saved, and the form-level Before Update event lets you check values across all fields in a record before they're committed to the table. This is similar in spirit to field- and table-level validation rules. Using both in the right context gives you flexibility in handling your data checks.
You can also coordinate Before Update and After Update events to achieve specific data validation flows, which I cover in another one of my videos if you want to see how those work together.
Let me provide an example that really highlights the power of VBA-based validation. Imagine you need to ensure that a customer cannot place two orders on the same date. While this might seem like a simple or rare scenario, it does arise in cases like membership plans, movie rentals, or boat rentals where timing and frequency matter. You need to compare the data being entered with other records in your tables, and this is not something input masks or basic validation rules can handle.
If a user tries to place multiple orders for the same date, the system will let them know with a clear message, stopping the duplicate entry. This approach is highly flexible and can be adapted for all sorts of complex business rules.
A quick note before you get started: this is developer-level material. If you have never done any VBA programming in Access before, I recommend watching my introductory video on VBA, which gets you up and running in about 20 minutes. For this example, you will also need to be familiar with If Then statements, the DLookup function, and converting nulls to zeros using the NZ function. I have separate instructional videos on each of these topics, all available for free on my website and YouTube channel.
For demonstration purposes, I use the TechHelp free template, which you can download from my website. This template has a basic setup with a customer list, customer form, orders, and so on.
Let's say we want to prevent a customer from having two orders on the same date. We'll implement this using a field-level Before Update event on the order date field. If you wanted to validate using multiple fields, like order date and description, you could use the form-level Before Update event to check both values.
In the code, we check the Orders table to see if a customer already has an order with the same date. If the database columns only include dates and not times, this process is straightforward. For situations where date and time are combined, things get a bit more involved; you would have to use inequalities to compare ranges. For this tutorial, we're only working with dates.
To make this happen, we look up whether there is another record for the same customer with a matching order date but a different order ID. If such a record exists, we display a message to the user and cancel the data entry. This prevents the duplicate record from being saved.
If the lookup does not find a match, the save proceeds as normal. A key point here is using the NZ function to handle possible null results from DLookup, making the logic easy and reliable.
After writing your code, always remember to save your work, and I recommend doing a Debug Compile to check for any syntax errors. Testing this in the form, I demonstrate what happens when you try to enter a duplicate order date for the same customer. The message appears and the edit is blocked until the data is corrected.
This type of data validation simply isn't possible using only required fields, input masks, or basic validation and is a great example of where VBA shines. With these techniques, you can handle all sorts of validation logic tailored to your unique business needs.
If you enjoy this kind of content, I encourage you to check out my developer-level Access courses. There's a wealth of information on my website, covering everything you need to know to really get the most out of VBA with Access.
That concludes our look at advanced data validation in Microsoft Access. If you have any other types of data validation you want to see explored, please leave a comment, send me an email, or post in the forums. I am always looking for new ideas based on your needs.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Required property and its pitfalls
Field-level validation rules in tables and forms
Table-level validation rules and cross-field validation
Using the Before Update event for data validation
Field-level Before Update event in forms
Form-level Before Update event in forms
Using DLookup and NZ for validation in VBA
Preventing duplicate orders on the same date
Custom error messages with MsgBox in VBA
Canceling record updates using Cancel = True
Criteria building in DLookup with dates and IDs
Validating data against other records using VBA
|
