Warn But Allow
By Richard Rost
3 years ago
Warn but Allow Duplicate Records in Access
In this Microsoft Access tutorial I'm going to teach you how to have the database warn the user if a particular record exists based on a duplicated field such as the phone number but give them the option to either continue adding the record or to open the existing record. We use a little bit of VBA to accomplish this task.
Avery from Alexandria, Virginia (a Platinum Member) asks: There are a couple of different fields that I track a user by in my database. One is their phone number and although I like to try to keep them unique sometimes there are duplicates between two customers, so I don't want to make it indexed (no duplicates). So I'd like to have it where if my user is entering a new customer's record and the phone number is already in the database it warns me but gives the option to either go to the existing record or continue adding the new record. It's not always practical to search up front.
Members
There is no extended cut, but here is the database file:
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!
Pre-Requisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Create a duplicate entry alert, popup message altering a duplicate, warn on duplicate record but allow entry, Handling Duplicates
Subscribe to Warn But Allow
Get notifications when this page is updated
Intro In this video, I will show you how to set up a warning in Microsoft Access so that if a user enters a duplicate value like a phone number while adding a new record, the database will alert them and give the option to continue or open the existing record. You'll see how to use the Before Update event, DLookup, and some basic VBA to accomplish this. I will also explain how to provide a user-friendly message and handle the logic for jumping to the existing record if the user chooses.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to teach you how to have the database warn the user if a particular record exists. If they enter in a duplicated field, let's say the same phone number as someone else, it will give them the option to either continue adding the record or to open the existing record. We'll use a tiny bit of VBA to accomplish this task.
Let's start with the developer level video. Here we go.
Today's question comes from Avery in Alexandria, Virginia, one of my platinum members. Avery says there are a couple of different fields that I track a user by in my database. One is their phone number. Although I like to try to keep them unique, sometimes there are duplicates between two customers. So I don't want to make it indexed with no duplicates. I'd like to have it where if my user is entering a new customer's record and the phone number is already in the database, it warns me but gives the option to either go to the existing record or continue adding the new record. It's not always practical to search upfront.
So basically, what you're saying is your user is going to be entering a new customer record. If they type in the phone number and it's already in the database, you want it to say, hey, wait a minute. This person's already in the database. Do you want to continue adding them, or do you want to cancel this and go to that record? So that's what we're going to do in today's video.
If you've never done any VBA programming before, go watch my Intro to VBA video. It's about 20 minutes long and teaches you everything you need to know to get started. We're going to use the Before Update event, so watch that video if you don't know what it is.
We're going to use DLookup to determine if the customer already exists, which is a very important function if you've never used DLookup. Definitely watch this one on my favorite functions. Go watch NZ to convert null values to zero. We're going to use an If Then statement so we can say if this customer already exists, then do something. Very important. Finally, we'll use a message box to ask them if they want to do something, yes or no. All of these are free videos.
Did you get all of that? A lot of prerequisites for this one, but it's all easy stuff once you get the hang of it. Trust me. Go watch all those videos if you don't know anything I just mentioned. They're all free. They're on my YouTube channel. They're on my website. You'll find links down below.
Here I am in my TechHelp free template. This is a free database. You can download it on my website if you'd like to. Let's say we've got our customer form here. I'm going to put in 716555121 for my phone number.
Now, if I go to add a new customer and someone comes in here and they're Joe Smith or whatever, 716555121, at that point, when they tab off of that field, I want to say, hold on. Wait a minute. That person's already in the database. So what do you want to do? Do you want to continue adding it or do you want to forget this record? Cancel this and go to the other record.
There are a million choices. You can handle this however you want. This is how I would do it. Yes, you're going to lose the just-entered Joe Smith, but you have to pick your poison. That's how I'm going to do it.
Here we go.
We're going to cancel this. We're going to go into design view. We're going to go to the phone number field or whatever field you care to check. You can do this in multiple fields if you want to. Go to the Before Update event. Remember, Before Update can be canceled. That's the nice thing about Before Update versus After Update. After Update happens after the data's already been committed to the table. Before Update has this cancel option.
First, we're going to check to see if this phone number is in the table already. We're going to need an ID variable to store that value.
Dim ID as Long.
ID equals NZ(DLookup(what are we looking up?), the CustomerID from the Customer table, where the phone number equals whatever the user just entered in). But it's got to be inside quotes because it's a string value. So quote, quote, quote, and the phone number, and quote, quote, quote, quote.
If you don't know all of that, go watch my Double Double Quotes video. A lot of people like to use single quotes in here. I don't. I like to use double double quotes, and I have a video on why. I'll put some links down below for that as well.
So now, if this phone number exists in the customer table already, then we'll have an ID, a number other than zero. If it doesn't exist, it's going to return zero. At this point, I can say:
If ID = 0 Then Exit Sub.
We're good; the phone number is not in the table. We can exit out and continue on our merry way entering this record. But if it gets down to this point, that means that we've got an ID returned, so that phone number does exist.
Now I'm going to ask the user, what do you want to do? I'm going to use a message box to ask them. I'm going to say, "Phone already exists. Go to that record?" I like to warn them, "Note that all changes in the current record will be lost." Let them know that if you say yes, it's going to go away.
Next line, vbYesNoCancel plus vbQuestion. That gives us the Yes, No, and Cancel buttons and a little question mark icon. "Phone Exists." That's going to return a Yes, No, or Cancel value. All I care about is Yes.
I like to give people the Cancel option because sometimes they see Yes and No and they panic. If they see Cancel, they'll just hit Cancel.
If it's equal to vbYes, then do some stuff. What stuff do you want to do here? The user has chosen at this point to go to that record - go to the other record.
We're going to cancel editing this one: Cancel = True. Technically, Cancel is an integer, but it's really only looking for zero or a nonzero value. True happens to be negative one, so it works just fine. Cancel = True reads better.
We also have to undo editing of this record, which is going to lose all the other data that was entered, but they really don't have any choice because if you want to move to a different record, you have to. So you have to say Undo. It's going to undo whatever else they've entered in, like first name, last name, etc. That's why I warned them.
Now we can open up the other record. We can say:
DoCmd.OpenForm "CustomerF", , , "CustomerID=" & ID
That will reopen the customer form to that record only. There are other things you can do; if you've got all the records, you can open it up and use a filter, or you can open it up and do a find for it. There are a lot of things you can do. That's the easiest way. That's what I like to do.
Could you open up a second form that's got this user as an example? Yes. You can make a "Customer2" record that shows you the other record. You can't even open up a second instance of the customer form. That's possible, but I don't like doing that. It is possible. If you want to see how to do that, let me know. Maybe I'll make a developer class out of that. It's a little bit more involved than a TechHelp video, but I don't like doing it.
You can have multiple instances of the same form, but it's weird, and there are crazy things you have to remember to keep track of. If you want, make a separate form. For example, make a little pop-up form that says, "We noticed that this phone number is already in the database for Jane Doe at this address. Do you want to use this one or do you want to keep editing?" Then you can cancel it out. Maybe a little pop-up, a modal form or something. There are many things you can do. It's up to you. It's your database. Your Legos - put them together however you want. My job is just to show you alternatives.
Let's give it a quick debug compile to make sure everything's good. Looks kosher. We can close that. Save it.
Now, back to our customer form. Let's go to a new record. I'm typing in Jane Doe, phone number 7165553333. That one's not in there. That's fine, we're good. DLookup didn't see it.
But let's type it in again: 7165551212. Tab. The record already exists. Do you want to go to that record? Note that all changes will be lost. I'm not sure. I'm going to cancel, and at least you're right where you were.
Now let's say we did it the real way. We come in here and we put that and make it a 2. Do you want to go to that record? Say yes, and it switches you over to the other record.
So there you go. That's how you do it.
Are there other options? Yes. I want to hear what other options you have in the comments down below. Make sure you post them for me. I do read them all.
I've got my ways of doing things that I've developed over the last 30 years or so, but I'm always learning from you. You always post cool stuff in the comments and you teach me things from time to time. I love reading it.
That is your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you next time.Quiz Q1. What is the main goal of the VBA solution described in the video?
A. Prevent the entry of any duplicate phone numbers entirely
B. Warn the user about duplicate phone numbers and offer options on how to proceed
C. Automatically merge all duplicate customer records
D. Restrict users from entering any data until duplicates are resolved
Q2. Why does Avery not want the phone number field set to "indexed with no duplicates"?
A. Because phone numbers are never duplicated in the data
B. Because some customers may share the same phone number
C. Because it is not possible to index text fields in Access
D. Because the database does not track phone numbers
Q3. Which event is used to trigger the duplicate check in this solution?
A. After Update
B. On Click
C. Before Update
D. On Current
Q4. Why is the Before Update event preferred over After Update in this context?
A. After Update can be canceled more easily
B. Before Update happens after data is saved
C. Before Update allows the data change to be canceled before saving
D. After Update can only be used on reports
Q5. What VBA function is used to check if a phone number is already in the customer table?
A. DSum
B. DCount
C. DLookup
D. DSolve
Q6. What value will DLookup return if the phone number is not found in the customer table?
A. Null
B. 1
C. 0
D. -1
Q7. Which VBA function is used to convert null values returned by DLookup to zero?
A. NullToZero()
B. IsNull()
C. Nz()
D. ZeroIfNull()
Q8. In the provided solution, what does the message box offer the user when a duplicate is found?
A. Only Yes and No options
B. Only Cancel option
C. Yes, No, and Cancel options
D. Only a warning with no response options
Q9. What action does the code perform if the user clicks Yes in the duplicate record warning?
A. Saves the new record and does nothing else
B. Cancels the add operation, undoes changes, and opens the existing record
C. Deletes both records
D. Ignores the duplicate and continues adding the new record
Q10. What does Cancel = True accomplish in the Before Update event code?
A. Saves the changes to the table
B. Cancels the update and stops saving the new record
C. Deletes the new and the existing record
D. Closes the database form
Q11. Which method is used to switch the user to the existing customer's record?
A. DoCmd.GoToRecord
B. OpenQuery
C. DoCmd.OpenForm with filter
D. DoCmd.Close
Q12. What is the recommended way in the video to handle multiple forms or views of the same record if more user interaction is needed?
A. Open multiple instances of the same form as new windows
B. Create a separate form (such as a popup modal) to display duplicate information
C. Only allow editing of records in datasheet view
D. Export duplicate records to Excel
Q13. Why does the presenter warn the user about changes being lost in the message box?
A. Because moving to another record without saving will lose any newly entered data
B. Because the database automatically merges duplicate entries
C. To encourage the user always to click Yes
D. So users will avoid using the database
Q14. Which of the following best summarizes the flexibility discussed at the end of the video?
A. Users must follow the presented method without modifications
B. There are many alternatives, and users can implement their logic as preferred
C. Only VBA-based solutions are possible in Access
D. Only the presenter's approach will work in every scenario
Answers: 1-B; 2-B; 3-C; 4-C; 5-C; 6-C; 7-C; 8-C; 9-B; 10-B; 11-C; 12-B; 13-A; 14-B
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 focuses on teaching you how to set up a warning system in Microsoft Access to alert users when a duplicate record is about to be entered based on a particular field, such as a phone number. This approach does not prevent users from entering duplicates, but it does give them a choice to either continue adding the new record or open the existing record with the same information. We will accomplish this with just a little bit of VBA.
This question comes from someone who tracks users by fields such as phone number. Although it is helpful to keep these values unique, it is not always possible, as sometimes there are legitimate duplicates among customers. Therefore, instead of making the phone number field indexed with no duplicates, it is better to prompt users with a warning if they enter a phone number that already exists in the database. The user can then choose to either jump to the existing record or proceed with the new entry. This solution is particularly useful when searching beforehand is not always practical.
Let me clarify the goal: When adding a new customer record, if the entered phone number is already present in the database, we want the system to inform the user and ask whether they want to continue with the new record or switch to the existing one. That is exactly what we'll be covering in this lesson.
For those of you who are not familiar with VBA programming, I recommend watching my Intro to VBA video first. It is about 20 minutes and covers the basics you need before attempting this. The method here involves the Before Update event, which you might also want to review if you don't know how it works.
We'll also be using the DLookup function to check whether the entered customer already exists. If you're unfamiliar with DLookup, I suggest reviewing my video on my favorite functions. Another important function we'll use is NZ, which converts null values to zero, and we'll need to use an If Then statement to control the flow of the process. A message box will then be displayed to prompt the user to make a decision.
If some of this sounds daunting, don't worry. Each aspect is explained in detail in my free videos. If you're missing any background, you can find those resources on my website and YouTube channel, using the links provided there.
In my demonstration, I use the free TechHelp template database available for download on my website. On the customer form, a user would enter a phone number, such as 716555121. If a new customer comes along with the same number, as soon as the user tries to move out of the phone number field, the database should check for duplicates and present a message: Do you want to continue adding the new customer or cancel and go to the existing record?
You can handle duplicates in many ways, but this is the method I prefer. Keep in mind that if you decide to switch to the existing record, you'll lose any new, unsaved information you just typed in, which is why I warn users about it in the message box.
To implement this, you'll first open your form in Design View and set up the Before Update event for the field you want to check—in this case, the phone number. The Before Update event is ideal for this situation because it allows you to cancel the update if necessary. In contrast, the After Update event occurs after the data is already saved, which would be too late for this use case.
The code checks if the phone number already exists in the Customer table by using DLookup. The result is stored in a variable. If the phone number is there, DLookup returns an ID other than zero; otherwise, it returns zero. If it returns zero, the code exits and allows the user to keep entering data. If a duplicate is detected, the user is prompted with a message box asking if they want to visit the existing record.
The message box displays a warning that any changes to the current, unsaved record will be lost if the user chooses to switch. Three options are available: Yes, No, and Cancel, presented with a question mark icon. This gives the user a chance to reconsider before making a decision.
If the user confirms they want to open the existing record, editing of the new entry is canceled and any unsaved data is removed using Undo. Then, the system opens the customer form to the record with the matching phone number. There are variations on this approach, such as opening a filtered form or using a pop-up to display the duplicated information, but I find the simplest method is often the most effective.
If you want to get fancy, you could set up a pop-up modal form with additional details or even allow multiple form instances, but these options add complexity. If you are interested in those alternatives, let me know and maybe I'll cover them in a more advanced class.
After adding your code, always do a Debug and Compile to make sure there are no errors. Once everything is saved and the form is reopened, testing with different phone numbers will show you how the system responds to new and duplicate entries.
Remember, there are lots of different ways to handle duplicate detection. I encourage you to share your ideas in the comments section of my videos—I always enjoy learning new techniques from my students.
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 Warn users of duplicate data entry in a form
Use VBA in the Before Update event
Check for existing records with DLookup
Store DLookup result in a variable
Handle string criteria with double double quotes
Prompt user with a message box for action
Cancel form update with Cancel = True
Undo current record edits with the Undo method
Open an existing record with DoCmd.OpenForm and filter
Explain consequences of switching records during data entry |
