Welcome

Developer 18 continues our coverage of Recordsets. We will also learn how to build several different types of Progress Bars. See below for details, or watch the intro video to the right.

Resources

Topics Covered

In Lesson 1, we will learn how to use the Microsoft ProgressBar ActiveX Control that comes with Access. I don't like using external ActiveX controls however (for reasons I'll cover in class). So, I'll show you how to create a simple progress bar using a text box.

In Lesson 2, we will create a cool-looking 3D progress bar with shadowing, the percent complete, and an ABORT button to cancel the loop.

In Lesson 3, we will use the 3D progress bar, and create a loop to add 10,000 records to a table. We will test the loop using both and SQL INSERT command and a RecordSet and see which one is faster, and by how much. Spoiler: it's the RecordSet. However, we will use this opportunity to calculate the speed at which records are processed, show the total seconds elapsed, the records per second, and the total time remaining. This is very handy to show your users during long processes. I use it for my Access email server so I know that a batch is going to take 3 days, 14 hours, for example.

In Lesson 4, we are going to learn how to edit and add new records to a table using a recordset. We will create an unbound form with some unbound controls. Make our own buttons to navigate through the records using recordset commands. Make an edit button to put us in "edit mode" and an "add" button to add new records. We'll add a "save" button to save the record to the table. And of course, we'll make our own "close" button to close the form. This gives us complete control over everything that the user does with the data.

In Lesson 5, we will learn how to delete records and sort the recordset.

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 18
Get notifications when this page is updated

Intro

In lesson 18, you will learn how to create various progress bars in Microsoft Access, including a text-only version and a custom 3D effect using text boxes and shadowing, as well as using the built-in ActiveX control. We will discuss how to display status updates during long record set loops, calculate elapsed time, records per second, and estimate completion time. You will also learn how to create an unbound form with custom navigation, add, edit, delete, and sort functionality, and work with record sets to manage and manipulate data efficiently.

Transcript

Welcome to Microsoft Access Developer Level 18, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we are going to work more with record sets. First, we are going to build some different progress bars. We are going to build a progress bar that is text-only. We will use the ActiveX control that comes with Microsoft Access, although I personally do not like it. But then I will show you how to make some other cool progress bars, as you can see there on the screen.

Of course, my goal is to always use Access-only components whenever possible. So we will use a couple of text boxes with some shadowing effects. Then we will learn how to use these progress bars to display a status when our record set loops are running, because sometimes you can have a record set loop that takes forever, and it is nice to know how long you are going to take. I will show you how to calculate seconds elapsed, how many records are being processed per second, and how many seconds are left before the record set loop is over.

Now we have done a lot with looping through record sets and accessing data. In this class, we are going to learn how to add, edit, and delete data, and how to sort data. We will make our own little unbound form. It is not bound to a table or query. We will make our own set of navigation buttons, an add button, delete, edit, close, a sort button, and so on. We will load up the records as we need them from a record set, and we will learn how to manipulate them accordingly.

Lesson 1 will take a look at the ActiveX control from Microsoft, and we will learn how to build a text-based progress bar control without it.

In lesson 2, we will make that fancy looking 3D progress bar with a couple of text boxes and some shadowing effects. We will also make an abort button, so your user can cancel the record set loop while it is running.

In lesson 3, we are going to attach our record sets to the progress bar, and I will teach you how to perform all those timing operations.

In lesson 4, we are going to create that unbound customer form. We will learn how to move around through the records. We will get a record count. The Move Previous command, which we have not covered yet. I believe we covered Move First already and Move Last, then we will go into how to edit, add new, and update records.

Finally, in lesson 5, we are going to cover deleting and sorting records.

I will be using a Microsoft 365 subscription. You will be fine if you have 2019. I believe this code should work all the way back to 2007. In fact, most of it should run all the way back even to 2003. But I would recommend being in at least 2016 or later.

As far as my courses are concerned, you should be familiar with all the beginner material, Expert 1 and 2, at least for the relationship material, Developer 1 covers VBA basics, Developer 13 and on for the latest in what I am covering, and Developer 16, 17, and of course this is 18, cover record sets. So at least make sure you are familiar with 16 and 17 before taking this class.

So let's sit back, relax, and enjoy Developer 18.

Quiz

Q1. What is one of the main topics covered in this class?
A. Creating web forms in Access
B. Building different types of progress bars
C. Integrating Access with Excel
D. Importing data from external sources

Q2. Why does the instructor prefer Access-only components over the ActiveX control?
A. ActiveX controls are outdated and unavailable
B. Access-only components are more customizable and reliable
C. ActiveX controls cannot be installed in newer Access versions
D. Access-only components support macros better

Q3. What useful information can be displayed with the progress bars built in this class?
A. Database design structure
B. Number of users connected
C. Seconds elapsed, records processed per second, and estimated seconds left
D. List of available tables

Q4. What is the purpose of creating an unbound form in this course?
A. To link directly to an external database
B. To create a static data entry form
C. To fully control loading and manipulating records with VBA
D. To use built-in Access navigation buttons

Q5. Which of the following is NOT mentioned as a feature on the custom unbound form?
A. Navigation buttons
B. Add, delete, and edit buttons
C. Built-in Access search bar
D. Sort button

Q6. What special function is added alongside the progress bar in lesson 2?
A. Data validation
B. Abort button to cancel the record set loop
C. Import data functionality
D. Automatic email alerts

Q7. Why is it recommended to be familiar with Developer 16 and 17 before taking this class?
A. They cover essential Access security topics
B. They introduce the basics of SQL queries
C. They cover essential concepts related to record sets used in this course
D. They teach form design techniques

Q8. What command related to navigation within a record set is specifically covered for the first time in lesson 4?
A. Move First
B. Move Last
C. Move Next
D. Move Previous

Q9. What Access versions are recommended for best compatibility with the code in this class?
A. Only Access 2003
B. Only Access 2013 and newer
C. Access 2016 or later, but most code works with 2007 and newer
D. Only the latest Microsoft 365

Q10. What are the final operations covered in lesson 5?
A. Importing and exporting data
B. Creating complex reports
C. Deleting and sorting records
D. Connecting to SQL Server

Answers: 1-B; 2-B; 3-C; 4-C; 5-C; 6-B; 7-C; 8-D; 9-C; 10-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.

Summary

Today's video from Access Learning Zone focuses on some advanced techniques in Microsoft Access Developer Level 18. In this lesson, we will continue our work with record sets, expanding on what we have learned in previous sessions.

To start, I will demonstrate the creation of different types of progress bars. We will begin with a simple text-only progress bar and also explore the built-in ActiveX control provided with Microsoft Access. Although I am not a fan of the ActiveX progress bar, I want to show it for completeness. I will also introduce some more appealing progress bars crafted entirely using standard Access controls, such as text boxes with shadow effects. I always aim to use Access-native tools whenever possible, so these custom progress bars will fit seamlessly into your projects.

We will make use of these progress bars to monitor the status of processes that run in loops over record sets, which can sometimes be quite lengthy. I will explain how to calculate the number of seconds elapsed, determine the number of records processed per second, and estimate the time remaining until completion.

Beyond tracking progress, this class will move deeper into manipulating data through record sets. We will cover how to add, edit, delete, and sort records. To demonstrate these skills, I will build an unbound form–one that is not tied directly to a table or query–so you can see how to handle data manually. I will guide you through creating your own navigation buttons, as well as add, delete, edit, close, and sort buttons. We will see how to load records from a record set as you need them, and how to interact with this data using your controls.

Lesson 1 will introduce the ActiveX Microsoft progress bar control and show you how to create a purely text-based progress bar without it.

In lesson 2, we will switch gears and design a polished, three-dimensional progress bar using only a couple of text boxes and some shadowing effects. I will also show you how to incorporate an abort button, enabling users to cancel a long-running record set process.

Lesson 3 is where we tie our progress bars into record set operations. At this point, we will explore the timing calculations discussed earlier.

For lesson 4, the emphasis shifts to building an unbound customer form. I will demonstrate how to navigate through records, get the record count, and use the Move Previous command along with Move First and Move Last. We will also cover how to edit records, add new entries, and make updates where necessary.

Lesson 5 will round things out by addressing deletion and sorting of records.

I will be working with Microsoft 365, but if you are using Access 2019, you will still be able to follow along. Most of the code we work on in this class should function all the way back to Access 2007, and even possibly 2003, though I recommend using at least Access 2016 to ensure compatibility.

To get the most from this course, you should already be comfortable with all the beginner-level content along with Expert 1 and 2, especially in terms of relationships. Developer 1 covers the VBA fundamentals you will need. It is also important to be up to date on the latest covered in Developer 13 and on, and definitely make sure you have a good grasp of Developer Levels 16 and 17, since those introduce and cover record sets in depth. Being prepared with this background will help you make the most of Developer 18.

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

Building a text-only progress bar in Access
Using the Microsoft Access ActiveX progress bar control
Creating a custom 3D progress bar with text boxes and shadows
Adding an abort button to interrupt record set loops
Displaying progress during record set loops
Calculating elapsed time and records processed per second
Estimating seconds remaining in a record set loop
Creating an unbound customer form for record navigation
Implementing custom navigation buttons on an unbound form
Using the Move Previous command with record sets
Editing, adding, and updating records in an unbound form
Deleting records from an unbound form
Sorting records in an unbound navigation form