|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Add Records By Richard Rost 
6 years ago
Add Records for Followup Dates Automatically Learn how to automatically add multiple new records to a continuous form in Microsoft Access with the GoToRecord command. We will create a button that will add followup appointments at 5, 10, 30, and 90 day intervals. Desmond from Arlington, Virginia (a Silver Member) asks: I am managing a doctor's office. I need to set followup dates for patients who have come in for testing. The first followup date has to be 5 days after their appointment. The next 10 days, then 30 days, then 90 days. Is there any way Access can add these dates to my followups automatically? Geir Arne from Norway (a Gold Member) asks: I have a single table that I need to automatically fill in values when I click a button. I have boxes with alphanumeric labels such as FE30001, FE30002, etc. and each of those boxes have positions such as A1, A2, A3, etc. Is this possible? MembersI'll show you how to create new records in a similar fashion, but this time we will use FOR loops to add 64 new records with specialized criteria.
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! LinksInputBox: https://599cd.com/inputbox
IntroIn this video, I will show you how to automatically add future follow-up records to your Microsoft Access database by clicking a button using the GoToRecord command. We'll work through creating a form button that generates multiple follow-up appointments at customizable intervals, updates the correct fields, and ties everything to the right customer. You'll see how to build a follow-up query to track upcoming tasks, set default dates, and mark records appropriately for easy management. This tutorial provides step-by-step instructions for automating the process of scheduling future activities in your Access contact management database.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. My name is Richard Rost, your instructor, and in today's video I'm going to show you how to add records automatically to your databases by clicking a button using the GoToRecord command.Today we actually have a double question. Part 1 comes from Desmond in Arlington, Virginia, a silver member. He says, "I'm managing a doctor's office. I need to set follow-up dates for patients who have come in for testing. The first follow-up date has to be five days after their appointment, the next 10 days, then 30 days, then 90 days. Is there any way that Access can add these dates to my follow-ups automatically?" Yes, there certainly is. I'll show you how to do that in just a few seconds. The follow-up, which is a very similar question, comes from Gerrarn in Norway, a gold member. He says, "I have a single table that I need to automatically fill in values when I click a button. I have boxes with alphanumeric labels such as FE, 300, 01, 02, and so on, and each of those boxes have positions such as A1, A2, A3, and so on. Is this possible?" Yes, this certainly is possible. This will involve a little more work. We'll have to do some looping and changing values from alphanumeric values such as that FE300, 01, which is a little more difficult than just adding numbers. We'll do this in the extended cut for members. But right now, let's see how we can add these automatic follow-ups to our contacts table. I'm going to start with my blank customer contact database. You can download a free copy of this from my website. I'll put a link down below in the description. In this database, this is my starting database. I've got a basic customer form where you can see all the customer's information. I've got a customer list where you can click on a customer and open up their particular record. Inside of that, I've got the contact history, and we can use this contact history for future follow-ups as well. When we built this template, we added this little checkbox down here so that we can specify follow-ups. We really didn't do much with it as far as making a list of follow-ups. So for example, let's say this came in for an interview here. I checked that I want to follow up. Where's my list of follow-ups? We can create a query, create query design, to make our list of follow-ups. Show me all of the contacts, and I also want to add in the customer information to link together here. Bring in the contact ID, the contact date, the description, and you can bring in the notes if you want to. Where follow-up is equal to true. I'll save this as my follow-up queue. This is my follow-up query. Now, to get a list of follow-ups, all I have to do is run the follow-up query, and there it is. There's my follow-up date, the description, and I can add in the customer information too if I want to. Who's the customer? Well, first name, last name, and maybe you want phone number. Save that, close it, run the query. There are all your follow-ups. You can sort them by date. You can do whatever you want to. You could take this query and pull it into a form or print it out in a report. I've got lots of different videos on how to do all that stuff. This video is to show you how to add these follow-ups automatically. I want to add a date that's in the future. Today's 12/13. I can just simply click on this little button here. I can make a date for five days in the future. One, two, three, four, five. First follow-up. Now make sure you check the box down here for a follow-up. Now when I close this and come and run the follow-up queue, there's my first follow-up, and it's on 12/18. You could put a criteria in here so you can only see follow-ups that are today or in the past so you don't see all your future follow-ups. There are all kinds of things you can do. Now how do I get the system to automatically put those follow-ups in for me? Let's get rid of that. I just realized one of the things that I did in the template was in my continuous forms, I set it so that Allowed Deletions is off. We're going to have to change that real quick if you're using the template. I'm going to change this in the template actually. This is the older version of the template. In my contact form here, in design, I have to go into the properties and set Allow Deletions to true. All right, so let's change that now. Let's get rid of that first follow-up, delete. I set it that way because usually when you have a customer list, you don't want someone to be able to delete customers out here. If I hit delete, nothing happens. In order to delete that customer, you want them to have to actually open up the customer and delete it here. So let's make a little button down here that creates those follow-ups for us automatically. I want to put a little button right there that simply goes up here, adds a record five days in the future, goes to a new record, adds another one ten days in the future, and so on. Let's start with just one. We'll put a button right here. This is easier to do while this is not a subform. So let's just go back out and open up the contact form directly in design view. This is much easier to do like this. Grab a button, drop it here. Now the wizard starts up, cancel the wizard. This is a little more advanced. Add future follow-ups. This will add our future follow-ups for us. I'll slide this button over just a little bit like that. Let's give the button a good name instead of Command7. Let's call this addFollowUpsBTN (button). Right click on it, go to build event. You may be asked what builder you want. Pick the code builder. Now right inside here is what happens when we click on that button. What do I want to do? I want to go up here to the contact date field and go to a new record. The first thing I'm going to do is say DoCmd.GoToControl. There it is. What is the control name? The control name is contact date up there on top in the other form. I want to move from the button up to contact date. Now I want to go to a blank new record. So DoCmd.GoToRecord. What kind of object are we dealing with? acDataForm. Comma. What's the object name? The name of the form is always going to be Me.Name. Just put that in there. So no matter what form you're on, it'll always go to that form's controls. You can go to control and go to a record on a different form if you want to. That's why that option is available. But usually you want Me.Name. Which record do you want? We want acNewRec. You can go to first, last, next record, previous. We want to go to a new record. Now I'm on the contact date field sitting on a new record. The first follow-up is going to be five days in the future. So contactdate equals today's date plus five days. That's five days in the future. If you want to specify a different date for the start date, you can. You can simply ask them. We can use an input box if you want to. Right up top here, we could say Dim startDate As Date. We'll need a string to put it in. So Dim s As String. We'll say s = InputBox("Enter appointment date", "Enter Date"). Now if they hit cancel or they don't enter anything, I'm going to say If s = "" Then Exit Sub. They didn't give me anything. But they typed in a date. Now I'm going to say startDate = CDate(s). That says convert that string into a date value. If you want to check for crazy dates here, you can. You could say if the start date is less than a year ago or if it's more than a year in the future, whatever you want to do, there are all kinds of things you can do in here. I'm just going to leave it at that. We're going to assume that our people typing the data in are putting in valid dates. If you want to add date checking, do it here. And if you're not sure how, send me an email and I'll point you to the right videos that cover it. Now we'll just change this from being today's date to the startDate plus 5. Remember, in Access, one day is a value of 1, so plus 5 is five days in the future. In fact, we can put a default date in the input box, comma, default value, put the date in there, today's date. Or if you normally enter in yesterday's appointments in the system, go Date - 1. Or if it's tomorrow, Date + 1. Days are easy. Weeks are easy. It's always sevens. Months are a little tougher because months can be 28, 30, 29, 31. For that you have to use the DateAdd function. I've got videos on DateAdd. For this particular person, Desmond wants 30 days and 90 days. That's easy. That's already plus 90. But if you wanted two months to the actual calendar date, then you have to use DateAdd and add months. If you've never done any Visual Basic programming, I cover it in my Developer 1 class. I also have some free Intro to VBA, which is available on my website and on YouTube. If you've never done any programming before, go watch that video. I'll put links down below. I usually mention that when I start, but I forgot to today. Default to today's date for the "Enter Appointment Date." We're going to go to Control-ContactDate, go to a new record, put that in there. What do we want to put in the description? We'll put in there: Description = "Follow Up," and we'll put "Appointment Follow-up number one," just like that. Let's check this to make sure it works. Let's come over here and open up the contact form and hit "Add Future Follow-Ups." Put it in OK. There it goes, and it works great. The problem is, when it's a standalone form, it works just fine, but it doesn't put the customer ID in there because we're not linked to the customer form since it's a subform. If I do this from inside the subform, "Add Future Follow-Ups," we get a problem here. ContactF isn't open because we're trying to go directly to ContactF. This is one of those rare situations where I'm actually going to tell you not to put these values in here. Just go comma, comma. What that says, it will use the default object that you're on and the default object name, which is the form that we're currently in. Otherwise, you have to refer to it as Forms!CustomerF!SubformName.Form, and it becomes complicated. But this will actually work here. I wanted to show you the right way first. This is the shortcut way. It says just go to a new record on whatever I happen to be sitting on. Now that I've shown you that, I can hit this button, hit OK, and it will actually work. See that? But the proper way to do it is to put the actual values in there. Now let's add in the other records. We've got the next one here, which is going to be 10 days. So we're going to just basically copy this stuff. Control+C and then paste it down here. Control+V. We're going to go to another new record. ContactDate now equals startDate plus 10. Appointment follow-up two. And then another one. What was it? Plus 30. Follow-up three. Oops, that's four or three. Then the final one was 90 days. That's follow-up four. When we're all done, we're going to go to a new record so we're sitting on a blank new record when we're finished. Let's save that. Let's come back out here. Let's clear this old one, delete. Ready to add the future follow-ups? Click the appointment date. Let's put an appointment date in here of 12-1. Then hit OK. Boom. There you go. Five days, 10 days, 30 days, 90 days. There's your appointment follow-ups. One thing we forgot to do is, notice, we didn't mark them as follow-ups. So let's set that too. That's another field we forgot to put in there. That field is, let's go to the contact table. What's that called? That's called just "FollowUp." In here, we need FollowUp = true for each of these. So we'll copy this. Copy. Paste. Paste. And paste. Save it. Come back into here. Ready? Go. There we go. And they're all marked as follow-ups, and now they'll show up on our follow-up queue. See? There they are. So that is the quick and dirty way to automatically add future records with a little bit of VB code. Now in the extended cut, we're going to take this a little bit further, and we're going to have to do some looping because Gerrarn wants to do the same thing basically, but he's using fields that have alphanumeric values. For example, FE3, 0001. We have to separate this out into a prefix and a suffix, then add to that suffix value. Then he wants separate subrecords. They're going to be A1, A2, A3, and so on. We'll see how to do that in the extended cut. Here's what it looks like. We've got a little bit of work to do for the members in the extended cut. We have to determine what the next box number is. If one doesn't exist, we have to create it. It always is in that format: FE3 and the number, 0001, then the next one is 0002, and so on. We'll have to use the DMax function to figure that out. We'll have to split the prefix and the suffix, and we're going to add that value together. Then we'll have to create nested for loops for the position, one through eight, and then A through H. In order to do that, we're going to learn about ASCII codes and the Chr function. Lots to do in this members-only extended cut. How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks. But don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free. QuizQ1. What is the main feature demonstrated in this video?A. How to generate reports in Access B. How to automatically add follow-up records when clicking a button C. How to create a login form in Access D. How to secure your Access database Q2. What Access command does the video use to navigate records programmatically? A. DoCmd.OpenForm B. DoCmd.RunSQL C. DoCmd.GoToRecord D. DoCmd.Close Q3. In Desmond's scenario, what pattern are the follow-up dates based on? A. Every day for a week B. 5 days, 10 days, 30 days, 90 days after the appointment date C. Every month on the first D. Randomly spaced intervals Q4. What type of form element is added to allow users to trigger the creation of follow-up records? A. Textbox B. Label C. Combo box D. Button Q5. To set the follow-up dates, the script uses which field as the starting point? A. Description B. StartDate (entered by user via InputBox) C. CustomerName D. Status Q6. In the example, how is user input for appointment date handled? A. Using a combo box B. Hardcoding the date in code C. Using an InputBox to let the user enter a date D. Automatically using today's date without confirmation Q7. What does the video recommend setting in the Contact form properties to allow record deletions? A. Allow Additions to True B. Allow Edits to False C. Allow Deletions to True D. Allow Filters to False Q8. What happens if you do not mark the 'FollowUp' field as true when adding follow-up records? A. The follow-ups will always show in the query B. The records will not appear in the follow-up queue C. The contact will be deleted D. An error message is shown Q9. What is the suggested way to ensure the correct object is referenced when using GoToRecord in a subform? A. Specify Forms!CustomerF!SubformName.Form explicitly B. Always reference the form as CustomerF C. Use commas to let Access use the current form context D. Reference the database table name Q10. When computing the follow-up dates, why would you use the DateAdd function? A. To subtract months from the date B. To handle weeks and days only C. Because months have variable lengths, unlike days D. To format dates as text Q11. What programming language is used for the automation demonstrated in the video? A. C# B. JavaScript C. Visual Basic for Applications (VBA) D. PowerShell Q12. What is the purpose of creating a query called 'follow-up queue' in the video? A. To manage security B. To display all follow-up appointments pending for customers C. To back up the data D. To import records from Excel Q13. In the upcoming extended cut, what more complex task will be demonstrated? A. Creating a report from scratch B. Looping through and incrementing alphanumeric box numbers and positions C. Adding images to records D. Integrating Access with SQL Server Q14. What additional topic does the extended cut plan to cover for box IDs with alphanumeric values? A. Importing JSON data B. Handling only numeric IDs C. Splitting prefix and suffix, and incrementing the suffix D. Sending emails with follow-up data Answers: 1-B; 2-C; 3-B; 4-D; 5-B; 6-C; 7-C; 8-B; 9-C; 10-C; 11-C; 12-B; 13-B; 14-C 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 covers how to add records automatically to your Access database by pressing a button, using the GoToRecord command.This session actually addresses two questions. Desmond, who manages a doctor's office, needs to set automatic follow-up dates for patients who have come in for testing. The series of follow-ups is at 5 days, 10 days, 30 days, and 90 days after an appointment. He wants to know if Access can automatically add these follow-up records. The answer is yes, and I will demonstrate how to do this. The second question, from Gerrarn in Norway, is very similar. He has a table where he needs to automatically fill in fields when a button is clicked. His boxes are labeled with alphanumeric codes like FE, 300, 01, 02, and so on, and each box has positions like A1, A2, A3, etc. He asks if there is a way to fill these values in automatically. This is also possible, though it requires a more advanced approach involving loops and managing those alphanumeric values. I will save that discussion for the Extended Cut for members. For now, let's focus on adding automatic follow-up records to a table. To begin, I'm starting with a blank customer contact database, which you can download from my website. This database includes a simple customer form containing all customer information, a customer list you can select from, and a contact history. The contact history table will be used for tracking future follow-ups as well. The template has a checkbox for designating a record as a follow-up, but until now, we haven't built out a system to actually list follow-ups. To address this, create a query that pulls in all contact records and links each to its customer. In the query, bring in fields like ContactID, ContactDate, Description, and Notes if desired. Add the customer details too, such as first name, last name, and phone number. Set a criteria so that only records where "FollowUp" is true are shown. Save this as your follow-up queue query. Running this query will display all upcoming or pending follow-ups. The results can be sorted, displayed in a form, or used in a report. Now, the main goal is to allow the system to automatically add these scheduled follow-up dates. To do this, you can create a button that, when clicked, adds records for 5, 10, 30, and 90 days in the future. One note: if you're using my template, be aware that in continuous forms I previously set the Allow Deletions property to off. This prevents deleting records from a list, which is useful for customer lists, but in this context we need to allow deletions. So update the form properties to allow deletions before proceeding. Now, create a button on the contact form labeled "Add Future Follow-Ups." Assign it a clear name, like "addFollowUpsBTN." When this button is clicked, the procedure will move the focus to the Contact Date control, go to a new (blank) record, and start the process of adding follow-up entries. If you want to prompt the user for an appointment date to use as the initial reference, you can do so with an input box. Make sure to validate the input to ensure a valid date is entered. You can even provide a default, such as today's date. Each follow-up record will use this entered date, adding the appropriate number of days for each interval—5, 10, 30, and 90. In Access, dates work with basic arithmetic, so adding days is straightforward. For each follow-up entry, set the date appropriately, fill in the description (such as "Appointment Follow-Up 1"), and make sure to check the follow-up box so that the records appear in your follow-up queue. Be aware that if you trigger this button from a subform, the context can affect which fields are available or what records are created. In those cases, referring to the correct forms and subforms is important, but you can use shortcuts with the GoToRecord command to ensure it works in both standalone forms and subforms. The process can be repeated for each required interval, and at the end, you can have the form reset itself with a new blank record. Try it out: after removing any existing follow-up entries, run the process and you should see all four follow-up dates created for the selected customer or patient, each properly marked as a follow-up. These will now show up in the follow-up queue and be easy to manage. This approach provides a quick way to automate adding future follow-ups or scheduled appointments with a bit of VBA code. Also, in today's Extended Cut, I will cover Gerrarn's scenario, which is a bit more complicated. In that situation, you need to deal with alphanumeric codes, separating prefixes and suffixes, and incrementing box numbers appropriately. That requires using functions like DMax, splitting strings, and setting up nested loops for various positions, such as A1 through H8. You'll also learn about using ASCII codes and the Chr function. All of this is covered in more detail for members in the Extended Cut. If you are interested in watching the Extended Cut, membership information and additional perks are available on my website. Silver members and above get access to all Extended Cut TechHelp videos, plus live video and chat sessions. Even if you choose not to become a member, you can count on more free TechHelp videos in the future. 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 follow-up query for future appointmentsFiltering contacts based on the follow-up checkbox Displaying follow-ups in a query with customer info Sorting and printing follow-up queries Adding a command button to auto-create follow-ups VBA code to set Allow Deletions on a form Using DoCmd.GoToControl to move focus in a form Using DoCmd.GoToRecord to add new records Prompting the user for a start date using InputBox Parsing and validating date input in VBA Assigning future dates using date arithmetic in Access Populating multiple follow-up records automatically Auto-filling contact descriptions for each follow-up Correctly marking new records as follow-ups Setting field values in VBA when adding records in forms Defaulting InputBox to today's date Handling subform vs. standalone form record creation Testing and verifying automatic follow-up record creation |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access gotorecord, gotocontrol, add records, automatically add records, followup dates, followup appointments, intervals, patient followups, follow ups, follow-ups, auto followups, action plan PermaLink Add Records Automatically in Microsoft Access |
