Introduction

Welcome! Progress Bars, Unbound Nav w Recordsets

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

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

Intro

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.

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

Article

In this tutorial, we will explore working with recordsets in Microsoft Access, focusing especially on building and using progress bars to enhance your user experience. When you have a long-running loop, such as one that processes or updates many records, it is helpful to give your users feedback about how much progress has been made and how much remains. By integrating a progress bar with your recordset loops, you can display elapsed time, records processed per second, and even estimate how much time is left. I will walk you through building different types of progress bars and link them to your recordset operations, and then show you how to take full control over the navigation and manipulation of records on an unbound form.

Let us start with progress bars. One approach is to use the built-in ActiveX ProgressBar control that comes with Microsoft Access. While this can work, I generally prefer to use components built directly in Access, such as text boxes and labels, for maximum compatibility and control. For a basic text-based progress bar, consider a form with a text label or textbox that updates as your recordset processes each record. For example, you can set up your VBA code to update the value or caption of the progress bar control within the loop, giving the user a clear visual indicator of progress.

Here is a simple way to implement a text-only progress bar. Suppose you have a form with a textbox named ProgressBar, and you want to show progress as you loop through records:

Dim rs As DAO.Recordset
Dim totalRecs As Long
Dim recNum As Long

Set rs = CurrentDb.OpenRecordset("SELECT * FROM Customers")
rs.MoveLast
totalRecs = rs.RecordCount
rs.MoveFirst

For recNum = 1 To totalRecs
' Do your record processing here

Me.ProgressBar.Value = "Processing record " & recNum & " of " & totalRecs
DoEvents
rs.MoveNext
Next recNum

rs.Close
Set rs = Nothing

This simple snippet updates the textbox with the current record number out of the total each time through the loop, giving the user an ongoing status.

If you want to get a little fancier, you can build a 3D-style progress bar effect by using two textboxes layered on top of each other, one for the filled portion and one for the background or shadow effect. As your VBA loop iterates, you can change the width of the top textbox to represent the percentage complete. For example, assume you have a background textbox called ProgressBarBack and a front textbox called ProgressBarFront. As you process each record, set the width of ProgressBarFront accordingly:

Dim percentComplete As Single
percentComplete = recNum / totalRecs
Me.ProgressBarFront.Width = Me.ProgressBarBack.Width * percentComplete

This provides a smooth, dynamic bar that fills as the records are processed.

To make your code even more user-friendly, you can include an abort button on your form. This button sets a global flag variable, which you check inside your loop to determine if the process should stop early. Simply place a button on your form named cmdAbort, and in its Click event, set a module-level Boolean variable, for example:

Public AbortProcess As Boolean

Private Sub cmdAbort_Click()
AbortProcess = True
End Sub

Then, inside your record-processing loop, check the variable:

If AbortProcess Then
MsgBox "Process aborted by user."
Exit For
End If

With these techniques, you can give your users control and feedback during long operations.

Now let us connect our progress bar to some more advanced timing features. You can show the elapsed time, records processed per second, and estimate how much time is left. At the start of your loop, capture the time using the Timer function:

Dim startTime As Single
startTime = Timer

Within your loop, periodically calculate:

Dim elapsedTime As Single
Dim recordsPerSecond As Single
Dim secondsLeft As Single

elapsedTime = Timer - startTime
If elapsedTime > 0 Then
recordsPerSecond = recNum / elapsedTime
secondsLeft = (totalRecs - recNum) / recordsPerSecond
End If

You can then update your progress bar or display these values in labels to keep your user informed, for example:

Me.lblElapsedTime.Caption = "Elapsed: " & Format(elapsedTime, "0.0") & " seconds"
Me.lblRecordsPerSec.Caption = "Records/sec: " & Format(recordsPerSecond, "0.0")
Me.lblSecondsLeft.Caption = "Time left: " & Format(secondsLeft, "0.0") & " seconds"

Let us now move beyond progress bars and look at working with recordsets through an unbound form. Unlike bound forms, where Access automatically manages the record navigation for you, using an unbound form and VBA code gives you maximum flexibility for adding, editing, deleting, and sorting records. You might build an unbound form with your own set of navigation buttons such as Next, Previous, First, Last, Add New, Edit, Delete, Sort, and Close.

To move around in a recordset, use the MoveNext, MovePrevious, MoveFirst, and MoveLast methods. For example:

rs.MoveFirst ' Move to the first record
rs.MoveLast ' Move to the last record
rs.MoveNext ' Move to the next record
rs.MovePrevious ' Move to the previous record

For editing records in an unbound form, you retrieve the current record's data and push it into your form controls. When the user clicks the Save button, your code updates the recordset:

rs.Edit
rs!FieldName = Me.txtField.Value
rs.Update

To add a new record:

rs.AddNew
rs!FieldName = Me.txtField.Value
rs.Update

For deleting, simply:

rs.Delete
rs.MoveNext

Sorting is done by re-opening the recordset with an ORDER BY clause. For example, to sort customers by last name:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM Customers ORDER BY LastName")

Always remember to close your recordsets and release them with:

rs.Close
Set rs = Nothing

If you want to count the records in your recordset, after opening it, move to the last record and use the RecordCount property:

rs.MoveLast
Dim recCount As Long
recCount = rs.RecordCount

Displaying this value on your form helps your users know how many records are available to browse.

These examples give you practical tools for managing long operations and data navigation in Microsoft Access. All of these techniques work best in the latest versions, such as Access 2016, 2019, or Microsoft 365, though most of the code should run fine back to Access 2003 or 2007. By creating your own progress bars, integrating timing and user-cancellation features, and building custom navigation controls for your forms, you gain far more control over the database experience and can build interfaces that are both powerful and user friendly.