|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Notes Anywhere! By Richard Rost 
4 years ago
Put Notes on Any Form in your Access Database In this video, I'm going to teach you how to put notes on any form in your Microsoft Access database using a little bit of VBA and SQL. Handy for notes on your menu forms, list forms, or anywhere else you want. Links
Members
Suggested Course
Instructions
Keywordsaccess 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, notes on any form, sticky notes, pop up notes, notepad, sql, vba, update query, long text, memo
IntroIn this video, I will show you how to add note fields anywhere you want in your Microsoft Access database, even on forms not bound to a table like your main menu. We will use a dedicated notes table, take advantage of the Tag property, and write simple VBA code using the DLookup function, SQL, and the AfterUpdate event to display and save notes. This tip is perfect for advanced users who want the flexibility to keep reminders or messages on any form in their database.TranscriptWelcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I am going to show you how to put notes anywhere. Put notes in a long text memo field on any form in your Microsoft Access database.It is easy to throw a notes field up for a form. It has data in it, right? Customer form, product form. That is just a long text field – drop a little box on the form and you are good to go. But what about places where you have a form that is not based on a table, like your main menu? Any other form in your database where you want to just keep notes. I like to keep notes all over the place. So, in this video, I will show you how to do that. Before we get started, today is for the more advanced users. You will need to know a little bit of VBA – not a lot, just a little bit. You should also know how the DLookup function works. You should know a little bit of SQL, what it is at least. I am going to show you the only command you need, and you should know what the AfterUpdate event is. Now, I have free videos explaining how to use all of these things, so if any of this stuff is unfamiliar, go watch all four of these videos. Watch them in that order. Well, the last two do not matter, but go watch those first. Then come back here and I will show you how to do the notes thing. Here I am in the TechHelp free template. This is the free database; you can download a copy off my website if you want to. Now, if you are in a customer record, like here I am, Richard Rost or James Kirk, you could easily put a notes field here. We know how to do that – watch my Intro to Access 101 class and you will figure out how to do this. But this is bound to a table. This information here gets saved in this customer table, and the notes field is over here somewhere. There it is, right there. That is easy. Now, what if you want to just drop a note somewhere, like on the main menu? I like to have little notes scattered throughout my database, depending on what the database is. Especially if you are sharing this database with other people, you can put a note right here on the main menu that says, for example, last date we shipped out orders was this, or last date we printed invoices and mailed them out was that. Stuff you do not want to formalize anywhere else in the database in a record, you can just drop a note right here. Or on your order form, you know, "86 the photon torpedoes," for example, or something like that, whatever. I have one, for example – I do a little stock trading now and then. I have one on my stock portfolio database. It is just a little note field that says the last date that I checked for dividends is whatever. I do not want to make an actual entry in the dividend table, so just drop it as a note. How do we do that? First thing is we are going to need a table to store all of our notes in. So let us make that first. Create, then Table Design. We do not need the property sheet to be that big. This will be our note table. NoteID – that will be our AutoNumber. Then NoteText, and that will be Long Text. That is it. That is all we need. Save it: NoteT. There we go. We will leave it blank. We have NoteID and NoteText. Normally I put some sample data in there, but we do not need to. Now, Design View, I am going to drop a notes box right here. I already have a nice notes box on my customer form, so let us just borrow this guy. Design View, click on that, copy it. Come over here, paste it in. Now, we do not want to bind this to a notes field. So go to your data tab over here and take the Control Source out of there. It is now unbound. That means it is not getting its data from a table. This form does not get its data from a table, right? There is no Record Source. So how are we going to get some data in here? In the OnLoad event for this form, we can go out to the note table, do a lookup for any information, and drop it into that box. Let us put a note in here first. It is kind of silly to do it without a note in there. Let us put some notes. Let us put the notes for the main menu. Notice this is NoteID 1, so we will use that NoteID 1. Every note box will have its own ID. Let us go to the properties here, go to Events. The OnLoad event or OnOpen – does not matter – click dot dot dot. That brings up our code builder. There it is. Let me resize this real quick. Get up there. In the Form_Load event, what are we going to do? We are going to say Notes (that is my text box) equals; we are going to look up, so DLookup. What are we looking up? The NoteText from the note table where the NoteID equals 1. Just like that. One more function too, just in case that is empty. We are going to wrap that inside of Nz (null to zero) and we are going to change that into an empty string. If you have never used Nz before, it is a cool little function. It says that if this guy returns a null value, I do not want an error message, because it will usually error out. Instead, just convert that with Nz into whatever values are over here, in this case, an empty string. I should have put that on this screen; I did not, I forgot. My bad. Go watch that if you need to. Let us go back over here. Save it. Close it. Open it back up again – main menu. I have it right up here. Boom! There it is: notes for main menu. Look at that. This form loaded; it went out to the table, found whatever was in there ("too much coffee today, folks!") and dropped it right there. Now, how do we save that back to the table when the user makes a change? If I come in here and type some stuff in, and then press Tab or leave that somehow or close the form, I want this to save it. We are going to use the AfterUpdate event. Right click, Design View. This is a little more complicated, but not too bad. Go to Event, AfterUpdate, dot dot dot. This is what happens after that field is updated. Here, we are going to execute an SQL statement. We are going to say CurrentDb.Execute. You can use DoCmd.RunSQL, but I like this better. What does it look like? UPDATE NoteT (that is the table) SET NoteText equal to – now, we have to put some stuff inside quotes, and normally you put it like this inside quotes: "abcd" like that, but we have to use double double quotes. There is another video for you to watch if you do not know how double double quotes work. I will put links to all these down below; you can click on them. I probably should have gone through this whole thing before. I did this as a quick video. So, double double quotes. This actually has to be double double quote, another double quote, and then concatenate on whatever is in that field, which is Notes, and open up the next string double quotes. Then we need our WHERE condition: WHERE NoteID equals 1. This says update the note table, set the NoteText equal to whatever is in the notes box inside quotes, where NoteID equals 1. That is all. If you want to use my little Status function, you could say "Note Updated." That just puts the words "Note Updated" in that little gray status box right there. If you watch my "blank" video, you will know what that is. Save this, close it. Come back in here: notes for main menu, yo. Hit Tab, and as soon as you do that, it is updated. See? If I close this and come back into it, look at that, it is saved right there! Is that not cool? Now, if you do not want to have to rely on having this "1" inside of your code, you can actually use the Tag property. Watch this. Design View, this guy here – almost every control, if you go to the Other tab here, has a property called Tag, and you can just use that for storing whatever extra kind of information you want. I am going to put a 1 in that box. Now I can refer to Notes.Tag – it is the Tag property. So watch this: come in here, change this to Notes.Tag in that, and then down here do the same thing: Notes.Tag. Now I can easily copy and paste that code without having to worry about changing the ID every time, as long as you name the box the same. You have to put the Form Load event and the AfterUpdate event. Save it. Close it. Come back in here. "Notes with tag." Close it, open back up again. Look at that! Is that not cute and cool? You learned something new. Let us drop another one. Let us put something on the customer list, maybe a note down here, saying something about "Make sure for the month of February to double credit limits," or whatever kind of note you want to have. Let us go to the note table first, and drop it in here just to make sure we get the right ID, because you might get a random ID sometimes. This will be "notes for customer list form." That is a 2, definitely 2. Now come back to here. Design View, copy this guy. Right click, Design View. Paste that in there. Change it up a little bit, maybe make it like that. Double click on this guy, change its Tag now to 2. Then we need two bits of code. Let us take this code out of the other one first. Where is it? Over here. We need the form load and the notes after update. I am going to take that and just drop it on my Notepad. Where is my Notepad? There is Notepad, trusty Notepad. We do not need the status stuff and I am not going to put the "note updated" in the next one, too. Back over here, we need to put something in the Form Load event. Go to Event, OnLoad right there. Let us copy it from the other form load. Copy and paste. Look – Notes.Tag – I do not have to change code, it is just going to read the Tag out of that notes box. Then the other thing is the AfterUpdate event. Event, AfterUpdate right there, and then we will copy it from Notepad and paste. Save it. Close it. Shut her down, shut this place down, Jon Taffer. Here we go. Ready? This one here works. Let us open up the customer list. More notes. Close it, come back in – look at that, saved! Now you can drop notes anywhere, any form in your database. You can drop notes. Just make sure that if you already have a field called Notes (this guy here), you cannot name that one Notes, too. Then you will have to change your code. But on any form, I usually put this on menu forms, put it on your main customer list form, for example. "Remember for the month of April, double credit limits" or whatever you want to put in there. And now that is saved. Next time I come in there, you will see it. "No more photon torpedoes this month; he destroyed an asteroid, Adam." Then you come back in the database next time, and there are your notes, and they are all saved nicely in your notes table. Sweet! If you liked this lesson, there is lots more to learn. I just scratched the surface – there is tons of stuff you can do with Access. Check out my developer lessons. I know this only goes up to 30 on here; I am up to 36 right now. There are tons and tons and tons of advanced developer-level VBA, SQL, all that stuff on my website. Check it out: AccessLearningZone.com. I will see you next time! Hope you learned something. How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will get one free expert class each month after you finish the beginner series. Platinum members get all the previous perks, plus even higher priority for TechHelp questions. Access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more. You can now become a Diamond sponsor and have your name or company name listed on a sponsors page. They will be shown in each video as long as you are a sponsor. You will get a shout out in the video and a link to your website or product in the text below the video and on my website. But do not worry. These free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more and they will always be free. QuizQ1. What is the main goal of the video tutorial?A. How to format text in Access reports B. How to add notes anywhere in any Microsoft Access database form C. How to print customer invoices in Access D. How to create a customer table Q2. In the context of this video, what kind of table is created to store note data? A. A summary table linked to each form B. A record history table for customers C. A universal NoteT table with NoteID and NoteText fields D. A table for each individual form requiring notes Q3. What field type is used for NoteText in the NoteT table? A. Short Text B. Yes/No C. Number D. Long Text Q4. What should you do to add a note field on a form that is NOT bound to a table (e.g., a main menu)? A. Use a bound field linked to a generic notes table B. Drop a standard notes field and bind it to any available table C. Place an unbound text box and use code to load/save data D. Use embedded form comments Q5. Which VBA function is used to bring in the note text from the NoteT table into the unbound notes box when a form loads? A. DCount B. MsgBox C. DLookup D. DSum Q6. What is the role of the Nz function when loading note data into the form? A. It counts note records B. It ensures a null value does not cause errors by converting it to an empty string C. It deletes null note records D. It formats number data Q7. When a note is updated on the form, how is the data saved back into the NoteT table? A. Manually typing in the table B. Using the AfterUpdate event to execute an SQL UPDATE statement C. Through a built-in Access save function D. Only on form close event Q8. Why is the Tag property of the notes textbox used in this approach? A. To store the user's name B. To set the color of the textbox C. To store the NoteID for dynamic code reuse D. To save the database name Q9. What is a consequence of naming multiple controls Notes on a form or across forms when using this approach? A. Access automatically links them together B. You get syntax errors in SQL statements C. Your code could break due to duplicate control names D. You can display multiple notes at once Q10. What must be done if you want to place a notes box on another form for a different note? A. Use the same Tag value as the other notes box B. Place a new notes box, assign a unique Tag (NoteID), and reuse/copy the Form_Load and AfterUpdate code C. Change the name of the NoteT table D. Use conditional formatting Answers: 1-B; 2-C; 3-D; 4-C; 5-C; 6-B; 7-B; 8-C; 9-C; 10-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. SummaryToday's video from Access Learning Zone shows you how to add notes anywhere in your Microsoft Access database, using a long text memo field on any form. Traditionally, it is simple to include a notes field on a form that is bound to a table, like a customer or product form. You just set up a long text field, place it on the form, and you are ready. But sometimes you want to place a notes area on a form that is not connected to a table, such as your main menu or a custom navigation form. I personally like to keep notes in lots of places throughout my databases, especially when working with others. You might want to drop a note on the main menu to record the last time orders were shipped out, or when invoices were last printed. Sometimes you just need an informal reminder that does not fit anywhere else in your data structure. This is also great on forms like order forms where you want to make a quick note for future reference.For example, in my stock portfolio database, I have a note that records the last date I checked for dividends. There is no need to add a record to the dividend table for that - a simple note field is perfect. To set this up, you will need to be comfortable with a little bit of VBA programming, the DLookup function, and basic knowledge of SQL. You also should be familiar with the AfterUpdate event. If these concepts are new to you, I have free video lessons for each one. I recommend watching those first to get an understanding, then come back to this lesson. I demonstrate this process in the free TechHelp template, which you can download from my website. On a standard customer form, for instance, you would simply connect a notes field directly to the table and it is straightforward. The notes are saved to the customer table and are easy to display, edit, and retrieve. The challenge comes when you want to add a notes box to a form that is unbound, that is, not connected to any table. The main menu of my template is a good example of this. To accomplish this, you first need to create a table that will store all of your notes. I start by creating a table called NoteT. It has just two fields: NoteID (an AutoNumber primary key) and NoteText (a Long Text field). You only need these two fields to keep things simple. After setting up your note table, you can place an unbound text box on any form. For convenience, I like to copy an existing notes box from another form, remove its Control Source so it is not bound to any field, and place it wherever I want. Since the form is unbound, we have to manually load and save the note text. When the form opens, I use the OnLoad event to run some VBA code that uses the DLookup function. This function retrieves the note text from the NoteT table for a specific NoteID. We place that value in the unbound notes box. To handle cases where there is no note yet stored (so DLookup returns Null), the Nz function is used to safely assign an empty string in that scenario and prevent errors. Once you have the note loaded, you also need to save any changes the user makes back to the table. This is handled in the AfterUpdate event of the text box. Here, you write a SQL UPDATE statement and execute it with CurrentDb.Execute, updating the appropriate note record in NoteT with the new value from the text box. To make the code more flexible, especially if you have more than one notes box on different forms, you can use the Tag property of the text box to store the NoteID. Almost all controls in Access forms have this property under the "Other" tab. By setting the Tag property to match the NoteID in your NoteT table, you can reuse the same code on different forms, each referencing their correct note record by referencing the Tag property in your code. This approach makes it easy to copy and paste your notes box and associated code onto any form throughout your database. If you want to add a note for another form, such as a customer list, simply insert a new record into the NoteT table, set your text box's Tag property to that new NoteID, then copy the code for OnLoad and AfterUpdate, ensuring the notes get loaded and saved correctly for that form. One important tip: be sure that if your form already has a control or field named Notes, you will need to name this notes box something else to avoid conflicts in your code. This technique lets you drop a notes field anywhere in your Access database, whether on unbound menu forms or other places where you do not want to create extra tables or fields. It is especially useful for reminders, shared database notes, or any quick bits of information you want to recall later. All of these notes are safely stored in one simple table. If you are interested in learning more, there is a lot more you can do with Access beyond this lesson. Check out the developer lessons and advanced courses on my website for more tips about VBA, SQL, and other developer-level topics. 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 ListCreating a notes table for unbound formsDesigning a notes table with AutoNumber and Long Text Adding an unbound text box for notes to a form Loading note data into an unbound text box using DLookup Using the Nz function to handle null values in DLookup Writing VBA code in the Form_Load event to display notes Saving updated notes back to the notes table with SQL Using the AfterUpdate event for real-time saving of notes Composing an SQL UPDATE statement in VBA Handling quotes and string concatenation in SQL within VBA Utilizing the Tag property to identify note locations dynamically Copying and reusing code to add notes to multiple forms Assigning unique IDs to notes using the Tag property Implementing notes on both menu and list forms Ensuring unique control and field names to avoid conflicts Saving and retrieving persistent notes across database sessions |
||||||||||||||||||||
|
| |||
| Keywords: FastTips Access notes on any form, sticky notes, pop up notes, notepad, sql, vba, update query, long text, memo PermaLink Put Notes Anywhere in Microsoft Access |
