|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Fast Form Update By Richard Rost 
5 years ago
Fast Form Update. Quickly Change Records in a Continuous Form In this video you will learn how to quickly assign values to records in a continuous form and then automatically move to the next record with the GoToRecord acNext command. Just a tiny bit of VBA is involved. I'll show you step by step. Santiago from Rio de Janeiro, Brazil (a Platinum Member) asks: Every day I get a list of jobs that I have to assign technicians to. There is no rhyme or reason to it, so I can't make an update query to handle the task. There's no real "criteria" for who gets it. I just look at a brief description of what the job entails and I know which tech to assign it to based on their skills, the customer, etc. I have four techs and right now it's a lot of copy and paste. I just go down the list and copy and paste, copy and paste. Can this be simplified at all? Even if I could just have a button for each of my three techs, that would make things so much easier for me. Click a button and it assigns the tech and moves to the next record. MembersWe'll add copy and paste buttons, as well as a button to copy the current value to all of the records, and to all of the records from the current position to the end of the list.
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! LinksRelational Database: https://599cd.com/Relationship
IntroIn this video, I'll show you how to quickly update records in a Microsoft Access form by assigning technicians to jobs with the click of a button. We'll talk about setting up job and technician tables, designing a continuous form for job assignments, and adding buttons for each technician to streamline the process. I'll also explain how to use combo boxes for technician selection and demonstrate a simple VBA subroutine to make the button code more efficient. This practical approach can help you speed up daily tech assignments right from your Access database.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I'm going to show you how to quickly update records in a form to assign a technician to a job by simply clicking a button that's got their name on it. For example, if you've got three different technicians, you just go click, click, click, and you can assign technicians that way as it goes right down the list for you, nice and fast. Today's question comes from Santiago from Rio de Janeiro, Brazil, a Platinum member. It's a bit of a specific question, but since Santiago is a Platinum member, we'll let him ask pretty much whatever he wants. That's the benefit of membership. He says, "Every day, I get a list of jobs that I have to assign technicians to. There is no rhyme or reason to it, so I can't make an update query to handle the task. There's no real criteria for who gets it. I just look at a brief description of what the job entails and I know which tech to assign it to based on their skills, the customer, etc. I have three techs and right now it's a lot of copy and paste. I just go down the list and copy and paste, copy and paste. Can this be simplified at all? Even if I could just have a button for each of my three techs, that would make things so much easier for me. Click a button, it assigns a tech, and moves on to the next record." Let's tackle this. Let's start with a copy of my blank customer database template. You can find a copy of this on my website. There's a link down in the description below the video. Feel free to download it if you want to, or you can do this in your own database. So you've got jobs and technicians. Let's make two tables real quick. Let's create a job table: job ID, auto number. Then you'll have a technician that's assigned to it, so a tech ID. Now, if you're using text in your table, if it's not relational, you can still do the same thing; you can still just copy and paste text. Although I suggest you make it relational with a second table. If you've never done relational databases before, I've got videos on that. I'll put a link down below; go watch that and then come back to this. In here, you can put any other kind of stuff you want related to the job: the customer ID, a description, the job date, or whatever other information you want. You might have a custom job number that the client gives you, an address, or other stuff about the job. Save this as jobT, my job table. The other fields are irrelevant for this conversation. Now let's make a tech table. Create table design: a tech ID (auto number), and we'll just do a first name. You can put all the other stuff about your technician in here. Save that as my techT. Save changes. Let's put a few technicians in here. Go to datasheet view. I'll put in myself, Sue, Jim, and Jean-Luc. Actually, you only have three technicians, so I'll stick to three. That'll make it easy. Close that. Save changes. Let's put a few jobs in the system now. Now, the default ID is zero. It doesn't matter if it's zero or null; you can leave it as zero. That's not going to hurt you. In here you've got a bunch of descriptions. I'm not sure what kind of business you're in or what kind of jobs you have to deal with, but I'll just do what I'm familiar with: the computer industry. I'll put, "replace motherboard," "clean out computer" - they get dusty. I used to have a service that I offered way back in the day in the 90s, when I did computer service. I used to offer something called PC Doctor, where we would take your computer apart and blow all the dust out of it. People would go months without cleaning out their computers. If you have an old desktop computer at home, take the case off once a month, get a can of air, and just blow all the dust out of it. You'll thank me later. I'll add "defragment hard drive," "take dog for a walk," "read a book," "drive a car," and "eat some soup." Now we've got some jobs in here. Now, I want to quickly go down this list and say: give that one to me, give that one to Bill, give that one to Jim, give that one to Sue. Let's use my continuous form template here. I'm going to copy this. Actually, we can just use the customer list. Let's copy it and paste. Let's call this one my jobF, my job form. This is the blank template. I'm going to close the main menu for now; we don't need it. Now, we just have to assign the jobF to the job table, so we've bound it with the Record Source. Save it. You can either set these guys or just delete them and put the fields you want in there. Go to design, add existing fields. What fields do we want? We don't need the job ID. We're going to make a combo box for the tech ID. All we need is the description. You could bring in job data, but we don't really need it. So just bring in technician for now. If you want to bring in job data and other stuff, that's fine, but all we need is the description here. This is the label, "Description." We'll put that first over here. Let's make a combo box over here so we can pick the tech. Go to design; open this to stay open. Double-click on it. Let's find the combo box, drop that there. I want the combo box to find values in a table/query. If you've never done combo boxes before, I've got videos on that too. I'll put a combo box link down below. We're getting the values from our techT. That's the value list. What fields do I want? I need the ID; that's the bound field. Bring over the first name next so you can see it in the box. Sort by first name. That's what it's going to look like. The key column is hidden. I'm going to store that value in the tech ID. We're going to pick a tech and store it in the tech ID. Finish. I can delete that label right there. Slide it over here. There's my list of techs, just like that. Save it. Close it up. Open it back up again. So right now, the old way you can do this: If your technicians all have one different first letter, this could be as easy as just entering "R". See that? If you take this out of the tab stop, watch. Design view. If this has already been entered - let's say you're importing this or someone else is typing in. If this list here - double-click on this guy - and go to "Other" and make it so it's not in the tab stop, it doesn't stop when you hit the tab key. This might work for you. You might just be able to go J, S, S, J. Who reads the car? Rick. Drives the car. See? That's pretty fast, if that's all you need to do. Now I'm assuming you're probably using a text field. You're probably typing all this in. Go do what I just showed you with the technician. Make that a combo box. Let me clear these values out of here, because I'm going to show you another way to do this. You want little buttons down here, you can click on, and it just assigns it. Here's how you could do that. Design view. It could actually be slower than the other technique. I typically don't like doing stuff like this because this doesn't foster a relational database design, because I'm basically assigning a specific button to a specific tech. It's fine if you've only got three, but what happens if you have 16? Normally, I would suggest maybe a drop-down box here with a button; you can add, add, add that way. Since you only have three, we'll make three little buttons down here. Cancel the wizard. Put the name of the tech in here, like Rick. Here's a trick: again, if they're all unique, you can go ampersand Rick, like that. See how the R now has the little underscore underneath it? That means that if you hit Alt+R, it'll push that button. You have to know what your techs are. Your techs are Rick, Sue, and Jim: one, two, and three. So here's what we're going to do. Right-click, Build Event. That'll bring up your VBA editor. I forgot to name my button. I don't want Alex yelling at me. Let's name the button first. Open this up and we'll call this RickButton. Again, I have to emphasize that this is specific to your database. It's not good relational design to do this, but it's what you want, so I'm going to show you how to do it. Right-click, Build Event. So this is the RickButton. What happens when I click the RickButton? In this field - actually, I have to name this box for us too, so it's Combo5. Let's change this; let's call this the TechCombo. TechCombo, and it's bound to TechID. Back to here. We're going to say the TechCombo equals 1. That's Rick. Like that. Then I want to go to the next record. Set it to Rick and then move to the next record. So, DoCmd.GoToControl, and it was the control name TechCombo. Then DoCmd.GoToRecord, comma, comma - ignore those first options, they're just to use the defaults. Then go to next, acNext. Just like that. That's it. Now you just make two more buttons: you have Sue and Jim, or just copy and paste. It's yelling at you because you have the Alt key assigned to different people. We'll go Sue there and Jim there. So we just have to come in here and go Sue, and then Jim. We'll call this the SueButton and this one the JimButton. Very quick. Build Event. We're just going to copy this and make it for 2 (that's Sue). Right-click, Build Event. Paste, 3, and that's Jim. Let's move this down here. Save it. For all of you experts watching, yes, I'm aware this is not proper relational database development, but sometimes, just in a pinch, you want something to work a certain way. It's not going to kill anybody. Rick, Rick, Rick. Sue, Sue, Jim, Jim. See? Jim, Jim, Jim. Sue, Sue. Now if you are using text fields here - I'm using combo boxes so I can just send a number - if this TechCombo was a tech string instead, just change this to Rick, like that. If that's what you got going on, which I suspect it might be. But since I used combo boxes, I can just put a 1 in there. I'm going to show you one more little cool thing too. You can make your own subroutine. This is getting a little more advanced, but I'll teach you a little more VB programming here. VB is great. Let's say you want to get rid of some of this duplicated code. Watch this: Private Sub ChangeTech (TechID As Long) TechCombo = TechID DoCmd.GoToControl "TechCombo" DoCmd.GoToRecord , , acNext End Sub Now, down here, all I have to say is ChangeTech 1. That's Rick. You can put Rick after it, too. Get rid of all that other stuff. For Sue, just ChangeTech 2. For Jim: ChangeTech 3. No more duplicated code. You click this button; it says ChangeTech 1; comes into here, sends it a 1; TechCombo equals 1 and does all that stuff. Same for the others. Works the same way, but now there's no duplicated code. Let's try it. Jim, Jim, Jim. Sue, Sue. Rick, Rick. You can add new records this way too. If you don't want to add new records, go to Data, Allow Additions: No. Allow Deletions: No. That way, you can't accidentally delete records off this form. If you're importing this somewhere else. Now if I just keep it in Sue. Oh, yeah. Can't go to the specified record, because we've gone past the last one. Debug it. You can throw "On Error Resume Next" up there. That'll ignore any errors. That's the simple and cheap way to get past that error. There are better error-handling techniques, but that's fine. I hope that answers your question. Again, I think it might be faster just to go RRR. You can use those Alt keys too. I think it's better for the tab; because here you'd have to hit R Tab, R Tab, R Tab. With those shortcut keys, you could go Alt+J, J, J, just hold the Alt key down - J, S, R, R. That's a little faster with the buttons. Answer your question, Santiago. Hope that helps. 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, my 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 was the main problem Santiago wanted to solve?A. Automating report generation for technicians B. Assigning technicians to jobs quickly and easily C. Backing up technician records to another database D. Sorting jobs by completion date Q2. What database structure is recommended for storing technicians and jobs? A. A single table with all details as text fields B. Multiple spreadsheets for different technicians C. A relational database with a job table and a technician table D. Using only forms with no tables Q3. What was suggested instead of copying and pasting technician names manually? A. An update query to assign all jobs to a single technician B. Using a combo box in a form to pick a technician for each job C. Importing technician assignments from Excel D. Sending an email to each technician for every job Q4. What is the purpose of a combo box in the job assignment form? A. To filter jobs by customer B. To select and assign a technician to a job C. To display job dates only D. To automate job completion Q5. Why is using specific buttons for each technician not considered good relational database design? A. It is too complex to implement B. It makes the database too slow C. It is not scalable if the number of technicians changes D. It prevents data from being saved Q6. How was keyboard accessibility improved for technician buttons? A. By giving each button a unique background color B. By setting the ampersand before the technician's name in the button text C. By disabling shortcut keys for all buttons D. By making the buttons invisible Q7. What did the instructor do to avoid duplicating code for each technician button in VBA? A. Used a separate module for each technician B. Created a subroutine called ChangeTech that takes a TechID as a parameter C. Copied and pasted the same code three times D. Removed all VBA and used macros only Q8. What does the subroutine ChangeTech do? A. Deletes the selected technician from the database B. Sets the TechCombo value to the given TechID and moves to the next record C. Sends an email notification to the assigned technician D. Logs out the current user Q9. What is one quick way to speed up technician assignment using just the keyboard, as suggested in the video? A. Enter the technician's initial in the combo box and use the Tab key B. Export the list to Excel for quick assignment C. Use mouse clicks only D. Print the list and write assignments by hand Q10. What simple error handling was added when moving to the next record at the end of the form? A. On Error Resume Next B. Select Case statement C. If...Then...Else statement D. Try...Catch block Answers: 1-B; 2-C; 3-B; 4-B; 5-C; 6-B; 7-B; 8-B; 9-A; 10-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. SummaryToday's video from Access Learning Zone shows how to streamline the process of assigning technicians to jobs in Microsoft Access using a form with quick selection buttons. If you regularly receive a list of jobs that need to be distributed among a small group of technicians, this method can speed up your workflow and reduce repetitive tasks like copy and paste.The problem addressed here came from a viewer who has three technicians and no set rules for job assignments. He usually picks which technician is best suited for each job, but currently relies heavily on manually entering or copying and pasting names which is time-consuming. His idea was to have a button for each technician on the form, allowing him to assign jobs quickly by just clicking the appropriate button as he moves down the list. To set this up, start with two tables in your database. One table will store the job information, including fields for job ID (set to auto number), tech ID, description, and any other details such as customer ID or job date. The second table will list the technicians, with fields for tech ID (also set to auto number) and first name, along with any other necessary information. After setting up the tables and entering a few sample technicians and jobs, create a continuous form based on your jobs table. Instead of entering technician names manually, add a combo box to select the technician by name. This simplifies data entry and helps prevent errors. The combo box should pull its list of values from your technicians table and should be bound to the tech ID field in your jobs table. At this point, the simplest way to assign technicians is by selecting a name from the combo box as you work down the list. If each technician's name starts with a different letter, you can enter the first letter to speed things up even further. However, to go a step beyond that and implement the original request for buttons, you can add a button for each technician directly to the form. When one of these buttons is clicked, it sets the tech ID for the current job record and then moves to the next record in the list for faster assignment. Make sure to name the buttons appropriately, such as RickButton, SueButton, and JimButton, and use VBA code to handle updating the field and moving to the next record. While this button-based approach works well for a small number of technicians, it isn't considered best practice if your team grows or if you want a more flexible design in the future. Using a combo box is generally a better relational database approach, but for three technicians, having dedicated buttons can be a real time-saver. For those familiar with VBA, you can streamline your code by creating a single subroutine that takes the tech ID as a parameter and reuses the same logic for each button, reducing duplication and making your code easier to maintain. If your field stores technician names as text instead of ID numbers, simply adjust the code to assign the technician name string instead of a number. You can further customize your form by disabling the ability to add or delete records if you want to prevent accidental changes. For error handling, a simple "On Error Resume Next" statement will skip over any issues such as trying to advance past the last record, though more robust error handling is always recommended for production databases. This approach gives you a fast and flexible way to assign jobs without repetitive typing or the risk of data entry errors. For even more speed, consider using keyboard shortcuts assigned to your buttons. 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 jobT and techT tablesAdding sample technician records to techT Adding sample job records to jobT Building a continuous form for jobs Binding the job form to jobT as its record source Adding a combo box for selecting technicians Configuring the combo box to display techT records Customizing the form to show job descriptions Setting up tab stops for quick entry Clearing assigned technicians from jobs Adding command buttons for each technician Naming buttons and controls appropriately Writing VBA code to assign a technician via button click Navigating to the next record after assignment Adapting button code for both numeric and text TechCombo Refactoring button code with a ChangeTech subroutine Implementing basic error handling for navigation Disabling additions and deletions in the form when needed |
||||
|
| |||
| Keywords: TechHelp Access update records, quick update, fast update, GoToRecord, acNext, jobs, technicians, sales reps, copy, paste, gotofirst, while loop PermaLink Fast Form Update in Microsoft Access |
