Save Notes
By Richard Rost
3 years ago
Save Notes from Customer Form to Contact History
In this Microsoft Access tutorial I'm going to show you how to take notes from your customer form and archive them by saving them in your contact history which you can view in the contact history form and we'll do this with one click and a little tiny bit of VBA.
Grace from Albany, New York (a Platinum Member) asks: I like to keep notes visible on the customer form for the latest info on a client. However, when that's no longer valid, and I need to change it with something else, I don't want to lose what was there. Is there any way to archive that information when something new is typed in?
Prerequisites
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, Archive notes, move notes to contacts, archive data, save old info, docmd.runsql, currentdb.execute, insert into, append query
Subscribe to Save Notes
Get notifications when this page is updated
Intro In this video, I will show you how to archive notes from your Microsoft Access customer form into your contact history table with a single click using a combination of VBA and SQL. We'll walk through adding an archive button to your form, writing the necessary VBA code, and making sure the process is quick and efficient. I'll also explain common pitfalls, like handling empty notes, and how to customize your form for better workflow.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
In today's video, I'm going to show you how to take the notes from your customer form and archive them by saving them in your contact history, which you can view in the contact history form. We'll do this with one click, a tiny bit of VBA and some SQL.
Today's question comes from Grace in Albany, New York, one of my platinum members. Grace says, I like to keep notes visible on the customer form for the latest info on a client. However, when that's no longer valid and I need to change it with something else, I don't want to lose what was there. Is there a way to archive that information when something new is typed in?
Well, yes, Grace, I do the same thing myself.
So for example, on the customer form, if this information, let's say, deliver to back porch or whatever, whatever notes you want to have in here on your customer. So this guy moves, change all the information. This is no longer valid, but you don't want to lose it. Now, you can cut this out and then go to the contact history form in here, and then just paste that in there and that's how you save it.
That's a lot of work. That's like six clicks. Wouldn't it be nice if we had a little button where we could just click on it and it just dumps it in there for you? Then you can type in whatever new information you want. That would be pretty cool, huh? Well, that's what I'm going to show you how to do.
I'm all about automating stuff. If I can save you from having to select the text, copy it, open another form, paste it, just one click. Please, just let me do it in one click. That's the beauty of Microsoft Access, automating stuff like that, making your workflow a lot easier. If I can save you 20 seconds every operation times a hundred times you do it in a day, well, that's a lot of time.
If I can save you 10 minutes a day by automating things and making things easier, that's a whole work week over the course of a year. 10 minutes a day.
So how do we do that? Well, this is going to be a developer level video. So if you've never done any VBA programming before, go watch this video first. It's about 20 minutes long. It teaches everything you need to know to get started.
Go watch my SQL with Access video. It gives you the basics of SQL, what it is, and how it works. You could do this without SQL. You could make it an append query. That's what we're going to do with SQL. We're going to put our big boy pants on.
If you're unfamiliar with the database that I'm using, you can go watch this video, my customer contacts. I'll show you how I built this database. If you want to see how I built it from the ground up, go watch these videos. They're free. They're on my YouTube channel. They're on my website. Watch them and then come on back.
So I need some text in here. Blah, blah, blah. And I'm going to put a button on here that's going to basically copy this stuff into this table and then delete this stuff here.
This is the notes field. I'm on the customer form and it's going into the contact table. We have to set the customer ID equal to that customer ID, the contact date. I think the contact date is defaulted, so we can just use the Now that's in the default value for the table. Let's see. Yeah. You don't have to worry about setting the date.
So we need the customer ID and the description. Or you can put it in the notes, whatever you want. You can make the description say archived information and put it in the notes - whatever you want to do. Usually, I only put short stuff up here. I don't put paragraphs of stuff, so 255 characters is usually good enough.
At this point, since I'm thinking about it, I'm going to open up my Notepad. Where's Notepad? Give me Notepad. I always use Notepad a lot, constantly. What I started doing recently was Notepad, if you don't save your work often, you could lose it. If there's a power outage or something with the computer, I've started to use Google Docs.
I've been using Google Docs to keep stuff open in a web browser because when you type stuff into Google Docs, it's kind of like Word. It saves it. It runs in a browser, which is really cool. I know there's a Word version that runs in a browser too. I kind of like Google Docs - it's a little lighter weight. But anyway, sorry.
ContactT is the table. We need to set customer ID and we need to set description. Those are the fields we need to set. I just put that there. I'm going to slide it off to the side over here. Then I can close that and then I can go to work in here.
We need a button. I'm just going to copy one of these buttons, copy, paste, control C, control V. Just for now, we're just going to put something like that. I'm going to replace it with a picture in a minute.
We're going to slide this over the top of the notes field like that. And we're going to rename it, let's move it up a bit. We're going to rename this guy. Let's call this archive button. Archive button. ArchiveBTN. That's going to go on my Notepad. Archive button. I put this over to the side, like right over here, off to the side there, see.
Just so as I'm working, I'm going to be in the code editor in a minute. I don't want to have to be like, what did I name that button? I can just see it right there. It's open. It's on the side.
All right, so put that back over here. I would close this thing and now I can go right-click, fill the event, and then open up the code editor right there.
So, there are two different commands you can use to execute an SQL statement. There's DoCmd.RunSQL and there's CurrentDB.Execute. I prefer the latter. I have a whole separate video coming out soon that explains the differences between them, but for now, I'm going to use this. CurrentDB.Execute. That's how you execute an SQL statement.
This is going to be an INSERT INTO, which is basically an append query. What table are we inserting into? ContactT. And then what's the field list that you want to insert into? So we've got customer ID and description - those are the fields in that table.
Now, I'm not pulling from records. If I was pulling from records, I could put a SELECT statement here, but I'm not doing that. I'm just putting values in here. So, VALUES. And then again, inside parentheses. Sometimes I like to just put the parentheses in here and just type the values in. So again, it's going to be customer ID and it's going to be description.
But where are we getting these from? We're getting these from the currently open form. So I'm going to replace that customer ID with - I've got to close the string and put & CustomerID & and open the string back up again, because CustomerID is a field on the currently open form. Whereas this is a list right over here. This is a list of fields from the table, the contact table. But down here, the values, I want to actually put the value in there. So the number one is going to go inside this string.
The same thing with description. Now description is going to be this notes field. So here I'm not going to call it description, it's going to be notes, but it's got to be the value of notes. So close that, ampersand notes, ampersand open it back up again. So now I'm putting the value of notes in there, the value of customer ID in here.
But one more thing, customer ID is a number, so I don't need anything around it. Notes is a text string. So what does that need? It needs double double quotes. So double double quotes there and double double quotes there. Yes, I know it's a little confusing. If you're new to it, I have a whole separate video on the double double quotes, concatenation, and talk about single quotes, double quotes, double double quotes, and a lot more.
With Microsoft Access, I prefer using the double double quotes and not the single quote for a couple of different reasons. I cover that in this video, so go watch that. A lot of SQL Server people, for example, you have to use the single quote, but with Access, I like sticking to the double double quote.
If you want to learn more about that stuff, you'll find links to these videos down below in the link section.
So this line will execute and then we'll copy the notes for the current customer ID into the contact table. And then when we're done, we're going to set the notes equal to null, just blank it. And then we're going to probably want to Me.Dirty = False to save the record.
Save that. Give it a quick debug compile just to make sure our syntax is good.
Come back out here, close it, open it up. I got blah, blah there, click the button and it goes away.
Let's open up the contacts. And let's open this up a little bit more. Oh, it's right there on top. See, blah, blah. There it goes.
Let's put some more stuff in here. See, this is good if, for example, you could put this in an event too. You could put this in the After Update event and save every single change, but I don't always want to save every little change. Sometimes I want to delete stuff. In fact, you could make a quick button to delete stuff too.
Watch this - copy paste. Put this over here. That's what I have in my database. I have an archive and I've got a delete button like this. All right, this one will be the delete button or delete. Let's call it delete notes, but DeleteNoteButton to keep everything singular.
Now in here, all this does is the last two things here. It doesn't archive, but it just deletes it. I like a little quick button to just delete stuff.
So now you could save that, come back out here. You got your little button, you got some stuff, and then click and it just goes away.
You could put an input box on here, a message box, are you sure in case it's accidental? There's a lot you could do.
Now I want to change these guys. These little button captions I got in here, they're not very friendly. So let's make these look better. Let's do right-click, properties, and we're going to find a picture right in here. Take a picture that you think looks archivy. I don't know, up, oh, looks fine, maybe down, throw it down, throw it right, is it okay? This guy, there is a delete picture, I think, as a delete. Let's see, where is it? Oh, little x. Yeah, there we go. And then there we go.
You can make these a little smaller, put them in the corners like so. There. Looks good. Save it. Close it.
Now, one problem you might run into: If this is null, nothing really happens. I think it's just going to fill up blank space in here. Let's see. Yeah, you're just getting blank records. So you can just either hide it if you want to not have that display or you can just check it in the button code.
Where's the button? Let's go back. You could say right in here: If IsNull(Notes) Then Exit Sub. And maybe when we're done, give them a beep. Put a beep down there. Save it.
All right, what do we got? If I click on it now, nothing happens. If I click on it here, type in some stuff, hit the button. Beautiful. Perfect.
And while you're typing, you can't see the buttons. That's okay. You don't usually want to click on the buttons while you're typing. You're not going to type something in and immediately just archive it right away. If you want, you can move these down here. You can do whatever you want. They're your Legos, play with them however you want to.
Now before someone puts it in the comments, yes, I'm aware there's an append only property where you can save a history of a field. I don't like it, I don't use it, and I don't recommend that you use it. See Why You Shouldn't Use It. I have a whole video on why you shouldn't use it and what the proper procedure is, which is basically what I do with the contacts. I put it in a separate table in a separate form.
This thing that I just showed you allows you to put the notes on the customer form, keep them here. So when someone else in the office opens this up, they see whatever the relevant information is for this client. But then later on, if this information changes, but you don't want to lose it, you can just archive it, see? And then type in something new.
Oh, that's another thing you might want to do. Once you archive it, if you're going to assume the user is going to type something else in new after that, move the focus. You click on this button, it's still sitting on the button. Wouldn't it be cool to put the focus back up here? I mean, it'll hide the buttons momentarily, but I think that might be handy.
So right in here, maybe after the beep: Notes.SetFocus. All these little controls or commands, you just have to learn them.
So blah, blah, blah, blah, blah, blah, come in here, click, bang, done, and it clears it. That's kind of cool. And you can do all kinds of stuff. You can do an OnCurrent event that hides these and then another event after this gets updated that shows them again, whatever. Again, there's so much you could do with this stuff. My job is just to teach you a little bit at a time and you can improve your databases as you go on.
If you like learning this stuff and you want to learn more with me, I have a whole series of developer classes. I have like 40-some hours' worth and counting. I still have lots and lots of topics to cover, but check them out. My developer classes, you'll find them on my website. There's a link right there.
If you want to learn more about SQL, I've got a three-part seminar series about learning the SQL language as it pertains to Microsoft Access specifically. Because if you know SQL, you can do a whole bunch of stuff inside your database. And if you decide later on to upgrade your backend to SQL Server, well, you already know the language. It's 90 percent the same as Access. There are a few minor differences and I cover them in my other seminars.
Even if you only plan on sticking with just Access for the rest of your career, it's still good to learn SQL instead of having to rely on queries for everything.
So that, folks, is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main purpose of the "archive notes" button added to the customer form?
A. To automatically save the current notes to a contact history table with one click
B. To delete all previous customer notes from the database
C. To permanently remove notes from the customer form
D. To export customer notes to an external file
Q2. Why is automating the archiving process with a button helpful?
A. It eliminates the need to manually copy, open another form, and paste
B. It allows notes to be automatically emailed to customers
C. It converts text notes into images for storage
D. It lets users change form colors quickly
Q3. In the video, which action follows after archiving the notes?
A. The notes field is cleared (set to null)
B. The archived notes are immediately printed
C. The customer is deleted from the database
D. The form is closed and reopened
Q4. Which two fields are required in the SQL INSERT statement to archive customer notes?
A. customer ID and description
B. notes and phone number
C. address and contact date
D. status and order total
Q5. What is the advantage of using CurrentDB.Execute over DoCmd.RunSQL in VBA?
A. CurrentDB.Execute does not display confirmation messages and is faster
B. DoCmd.RunSQL allows more complex SQL statements
C. DoCmd.RunSQL supports web-based queries only
D. CurrentDB.Execute can only delete records, not insert
Q6. How does the SQL INSERT statement dynamically obtain the values to archive?
A. By using values from the currently open form fields
B. By importing data from Excel
C. By querying an external API
D. By generating random placeholder data
Q7. When inserting text values like notes in SQL from VBA, what must you do?
A. Surround the value with double double quotes for proper string formatting
B. Surround the value with single quotes like in SQL Server
C. Omit any quotes around the value
D. Use square brackets around the value
Q8. What does the line Me.Dirty = False do after archiving the note?
A. Saves the current record
B. Deletes the current record
C. Checks for duplicate entries
D. Closes the form
Q9. Why should you avoid using the "append only" property for saving note history?
A. It is less flexible and harder to control than archiving in a separate table
B. It automatically deletes all old records
C. It makes the database unreadable
D. It encrypts the notes and makes them inaccessible
Q10. Why might you want to move focus back to the notes field after archiving?
A. So the user can immediately type in new information
B. To prepare for form printing
C. To hide the archive button
D. To export the new note to Excel
Q11. What simple check can prevent blank records from being archived?
A. If IsNull(Notes) Then Exit Sub
B. If Me.Dirty = True Then Close Form
C. If CustomerID < 0 Then RunSQL
D. If Notes.Length > 255 Then Exit Sub
Q12. What is the general workflow recommended in this video for managing notes history?
A. Keep current notes in the customer form, archive them to contacts when replaced
B. Store all notes in the customer form forever
C. Delete all old notes once new notes are added
D. Only use the append only field in the customer table
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 will show you how to archive customer notes from the customer form directly into the contact history in Microsoft Access. This process uses a straightforward VBA procedure along with a little SQL so you can save information with a single click.
Let me explain the situation. Often, you want to keep the latest notes about a customer visible on the customer form. But when that information gets outdated and you need to change it, you might not want to lose the old notes. The ideal solution is to move those notes to your contact history for future reference with as little manual effort as possible.
In the traditional approach, you would cut the data out of the notes field, open up the contact history form, and paste it in. This process takes several extra steps and is inefficient, especially if you need to do it often. My goal is to introduce a way for you to accomplish this with a single button click.
Access is designed for just this kind of automation. Saving small increments of time with each operation adds up very quickly, especially if you or your staff handle this task multiple times daily. Ten minutes saved each day translates into an entire work week over the course of a year.
This solution does expect a bit of comfort with Access VBA and some basic SQL. If you are new to VBA, I recommend first checking out my introductory video on the subject. Additionally, if you want a better grasp of SQL, I have a video explaining the basics which will help, since we are using an SQL append query in VBA for this archive operation.
Let's clarify the task. On the customer form, you have a notes field. When notes become outdated, you want to transfer them to the contact history table, which stores the customer's historical interactions. Specifically, you will archive the note along with the associated customer ID. In the contact history table, you can store this text as a "description" or in the notes field, depending on your design. Usually, short pieces of text are sufficient for this field.
For demonstration, I usually jot down the names of fields and control names in Notepad or a similar tool as I work, so I can keep track easily while moving between the form and the VBA editor. Over time, I have started using Google Docs in the browser for this, as it autosaves and prevents loss of work during power interruptions.
To get started with the archive feature, you add a button to your customer form, position it near the notes field, and give it a meaningful name, such as ArchiveBTN. This naming clarity comes in handy when writing the VBA code.
Writing the VBA for this process involves using CurrentDB.Execute to run an SQL INSERT INTO command. This allows you to add a new record to the contact history table using the current customer's ID and the note text. Because the customer ID is a number, it is inserted as-is. The notes field is text, so it needs to be enclosed in double quotes within the SQL statement. I prefer using double double quotes for text values in Access and recommend this approach for most situations.
After you execute the archive command, set the original notes field to null to clear it, and save the form record. Double-check your syntax by compiling, then return to your form to test. Now, pressing the archive button should move the text into your contact history and clear out the notes field.
You can also take this further by adding a companion button for deleting notes. This button would simply clear the notes field without archiving. Consider adding confirmation dialogs to prevent accidental deletions.
For a more polished look, you can replace button captions with icons or images that represent archiving or deleting. Adjust the layout and appearance to fit your own style and business requirements.
Be aware, if the notes field is empty when you click the archive button, you may end up with empty records in your history table. To prevent this, include a line in your VBA to exit the procedure if the notes field is null. Adding a friendly beep or shifting the focus back to the notes field after archiving can further streamline the user experience.
Occasionally, students mention Access's "append only" field property to maintain a history, but I do not recommend using that feature. I have a dedicated video discussing the reasons why a properly normalized design using a related history table is a better practice, as implemented here.
This setup preserves the most current note right on the customer form for easy access, while still letting you keep a record of previous notes or information. You can continually refine the solution by, for example, updating focus, hiding or showing buttons contextually, or tying additional automation into update events.
If you want to learn more about Access development, I offer a series of developer classes covering a wide range of topics. I also teach a seminar series on SQL as it applies specifically to Access, including how it prepares you for working with SQL Server in the future. Even if you plan to stick with Access, understanding SQL will let you write your own queries and give you greater control over your databases.
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 Archiving customer notes from a form to contact history
Creating a button to automate note archiving
Using VBA to execute an SQL INSERT INTO statement
Utilizing CurrentDB.Execute for SQL in VBA
Passing values from form fields into SQL statements
Handling text and numeric fields in SQL with double quotes
Clearing the notes field after archiving
Saving the record after making changes with Me.Dirty
Adding a delete notes button to remove notes quickly
Changing button captions to pictures for improved UI
Preventing archiving if notes field is empty with IsNull
Confirming actions with message boxes or beeps
Returning focus to the notes field after archiving
Customizing button placement and appearance on the form
Explaining limitations of the append only property
Storing archived notes in a separate table for history |
