Welcome

Developer 22 continues our study of Recordsets. We will learn about the Tag property, how to apply a single large payment to multiple orders, and how to order records consecutively in a subform, getting a record count (1, 2, 3, etc.)

Resources

Topics Covered

In Lesson 1, we will learn about the Tag property and how to loop through the controls on a form using a For Each loop. We will calculate the percentage complete that a specific record is based on what fields have data. We will display that on a progress bar at the top of the form. Then we will show that percentage on a continuous form with all records, and jazz it up with some conditional formatting. This is BONUS material, meaning the whole 35 minute lesson does not count towards your hour for this class.

In Lesson 2, we will learn how to apply payments to orders. Here's the setup: you've got an order table with an order total, and amount paid. If you've got a customer who has, let's say 3 open orders and make a single payment, you want to automatically apply that amount to each of the orders (starting with the oldest) until there's no money left. So if he owes $100 on three orders and pays you $250, the database will pay off the first two orders and put $50 on the last one. We'll use a recordset for this.

In Lesson 3, we're going to set up a record counter. Whether you've got products, customers, products, or any type of data, we can have Access automatically number the rows for us (1, 2, 3, etc.). If we delete a record or add a new one, Access will automatically update the list. We'll learn how to auto-order with the DCount function. We will then set up manual ordering, so you can put the items in any order you like. We'll add buttons to move items up and down the list.

In Lesson 4, we're going to take the same form we used in lesson 3 and redo all of the VB code to use Recordsets instead of the GoToRecord command. We'll learn how to deal with an issue that comes up when deleting multiple records, and we'll learn about the Me.SelHeight property.

Enroll Today

Enroll now so that you can watch these lessons, learn with us, post questions, and more.

Questions?

Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.

Subscribe to Access Developer 22
Get notifications when this page is updated

Intro

In lesson 22 of my Microsoft Access Developer series, you will learn more advanced techniques for working with record sets, including how to use the tag property, apply lump sum payments to multiple orders starting with the oldest, and create automatic record counters that update as records are added or deleted. I will also show you how to enhance your forms with progress bars and conditional formatting, and demonstrate how to handle record numbering and updates using DCount and record sets. This lesson builds on concepts from previous developer classes and is designed for anyone looking to deepen their Access skills.

Transcript

Welcome 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 want 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 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 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 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 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 an amount that has been paid, and whether that order is paid or not. So we have a balance on each order.

For example, your 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. We have to use a record set loop to go 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 re-number it. If we add a new one, it will re-number it. We will learn about auto-ordering with the DCount property and DCount function, and it will allow manual re-ordering of the numbers as well. So 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 re-number 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 SelHeight property.

Quiz

Q1. What is the main focus of Microsoft Access Developer Level 22?
A. Advanced table design
B. Query optimization techniques
C. Record sets and their uses
D. Report formatting

Q2. Which property will be explored for tracking progress on a form in lesson 1?
A. Caption property
B. Tag property
C. Conditional Format property
D. Default Value property

Q3. When applying lump sum payments to orders, what important principle is followed?
A. Apply to the newest order first
B. Divide the payment equally among orders
C. Apply to the oldest orders first
D. Randomly assign payments to any orders

Q4. How is record numbering managed in lesson 3?
A. Using the AutoNumber field type
B. With the DCount function and manual buttons
C. Via the Sorting and Grouping pane
D. By renaming the primary key

Q5. Which Microsoft Access versions are compatible with the lessons from this class?
A. Only Access 2019
B. Only Microsoft 365
C. Any version from 2007 to 2019 and 365
D. Only Access 2010 and up

Q6. In lesson 4, what programming change is made under the automatic numbering form?
A. Replacing macros with queries
B. Using record sets instead of GoToRecord
C. Removing row numbering completely
D. Switching from VBA to SQL

Q7. What issue does SelHeight help address?
A. Coloring alternate rows
B. Deleting multiple records at once
C. Importing data from Excel
D. Validating user input

Q8. What is a suggested step before posting a new question about the course?
A. Wait for a weekly Q&A session
B. Email the instructor directly
C. Check previous questions and answers
D. Post to a third-party Access forum

Q9. If a record is deleted from the list with numbering, what happens to the remaining numbers?
A. They stay the same
B. They randomize
C. They renumber automatically
D. They reset to zero

Q10. What will conditional formatting be used for in lesson 1?
A. Changing background color on weekends
B. Jazzing up the progress bar display
C. Formatting currency fields
D. Highlighting cell borders

Answers: 1-C; 2-B; 3-C; 4-B; 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.

Summary

Today's video from Access Learning Zone focuses on continuing our exploration of record sets in Microsoft Access. The main topics in this class include using the tag property, applying lump sum payments to multiple orders, and setting up automatic record counters for subforms. Each of these tasks provides practical experience working with record sets.

For this course, I am using Microsoft 365, but the concepts and examples I show here will work with any version of Access from 2007 through 2019. This class is the next in line after Developer Level 21, and I recommend that you have completed Developer Levels 16 through 21 before starting this one because they cover the foundational concepts behind record sets.

If you have any questions as you work through the material, please scroll down to the bottom of the page and add your question in the comments section. I also encourage you to review the existing questions because yours may have already been answered. To stay up-to-date with ongoing discussions, you can subscribe for notifications of new questions and responses. For anything unrelated to this specific class, you can post your inquiry in the Access Forum.

Now, let me break down the topics we will cover in each lesson.

Lesson 1 starts with learning to create a progress bar that visually displays how much of a single record in a form has been completed. After setting this up for individual records, I will show you how to place this progress bar in a continuous form view to track the status of multiple records at once. We will also use some conditional formatting to enhance the appearance and make the data easier to read.

Lesson 2 focuses on processing payments for orders, particularly when a user makes a single payment intended to cover multiple outstanding orders, starting with the oldest. In this lesson, I will walk you through setting up an order table that keeps track of amount due, amount paid, and the balance for each order. Then, I will demonstrate how to use record sets to loop through the orders, applying the payment to each one in turn, and ensuring that any remaining balance continues to the next order in the sequence.

In lesson 3, the focus shifts to adding an automatic record counter. This functionality adds a row number in your subforms, whether you are displaying products, customers, contacts, or any other type of record. The numbers update automatically if you add or delete records, and you can reorder them manually as well. We will use the DCount function for this, and I will show you how to add buttons below the form to move records up or down and adjust their order accordingly.

Lesson 4 builds on the work done in lesson 3. Instead of using the GoToRecord command to manage the order of records, we will modify the programming to use record sets. I will also point out a particular issue that can arise when deleting multiple records at once and show you how to handle this scenario by working with the SelHeight property.

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 List

Using a progress bar to display record completion
Displaying multiple progress bars in a continuous form
Applying conditional formatting to progress bars
Creating an orders table with amount due and paid fields
Applying lump sum payments to multiple orders by oldest first
Using record set loops to distribute payments across orders
Setting up an automatic record counter in a form
Renumbering records after addition or deletion
Manual reordering of records with up and down buttons
Using the DCount function for dynamic ordering
Replacing GoToRecord with record set programming
Handling deletion of multiple records with SelHeight property