Welcome

Welcome! Progress Bars, Unbound Nav w Recordsets

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

Welcome to Microsoft Access Developer Level 18. In this course we will work with record sets, create various types of progress bars including text-only and 3D styles using Access components, and learn to display progress during long record set loops. We will cover how to calculate elapsed time, records per second, and estimated time remaining in loops, as well as how to add, edit, delete, and sort data in record sets. We will build an unbound navigation form with custom buttons, discuss abort buttons for user cancellation, and review relevant Access subscriptions and prerequisites for this material.

Navigation

Prev: D18
Next: Lesson 01

Keywords

Access Developer, recordset progress bar, VBA progress bar, text-based progress bar, 3D progress bar, ActiveX control, shadow effects, recordset timing, seconds elapsed, records per second, unbound form, navigation buttons, add edit delete records, sort r

Subscribe to Welcome
Get notifications when this page is updated

More Information

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. 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.

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. 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, sit back, relax, and enjoy Developer 18.

Intro

Quiz

Q1. What is the primary focus of Microsoft Access Developer Level 18?
A. Working with record sets and building different progress bars
B. Creating complex reports
C. Designing web-based forms
D. Integrating Access with Excel

Q2. What is Richard Rost's preferred method when creating components in Access?
A. Using ActiveX controls whenever possible
B. Using only Access-built components whenever possible
C. Always using third-party add-ons
D. Relying solely on VBA code

Q3. What problem do progress bars address when looping through record sets in Access?
A. Preventing data corruption
B. Reducing application size
C. Displaying status and estimating completion time
D. Encrypting user data

Q4. When demonstrating progress bars, what approach does Richard mention he personally does NOT like?
A. Using a text-based progress bar
B. Using a couple of textboxes with shadowing
C. Using the built-in ActiveX progress bar control
D. Using a graphic image for progress

Q5. What additional feature is included with the fancy 3D progress bar in lesson 2?
A. A sound notification
B. An abort button to cancel the loop
C. Automatic email sending
D. Export to PDF function

Q6. What type of form is created in lesson 4 of the course?
A. A continuous form bound to a table
B. A split form for data entry
C. An unbound form with navigation and record manipulation controls
D. A datasheet form for quick edits

Q7. Which of the following actions will you NOT learn in this course when working with the record set in lesson 4 and 5?
A. Moving through records with custom buttons
B. Editing and adding new records
C. Deleting and sorting records
D. Creating user-level security

Q8. What is recommended as the minimum version of Access for running the code taught in this course?
A. Access 2003
B. Access 2007
C. Access 2016
D. Access 2021

Q9. Which course(s) should you be familiar with before taking Developer Level 18?
A. Only Developer 1
B. Only Beginner Level
C. Developer 16 and 17
D. Only Expert 2

Q10. Which of the following timing calculations are covered when looping through record sets?
A. How to calculate disk space needed
B. How many records processed per second
C. How to sync Access with Google Calendar
D. How many fields in the database

Answers: 1-A; 2-B; 3-C; 4-C; 5-B; 6-C; 7-D; 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 is part of my Microsoft Access Developer series, specifically Developer Level 18. I'm Richard Rost, and in this class, we'll be focusing more on working with recordsets and progress bars in Access.

We'll be exploring different ways to build progress bars. First, I'll demonstrate a simple text-only progress bar. Then, even though I personally do not recommend it, I'll show you how to use the standard ActiveX progress bar control that ships with Microsoft Access. After that, I'll walk you through creating more visually appealing progress bars using only Access controls, like a couple of text boxes combined with some shadow effects to give that modern look.

One of the key goals I strive for in my courses is to rely exclusively on built-in Access controls wherever possible, avoiding outside dependencies. That's why I prefer crafting custom progress bars with native controls instead of third-party or ActiveX versions. Once we have these progress bars, I'll demonstrate how you can use them to track the status of a loop that's processing through a large recordset. It's always helpful for users to see how much time a process will take. I'll show you how to display elapsed time, how many records are being handled per second, and an estimate for the remaining time to completion.

We've previously worked a lot with reading data through recordsets. In this class, we'll go further and learn how to use recordsets to add, edit, and delete data, as well as how to sort records. We'll create an unbound form that doesn't rely on a table or query for its data source. On this form, we'll set up our own navigation buttons for moving through records, add new entries, delete, edit, close, and sort. As we need them, we'll load records from our recordset and update the display accordingly.

Lesson 1 covers using the Microsoft ActiveX progress bar control, as well as building a text-based progress bar that doesn't depend on ActiveX.

Lesson 2 focuses on making that attractive 3D-style progress bar just using text boxes and shadow effects. I'll also show you how to make an abort button so users can cancel long-running processes if needed.

Lesson 3 ties these custom progress bars into our recordset loops. I'll explain how to perform all the necessary timing calculations to keep users informed about progress.

In Lesson 4, we'll put together that unbound customer form. We'll learn how to navigate through records, fetch the total record count, and use the MovePrevious command, which is something we haven't touched on before. I'll also revisit commands like MoveFirst and MoveLast. Then I'll take you through editing records, adding new ones, and updating existing data.

Lesson 5 wraps up with deleting and sorting records.

I'll be using Microsoft 365 for these demonstrations, but everything in this course should work fine with Access 2019 and, for the most part, all the way back to Access 2007. In fact, the bulk of this material even applies to Access 2003. However, I do recommend using at least Access 2016 or later if possible.

If you're following along, you should be comfortable with all of the beginner courses, as well as the relationship material in Expert 1 and 2. Developer 1 covers the basics of VBA, and Developer 13 and up introduce the more advanced concepts we'll build upon here. Developer levels 16 and 17 introduced recordsets, so make sure you're familiar with those before starting with this course.

That's what you can expect in Developer Level 18. As always, 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 text-only progress bars in Access

Using the ActiveX Microsoft Progress Bar control

Creating custom progress bars with text boxes

Applying shadow effects to progress bars

Displaying progress during recordset loops

Calculating elapsed processing time

Showing records processed per second

Estimating time remaining for recordset loops

Adding, editing, and deleting records with VBA

Sorting data in recordsets

Creating an unbound form for navigation

Building custom navigation buttons

Implementing add, delete, edit, and close buttons

Using Move First, Move Last, and Move Previous commands

Loading and manipulating records from a recordset

Creating an abort button to cancel loops

Primary Topics

recordsets, progress bars, ActiveX control, unbound forms, VBA, navigation controls, adding records, editing records, deleting records, sorting

Secondary Topics

performance timing, shadow effects, abort operation, looping optimization