Search, Open, Add
By Richard Rost
3 years ago
Search for Existing Records, Open, Add New Ones
In this Microsoft Access tutorial I will teach you how to search to see if a value already exists in the table and if so open that record. If not, prompt to add it as a new record.
Sophie from Laramie, Wyoming (a Platinum Member) asks: Is there a way I can look up a customer by their phone number, and if it doesn't exist, ask the user to add them as a new record?
Members
There is no extended cut, but here's 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, search, existing record, new record, data management, add if doesn't exist
Intro In this video, I will show you how to search for a customer by phone number in Microsoft Access, open their record if it exists, or prompt the user to add a new customer if the number is not found. We will use VBA programming with DLookup, If Then statements, MessageBox, and the After Update event to achieve this functionality. This tutorial covers setting up the search box, handling new entries, and opening records, providing a practical solution for common data entry scenarios in Access.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost. In today's video, we're going to see how to search for a value in Microsoft Access, open the record if it exists, or ask to add as a new record if it does not exist.
For example, you want to look up a customer by phone number. I'm sure you've had that happen in Walgreens, CVS, and all those companies. They want your phone number to get your bonus points. Type in the phone number. If the customer exists, open up the customer record. If not, say, "Hey, this phone number doesn't exist. Would you like to add this as a new customer?" If they say no to that, then we don't do anything.
That's what we're going to cover today. This is a developer-level video, so to do this we're going to need some code. Nothing crazy, maybe about 10 lines of code tops, and it's all logic stuff.
Let's check out the question first, then I'll show you what videos you need for prerequisites. Sophie from Laramie, Wyoming, one of my Platinum members, says: "Is there a way I can look up a customer by their phone number, and if it doesn't exist, ask the user to add them as a new record?"
Yes, Sophie, definitely. We will need a little bit of programming. So let's talk about the prerequisites first.
If you've never done any VBA programming, go watch this: it's my intro to VBA. It'll teach you what you need to know to get started in about 20 minutes. Go watch this first.
We're going to use an If Then statement. If the customer exists then open them up; else, add them as a new record. Go watch this next.
We're going to use MessageBox to ask them what they want to do - a Yes or No response. Go watch this video after that one.
We're going to use DLookup to see if the phone number exists in the customer table in the first place. Very important function. Make sure you learn DLookup.
If DLookup returns a Null value, if it doesn't exist, we're going to convert that Null value into a zero. So go learn about NZ.
We're going to kick all this stuff off with an After Update event in a text box. Your user's going to type in the phone number, and the After Update event's going to fire off all that stuff I just mentioned.
See, this stuff is easy once you've got all the prerequisites under your belt. Once you get all the required knowledge, putting this all together is a piece of cake. All right, here we go.
This is my TechHelp free template. This is a free database you can download a copy of at my website. And all of those videos I just mentioned before this, those are all free too. So go watch all those. It's all free stuff.
I don't give away free cheesy bread. Commercial over.
Anyways, let's take the main menu here and I'm going to hijack this box. We are going to make this box be for the phone search. This guy up here, it's currently the current date. We're going to change that to phone search. The control source will be blank because the user's going to type it in. Get rid of that.
You could format this if you want to or put an input mask on it or whatever, that's all up to you. I think that's good for now.
What we're going to do is we're going to put an After Update event in that box, or you could make a button. Again, these are your Legos; put it together however you want. I'm just going to use an After Update event. It's going to fire off, and it's going to look to see if that number exists in the table.
If it does, it's going to open up that customer's record in the customer form. If not, it's going to say, "Hey, do you want to add this new customer?" So how do we do that? Well, let's go to Events.
After Update... that'll bring up my VB editor; puts me right here in the phone search After Update box. Put some blank lines in here. What's our code going to look like?
Well, I'm going to look up an ID. So: Dim ID As Long.
That ID is going to be equal to NZ(DLookup(...)). What are we looking up? We're looking up the CustomerID from the Customer table, where the phone equals whatever is in that phone search box. It's going to look like this: double quote, double quote, double quote, ampersand, phone search, ampersand, quote, quote, quote. Then close that, comma zero, close that. Done.
There's a lot to unpack there. If you're not familiar with this, that's because we have to put the actual criteria inside of quotes inside of a string, so you need double double quotes. I have a whole separate video that explains that in more detail. Go watch this one.
Where the phone number equals whatever is in the phone search box, this whole thing, if it doesn't exist, will give you a Null value, and that's what NZ is for. It'll convert that Null value into a zero.
So if the phone number exists at this point, we get the CustomerID back. If it doesn't exist, we get a zero back. So now we can say: If ID = 0 Then (it doesn't exist), and we'll do some stuff. Otherwise (phone number exists), we're going to do some other stuff.
I always put my If Then blocks up first unless I'm writing really, really simple code.
Now if it doesn't exist, we're going to ask the user, "Hey, do you want to add this as a new customer?" So we're going to say: If MessageBox("Phone number does not exist. Add as new customer?", buttons), what buttons do you want? You're going to go vbYesNoCancel, and I'm going to add to that vbQuestion. That changes the little icon that comes with it. I like to go with Yes, No, Cancel.
I mention this in a bunch of videos because sometimes if the user's not sure how to answer that question, they'll hit Cancel, which is fine. I just noticed I got "no bot." What is "no bot"? Is it Klingon? OK, the vbYesNoCancel and the question icon. Then the next thing is the title: "Add New" - that's the title on top of the box.
Now if that comes back as vbYes, we're going to do some more stuff, right (add as a new customer), and then if not, else do nothing. Do nothing is really just Exit Sub.
So now we're going to add as a new customer right here. How do we do that? Well, let's open up the customer form in Data Entry mode. That's where you're adding a record, and we'll set that field for them.
So we're going to do DoCmd.OpenForm "CustomerF", , , , acFormAdd. So it's going to open up the customer form, put us on a blank new record, and allow us to add a new record. While we're here, let's be nice and set the phone number for them. So I'll say the phone field on the customer form is equal to whatever they typed in the search box. So:
Forms!CustomerF!Phone = PhoneSearch (what they typed in here).
All right, so that's done. Now we're down here to phone number exists, and if the phone number exists, it's just a matter of opening up that specific record. So: DoCmd.OpenForm "CustomerF", , , "CustomerID = " & ID (that guy we got back from the DLookup).
That's it. Your code's done.
Look it up. If it doesn't exist, ask if they want to add it. If they say yes, add it. If not, do nothing. If it does exist, open up that customer record. Pretty straightforward. Give it a quick Debug - Compile.
All right, let's close it, save it, open it, and here we go. Someone: 655-51212. Enter. Phone number doesn't exist. Add as a new customer? Sure.
Look at that. It opened up, put me on a blank new record, and it put the phone number in there. My record's dirty, so I can continue to enter it and do whatever I want. This is Joe Smith. All done.
If you want to, when you're done you could blank this guy too. Where's the code? Let's go back to the code. For this one and this one, let's blank that. Let's say: PhoneSearch = "" here and PhoneSearch = "" here. Because in both of those cases, where we're either adding it or we've looked it up, do nothing might mean they typed it in wrong and you don't want to force them to type the whole thing in again. Maybe in this particular case, give them the chance; they can just edit it.
All right, so let's delete this. Let's try it again. 716-444-3333. Yes, 716 is my Buffalo days. I'm from Buffalo, New York originally. I live in Florida now. Enter. Doesn't exist. Add as new customer? No, that's not right. Maybe it's 3322. Enter. No, that's not it either. No.
OK, let's say, "Oh, you know what, never mind, it was 716-555-1212." Oh, there you are, Mr. Smith. OK, I got you. Same. Every one of those conditions is now taken care of, and that's how you do it.
If you've been following my videos for any amount of time, you know that I've been asking ChatGPT to do the same things I just showed you how to do, and it gave surprisingly good code. I just asked it the same question Sophie asked me, and it gave me pretty good instructions here. It used a slightly different method.
I'm not a big fan of RecordSetClone, but that's just me. It used FindFirst, which works. It used rs.NoMatch. All right, just a slightly different technique, but it basically got to the same results. I didn't type this in or plug this into my database to see if it works, but just at a glance, it looks like that's not bad code.
If you guys want to try it yourselves, check it out and see. Let me know if you have any problems with it.
I asked Bard the same thing and it doesn't look like Bard's on the right path here, so I'm going to say Bard failed that one.
Then I went ahead and asked Bing, the AI-powered Bing, and it looks like it's got some pretty good code here as well. Again, it used a more complicated method than I would. It used a RecordSet, but there's nothing wrong with this one that I can see. This one made you type in the first name and last name, which is beyond what I asked it to do, but OK, this should work.
We're going to update this slide now to the AI Update instead of just ChatGPT Update, because the other ones are coming along pretty nicely too. I'm going to try using all three of them in future videos.
I'm going to say definitely, the AI is a good place to get started with your code, especially with a problem like this one. But if you really want to learn how to code, that's what I'm for. Still no substitution for a good human instructor, and that's me. So check out my developer classes if you want to learn more programming like you saw today.
There is your TechHelp video for today. I hope you learned something. Live long and prosper. I'll see you next time.Quiz Q1. What is the main purpose of the video tutorial?
A. To show how to add multiple customer records at once in Access
B. To demonstrate how to search for a value, open the customer record if it exists, or offer to add as a new record if it does not exist
C. To generate automatic bonus points for customer phone numbers
D. To teach how to create input masks for phone numbers
Q2. Which Microsoft Access function is used to see if a phone number exists in the customer table?
A. DCount
B. DLookup
C. DSum
D. DMax
Q3. Why is the NZ function used in conjunction with DLookup in this solution?
A. To format phone numbers correctly
B. To perform mathematical calculations
C. To convert possible Null values returned by DLookup into zero
D. To separate multiple search criteria
Q4. What event is recommended to trigger the code for searching and adding customers by phone number?
A. OnClick event of a button
B. OnOpen event of a form
C. After Update event of the phone search text box
D. OnCurrent event of a form
Q5. What is the correct logic for handling the case where a phone number does not exist in the customer table?
A. Display an error and stop the process
B. Automatically add the phone number as a new customer without confirmation
C. Ask the user via a MessageBox if they want to add the phone number as a new customer
D. Search the table again using a different field
Q6. When the user chooses to add a new customer, what mode should the customer form be opened in?
A. acFormReadOnly
B. acFormEdit
C. acFormAdd (Data Entry mode)
D. acFormPreview
Q7. How is the phone number that the user searched for placed into the new customer record?
A. The user has to type it in again
B. It is pre-filled in the form by setting the phone field to the value from the search box
C. It is attached as a comment
D. It is added by a separate query after the record is saved
Q8. If DLookup finds an existing customer with the entered phone number, what action is taken?
A. A new record is created anyway
B. The existing customer record is opened in the customer form
C. The process stops and displays a success message only
D. The program closes the form
Q9. Why might the code include lines that blank the search text box after completion?
A. To prevent duplicate entries in the table
B. To clear the box so it is ready for a new search or correction
C. To automatically close the form
D. To save memory in the application
Q10. According to the video, what is a potential benefit of using AI tools like ChatGPT or Bing for code generation?
A. AI can always provide the exact code needed without further review
B. AI can offer good starting points and suggestions for solving coding problems
C. AI replaces the need for learning to code manually
D. AI is the only recommended way to write VBA code in Access
Q11. Which of the following is NOT recommended as a prerequisite to fully understanding the solution shown in the video?
A. Intro to VBA programming basics
B. Using If Then statements
C. Understanding how to use DLookup and NZ functions
D. Building SQL Server stored procedures
Q12. What is the main advantage of using an After Update event over a button click in this example?
A. It automatically triggers upon data entry, streamlining the process
B. It is the only way to trigger code in Access
C. It prevents the user from making mistakes
D. It enables multi-user concurrency
Q13. How should the DLookup criteria string be constructed when searching for the phone number?
A. By directly plugging the value from the form into the SQL statement with no quotes
B. By wrapping the value in double quotes within the string to create a valid criteria expression
C. By adding the value to a list of parameters
D. By placing the value in square brackets
Q14. What is the correct action to take if the user cancels the MessageBox prompt after a phone number is not found?
A. Automatically add the customer
B. Exit the routine and do nothing
C. Display an error message
D. Search another table for the phone number
Q15. In the tutorial, how are the Access template and prerequisite video resources provided to students?
A. They are only available for paying members
B. They are available as free downloads and video tutorials for everyone
C. Only the template is free; videos require a subscription
D. They are sent only to email subscribers
Answers: 1-B; 2-B; 3-C; 4-C; 5-C; 6-C; 7-B; 8-B; 9-B; 10-B; 11-D; 12-A; 13-B; 14-B; 15-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 how to search for a value in Microsoft Access, specifically how to look up a customer by phone number, open their record if it already exists, or prompt the user to add a new customer if the given phone number is not found. This is a common feature you might have seen in stores where your phone number is requested to access your account or earn rewards.
Let me walk you through how you can add this functionality to your Access database. The scenario is straightforward: when a user enters a phone number, the system will check if that customer already exists. If the phone number is found, the customer's record opens automatically. If not, the user is prompted with an option to add the new customer. If the user does not want to add the customer, nothing further happens.
Since this approach requires some VBA programming, let's cover the necessary foundation. If you have never programmed in VBA before, I recommend starting with my introductory video on VBA. It covers all of the basics and will have you ready in about 20 minutes. You should also be familiar with If Then statements, since that logic will determine what happens based on whether the customer exists or not. Additionally, I use the MessageBox function to ask the user if they want to add a new customer, so it helps to have a good grasp of how MessageBox works, specifically with Yes and No options.
To determine if the phone number already exists, I use DLookup to search the customer table for the phone number. If DLookup does not find a match, it returns a Null, so you need to know about the NZ function to handle that Null result. Finally, everything in this process begins with an After Update event from a text box. When the user types in a phone number and finishes editing the box, the event triggers the logic described above.
All the prerequisite videos and database templates I mention are available for free on my website. They will give you a solid background for this tutorial.
For the actual setup, I use my free TechHelp template database. I modify the main menu form by turning an existing box into a phone search box. The user types the phone number here, and we leave the control source blank since we only need it for this temporary operation. Input masks or formatting are optional, depending on your data requirements.
The core logic is placed in the After Update event for this text box, although you could also use a separate button if you prefer. The After Update event is what kicks off the sequence of VBA code.
Here is a summary of the VBA logic without going into the specific code:
- Define a variable for the CustomerID that results from the lookup.
- Use NZ and DLookup together to search the customer table for the matching phone number. If a match is found, you get the CustomerID; if not, you get zero.
- Use an If Then statement to decide what happens next. If the ID equals zero, the customer does not exist. Display a MessageBox to ask if the user wants to add a new customer.
- Provide Yes, No, and Cancel buttons in the MessageBox so that the user has options. I usually include Cancel to account for any uncertainty.
- If the user chooses Yes, open the customer form in Data Entry mode, which brings up a new blank record, and automatically fill in the phone number field for them.
- If the user says No or Cancel, exit the process.
- If the phone number exists, simply open the customer form directly on the matching record.
You may want to improve the user experience further by clearing the phone search box after adding or opening a record. This lets the user try again easily if they mistyped a phone number.
I ran through several test cases by typing different phone numbers, showing how the form responds whether the number does not exist, does exist, or needs to be added as a new customer. All scenarios are handled properly with this setup.
For those interested in comparing their work with artificial intelligence solutions, I tested this same approach with a variety of AI chatbots, including ChatGPT, Bard, and Bing AI. The AI-generated code generally produced similar results, often using slightly different methods such as RecordSetClone or FindFirst, but ultimately reaching the right outcome. Feel free to review and test those variations yourself if you're interested in alternative approaches.
While AI tools can be an excellent starting point for generating code or ideas, I still believe there is no substitute for understanding the logic yourself. If you want to master these programming concepts and become a better Microsoft Access developer, consider checking out my developer courses.
For a complete video demo that walks you through every step, you can find the full tutorial with detailed instructions on my website at the link below.
Live long and prosper, my friends.Topic List Creating a phone search box on a form
Setting up an After Update event for a text box
Using DLookup to find a matching phone number
Applying the NZ function to handle Null DLookup results
Opening a specific customer record if found
Prompting the user to add a new customer using MessageBox
Handling MessageBox Yes No Cancel responses
Opening the customer form in Data Entry mode
Pre-filling the phone number field for new customers
Clearing the phone search box after the operation |
