|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Welcome Welcome! Progress Bar, Payment, Record Count Welcome to Microsoft Access Developer Level 22. In this course we will focus on working with recordsets, including using the tag property, applying lump sum payments to multiple orders in sequence, and adding a numeric counter to records in subforms with automatic renumbering. We will discuss using a progress bar to show record completion, applying payments across several orders, and implementing record counting with DCount and manual reordering. Finally, we will revisit record counting forms to replace commands with recordsets, and address issues when deleting multiple records using the SelHeight property. NavigationKeywordsAccess Developer, recordsets, tag property, progress bar form, apply payments to orders, lump sum payment multiple orders, record counter, auto numbering subforms, DCount function, conditional formatting forms, SelHeight property, manual row reordering, c
More InformationTranscriptWelcome to Microsoft Access Developer Level 22 brought to you by AccessTurningZone.com. I am your instructor Richard Rost. Today's class is going to focus more on recordsets as we have been doing in our developer series. We are going to look at the tag property. We will see how to apply payments to orders if you get a lump sum payment. You want to apply it to multiple orders starting with the oldest. We will take a look at counting records so you can have a numeric count of the records in your subforms: 1, 2, 3, 4, and so on. All of these will make good use of recordsets. I will be using Microsoft 365, but these lessons will work for any version of Access from 2007 up to 2019, the current one. This class follows Access Developer 21. I strongly recommend you have taken that class first, as well as all the developer classes from 16 on. Those cover recordsets. If you have questions, just scroll down to the bottom of the page and post them there. Also, take a second and check through all the other questions that might have been posted because your question might have already been answered. If you want to get notifications when new questions and answers are posted, click on the subscribe link. If you have questions unrelated to this class, feel free to post them in the Access Forum. Now let's take a quick look at what is covered in each lesson. In lesson 1, we are going to learn how to use a progress bar to display how much of a single record in a form is completed. Then we will put all the records together in a continuous form and show how completed each one of those records is, and we will jazz it up with some conditional formatting. In lesson 2, we are going to learn how to apply payments to orders. We are going to make an order table. We have an amount due and amount that has been paid and whether that order is paid or not. We have a balance on each order. A user places three orders worth $100 each, so he owes you $300. He comes in and then makes a $250 payment, not quite paying all of them. So we have to use a recordset, loop through the orders from the oldest to the newest, apply the amount that is due to the first one, the amount to the second one, and if there is any left over, the amount to the third one. We are going to cover that in lesson 2. In lesson 3, we are going to set up a record counter. So whether you have products, contacts, customers, or whatever, we can have Access automatically number the rows for us: one, two, three, four. If we delete a record, it will renumber it. If we add a new one, it will renumber it. We will learn about auto-ordering with the DCount property and DCount function, and it will allow manual reordering of the numbers as well. So you can go in there, type it in, and change it. We will put little buttons on the bottom to go up and down and renumber them that way. In lesson 4, we are going to take the same form that we used in lesson 3 to do the automatic numbering and replace the programming underneath it to use recordsets instead of using the go-to record command. We will also see an issue that comes up when deleting multiple records and how to deal with it with the SelHeight property. IntroWelcome to Microsoft Access Developer Level 22. In this course we will focus on working with recordsets, including using the tag property, applying lump sum payments to multiple orders in sequence, and adding a numeric counter to records in subforms with automatic renumbering. We will discuss using a progress bar to show record completion, applying payments across several orders, and implementing record counting with DCount and manual reordering. Finally, we will revisit record counting forms to replace commands with recordsets, and address issues when deleting multiple records using the SelHeight property. QuizQ1. What property is used to display how much of a single record in a form is completed? A. Progress Bar B. Recordset C. DCount D. SelHeight Q2. When applying a lump sum payment to multiple orders, which method is recommended in the video? A. Apply payment to the newest order first B. Split payment equally among all orders C. Apply payment to the oldest order first, then the next, and so on D. Apply payment to just one order Q3. What tool is used to automatically number rows in a subform as described in lesson 3? A. DCount function B. Progress Bar C. Tag property D. SelHeight property Q4. What happens to the auto-numbered rows when a record is deleted? A. The numbers do not change B. They are renumbered C. They are replaced with null values D. A new record is automatically added Q5. Which Access versions does the material in this class support? A. Only Access 2019 B. Any version from 2007 up to 2019, including Microsoft 365 C. Only Access 2010 and later D. Only Microsoft 365 Q6. What is the recommended prerequisite before taking this class? A. None required B. Completing any developer class from 16 on, especially Developer 21 C. Only basic Access skills D. Experience with SQL Server Q7. In lesson 4, what property is discussed to help address issues when deleting multiple records? A. Progress Bar B. DCount C. Tag property D. SelHeight property Q8. What is the main focus of this class according to the introduction? A. Designing user interfaces B. Managing users C. Working with recordsets D. Setting up security profiles Answers: 1-A; 2-C; 3-A; 4-B; 5-B; 6-B; 7-D; 8-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 is Microsoft Access Developer Level 22, where we continue working with recordsets and focus on a few advanced topics. I'm Richard Rost, your instructor for this course. In this class, I'll guide you through working with the tag property, a handy feature that lets you store hidden information about each control or record. We will also look at a practical scenario: applying a lump sum payment to multiple outstanding orders, starting from the oldest order and working forward. You'll see how recordsets enable us to allocate payments properly across several records. Another important topic I'll cover is counting and numbering records in subforms. This allows us to display a sequential numeric count, such as 1, 2, 3, 4, beside each record. This can be especially useful for forms that show lists like invoices, products, or customers. All these examples make extensive use of recordsets, so a good understanding of them is assumed. I'm working with Microsoft 365 for these tutorials, but everything shown here is compatible with Access 2007 up through Access 2019. This course picks up right after Access Developer 21. I strongly suggest you've completed that class, as well as Developer 16 and newer, as those cover foundational Recordset concepts that you will need. If you have questions about anything I cover, I recommend looking through the Q&A section at the bottom of the course page. Many questions have already been answered, so reading there may save you time. You can also subscribe for notifications when new questions and answers are posted. If your question is about a different Access topic, feel free to post it in the general Access Forum. Let me give you a quick overview of what's in each lesson of this class. In lesson 1, you'll learn how to use a progress bar control in a form to visually display the percentage of a record that's completed. I'll also show you how to set this up to display completion progress for each record in a continuous form, and we'll add some visual style using conditional formatting so completed items really stand out. Lesson 2 addresses a real-world scenario: applying payments to multiple orders. We'll set up an orders table with fields for amount due, amount paid, and calculate the balance. You'll see how, when a customer makes a payment that does not quite cover all his outstanding orders, a recordset loop can allocate his payment across his oldest unpaid orders first, then move to the next, and so forth. Lesson 3 is all about keeping track of the order and count of your records. I'll show you how to automatically assign sequential numbers to your records using DCount. Any time you add or delete a record, Access will renumber them. You can also manually reorder the sequence by entering a new number or by using up and down buttons to adjust the order. In lesson 4, we will revisit the form from lesson 3 and refactor the code so that it uses recordsets for navigation and numbering, instead of relying on built-in go-to record commands. I'll also point out some special challenges, such as what happens when you delete multiple records at once, and how you can use the SelHeight property to manage issues that come up. If you are ready to take your Access development skills up a level, this class will give you practical tools and techniques you can use in your own 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 ListUsing the Tag property in Access forms Creating a progress bar for record completion Applying conditional formatting to progress bars Applying lump sum payments to multiple orders Looping through orders by oldest to newest Calculating and displaying order balances Automatically numbering records in subforms Renumbering records after deletion or addition Using DCount for auto-ordering records Enabling manual reordering of records with buttons Replacing go-to record actions with recordsets Handling deletion of multiple records with SelHeight Primary Topics
Access recordsets, applying payments to orders, progress bar on forms, auto-numbering subform records, DCount function, conditional formatting, record navigation Secondary Topics
tag property, SelHeight property, manual record reordering |
||
|
| |||
| Keywords: Access Developer, recordsets, tag property, progress bar form, apply payments to orders, lump sum payment multiple orders, record counter, auto numbering subforms, DCount function, conditional formatting forms, SelHeight property, manual row reordering, c PermaLink How To Use Recordsets, Progress Bars, Payment Application, and Record Counting in Microsoft Access |
