|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Access Developer 22 Lessons Welcome to Microsoft Access Developer 22. Total running time is 2 hours, 51 minutes, of which 73 minutes is free bonus material. Lessons
Database FilesResources
Questions?Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you! Subscribe for UpdatesIf you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.
IntroIn lesson 22 of my Microsoft Access Developer series, we will focus on working with record sets, using the tag property, applying payments to multiple orders by distributing lump sum payments starting with the oldest, and automatically numbering records in subforms. You will learn how to create a progress bar for form records, manage order payments with balances, set up an auto-numbering system that updates when records are added or deleted, and replace certain actions with more efficient record set code. These topics build on techniques from previous developer lessons, especially those covering record sets.TranscriptWelcome 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 record sets, as we have been doing in our developer series. We are going to look at the tag property. We are going to see how to apply payments to orders if you get a lump sum payment. You will learn how to apply it to multiple orders, starting with the oldest. We will also 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 topics will make good use of record sets. I will be using Microsoft 365, but these lessons will work for any version of Access from 2007 up to 2019, the current version. 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, since those cover record sets. 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'll 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 will have an amount due and an amount that has been paid, and whether that order is paid or not. So we will have a balance on each order. For example, if a user places three orders worth $100 each, 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 record set to loop through the orders from the oldest to the newest, apply the amount that is due to the first one, then 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. You can go in there and 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 record sets instead of using the GoToRecord command. We will also see an issue that comes up when deleting multiple records and how to deal with it with the CellHeight property. QuizQ1. What is the primary focus of Microsoft Access Developer Level 22?A. Using queries for data analysis B. Printing database reports C. Working with record sets, the tag property, and applying payments to orders D. Importing data from Excel Q2. Which Microsoft Access versions are these lessons compatible with? A. Only Access 365 B. Access 2019 and later C. Access 2007 through 2019 and 365 D. Only Access 2010 Q3. In lesson 1, what feature is used to show how much of a record is completed? A. Data validation rules B. Progress bar with conditional formatting C. Lookup fields D. Subquery execution Q4. How does lesson 2 suggest handling lump sum payments to multiple orders? A. Distribute payment equally to all orders B. Apply payment starting with the newest orders first C. Apply payment starting with the oldest orders first using record sets D. Ignore partial payments Q5. What functionality is added in lesson 3 regarding subform records? A. Color coding each row B. Automatic numbering and manual renumbering of rows C. Emailing each record D. Locking records from editing Q6. What function is discussed for tracking the count of records in forms? A. DCount function B. Avg function C. Sum function D. Max function Q7. In lesson 4, what programming change is discussed for working with records? A. Using GoToRecord for all actions B. Switching from record sets to SQL C. Replacing GoToRecord with record sets for better control D. Removing all programming and using macros only Q8. What issue is mentioned in lesson 4 when deleting multiple records? A. Form closing unexpectedly B. The CellHeight property problem C. Loss of formatting D. Errors with the DLookup function Answers: 1-C; 2-C; 3-B; 4-C; 5-B; 6-A; 7-C; 8-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 continues our Microsoft Access Developer series, specifically Developer Level 22. In this class, we will focus further on record sets and explore more advanced uses for them. Record sets have been a recurring topic in our previous developer lessons, and today we will build on that foundation.One of the primary features we will examine is the Tag property, which can be quite powerful when you need to store extra information about a control without adding new fields to your tables. I will also demonstrate how to handle situations where you receive a lump sum payment that needs to be applied to multiple orders. The method I'll show you will make sure payments are applied to the oldest unpaid orders first, moving forward chronologically. Another important topic we will cover is how to implement a way to count and display records in your subforms. This will give each record a clear numeric count such as 1, 2, 3, and so on, which can be very useful for navigation and organization. All of these lessons rely on a solid understanding of record sets. Although I will be using Microsoft 365 in my examples, everything I teach here will work in all major versions of Access from 2007 through 2019. This class picks up directly after Access Developer 21. I highly suggest that you complete Developer Level 21, as well as at least Levels 16 through 20, since those classes introduce and reinforce the core record set concepts you'll need for today's material. If you have any questions about the material covered in this class, you can post them at the bottom of the relevant page on the website. I also encourage you to read through previous questions and answers, as your concerns might have already been addressed there. For those who would like to stay updated, there is an option to subscribe so you'll be notified whenever new questions or answers are posted. For questions not related to this particular lesson, please visit and post in the Access Forum. Let me break down what you can expect from each lesson in this class: In lesson 1, I will guide you through using a progress bar in a form to visually indicate how much of a single record is completed. We'll also extend this concept by presenting all of the records together in a continuous form, so you can view the progress status of each record at a glance. Conditional formatting will be added for a little extra polish and usability. Lesson 2 will focus on the process of applying payments to orders using a lump sum payment. I'll demonstrate how to set up an order table that tracks total amount, amount paid, and remaining balance. We will look at a scenario where a customer has multiple outstanding orders and makes a partial payment. You will learn how to write logic to automatically allocate their payment, starting with the oldest order and working forward, until the payment is used up. In lesson 3, we turn to creating an automated record counter. This is useful whether you are managing products, customers, or any other records in a continuous form. You'll see how to automatically number each row and have those numbers update when you add or delete records. I will show you how to use the DCount function for generating these numbers, and how to add manual controls to reorder them as needed. Lesson 4 revisits the record counter form from lesson 3, but this time we will change the underlying code to use record sets rather than the GoToRecord command. We'll also address an issue that sometimes occurs when deleting multiple records at once, and I'll show you how to resolve it by using the CellHeight property. You can find a complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListUsing the tag property in Access formsApplying lump sum payments to multiple orders Looping through orders to allocate payments Tracking order balance, amount due, and paid Creating and using a progress bar on forms Displaying record completion with conditional formatting Building an order table with payment status Implementing a record counter for subforms Auto numbering records using DCount Manual reordering of records with up and down buttons Renumbering after add or delete operations Replacing GoToRecord with record sets for navigation Handling multiple record deletions with CellHeight property |
||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: access developer 22 lessons PermaLink How To Use Tag Property, Apply Payments to Orders, and Count Records with Recordsets in Microsoft Access |
