Welcome

Welcome! Scrub Data, Nested & Split Forms

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

Welcome to Microsoft Access Developer Level 21. In this course we will learn how to scrub and clean up data for insertion into relational tables, work with nested continuous subforms and arrange them side by side, and use arrays to manage hierarchical data and create parent breadcrumbs. We will cover using recordsets to restructure data, copying information into new tables, getting IDs of new records, handling composite keys, and loading arrays dynamically. We will also discuss applying these techniques to employee and product records and working with Access 365 and earlier versions.

Navigation

Prev: D21
Next: Lesson 01

Keywords

Access Developer, data scrubbing, recordset, subform, continuous forms, nested forms, side by side forms, composite keys, arrays, breadcrumbs, parent hierarchy, dynamic arrays, static arrays, redim preserve, list box, employee hierarchy

Subscribe to Welcome
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access Developer Level 21 brought to you by AccessLearningZone.com.

I am your instructor, Richard Rost.

In today's class, we are going to learn how to scrub data given to us in whatever format. Sometimes you get a spreadsheet of information and you want to be able to clean up that data a little bit and then insert it into other tables. Maybe check if records exist. Lots of different things.

We are going to learn about nested subforms where you get a continuous form inside of another continuous form. Then I am going to show you how to do the same thing side by side. So you have a continuous form on the left, a continuous form on the right, and when you click on the parent on the left, for example, all the child records show up on the right.

Then we are going to learn about arrays which we will use to do parent breadcrumbs. You get a hierarchy of employees and you want to say this is this person's supervisor and so on and so on up the tree or after the root or the top of the president, CEO, or whatever.

We will use an array to load up the customer or employee records. You can do this with product records too with product subforms. It is basically thousands of folders.

We will do all that today in Developer 21. Let's take a little more. Let's review exactly what is covered in each lesson.

In lesson one, we are going to use a recordset to scrub some data. Back in Access Developer 20, we created that box form where we have a bunch of boxes and each box has multiple positions and then some data. Well, that table was not necessarily relational. The box record repeated every record.

So in this lesson, we are going to use a recordset to make this relational. We are going to copy box information into two new tables, container and position. So I will have to use a recordset to loop through it, determine when we have a new box, add to the box table, then loop through all the other records, add the position table, and so on.

One important technique in this lesson is I am going to show you how to get the ID, the autonumber, of a newly added record inside of adding it to a recordset. Very important.

In lesson two, we are going to learn about nested continuous forms. We are going to build a form, a continuous form for the container and for the position. Then we are going to make them both continuous forms. Then we are going to put the position form inside the container form as a subform. You are not supposed to be able to do that. Access actually throws up a warning message, but I am going to show you how to get around it.

Then we are going to recreate the add box code from Developer 20. This time we are going to add a container. We have to use two different tables again. Then I am going to teach you about composite keys. That makes sure that we do not have two records that have both the same container and position. It is a key field based on multiple fields.

Lesson three was inspired by lesson two. I think it looks a whole lot better to have these side by side than to have the continuous forms one over the other, where the one inside the child form is in the form footer of the parent form. We can do them side by side, but we have to use two continuous forms each as a subform inside a third unbound form. I know it sounds a little complicated, but I will show you how to do it.

We are going to set up side by side continuous forms. The on current event of the one, the container form, the parent basically, will control the record source of the second form. Then I will show you how to change the subform's source object. Then we will put a nice little cool label up top there. All that in lesson three.

In lesson four, we are going to learn about arrays. It is going to be a crash course to arrays. We are going to learn how to dim a static array, load an array manually with some data, load it with recordset data by looping through a recordset and then loading that up into the array, and creating a dynamic size array. So if you do not know how many records go into it ahead of time, I will show you how to do that with the redim and redim preserve commands. Then we will learn about the ubound and elbound functions to tell how big your array is.

In lesson five, we are going to take what we learn about arrays in lesson four and put them to use to put together parent breadcrumbs. Let's say we have in our employee database, or ignore customers for a minute, let's say it is an employee database and every employee has a supervisor throughout the entire organization. I want to be able to see a breadcrumbs trail of this particular employee's supervisors all the way back up to the beginning or all the way to the top of the root.

You can see here, Jordy, for example, is below Data, Data is below Will, Will is below John Luke, who is below me, and I am over the root, off the root. So that is what a breadcrumbs trail is. You can use this for product categories, you can use it for all kinds of different things.

I am going to show you how to load an array with the parents. You have to start with Jordy and then go back through the parents until you get to the root. Then, now we can go forward with that list, which we are going to pull out of the array and use to fill our list box with the actual parents.

We will learn about for next step in this lesson as well.

I will be using Access 365, part of the Microsoft 365 program. This should work for 2019, 16, all the way back to maybe 2007. All this stuff is just fine for every version of Access since 2007, and probably even before that.

Of course, I strongly recommend all my previous classes, but you should have at least taken Developer 16 and on, Omicover Recordsets.

Of course, if you have any problems, questions, thoughts, or concerns, feel free to post them in the Access forum on my website.

And now, sit back, relax, get your thinking caps on, and let's enjoy Access Developer 21.

Intro

Quiz

Q1. What is the main topic covered in Microsoft Access Developer Level 21?
A. Data scrubbing, nested subforms, and arrays
B. Web integration with Access
C. SQL Server migration
D. Access security features

Q2. When scrubbing data in lesson one, what is the main goal?
A. Make un-relational table data properly relational
B. Convert text values to upper case
C. Remove all duplicates from a single field
D. Export data to Excel

Q3. Which tool is used in lesson one to loop through records and move data to new tables?
A. VBA Recordset
B. Access Macros
C. Form Filters
D. SQL Queries only

Q4. What important skill regarding autonumbers is taught in lesson one?
A. How to retrieve the ID of a newly added record in a recordset
B. How to increment numbers manually
C. How to avoid duplicating numbers in Access tables
D. How to delete autonumber fields

Q5. What is unique about the nested continuous forms demonstrated in lesson two?
A. A continuous form is placed inside another continuous form despite Access warnings
B. The forms only allow single data entry
C. Only pop-up forms are used
D. The continuous forms are only available in the 2021 version of Access

Q6. What database concept is illustrated with the container and position tables?
A. Composite keys
B. Indexing formulas
C. Lookup fields
D. Pivot tables

Q7. What side-by-side continuous form technique is taught in lesson three?
A. Two continuous forms as subforms inside a third unbound form
B. Placing forms only in navigation panels
C. Joining data with crosstab queries
D. Having both forms share a single record source

Q8. In the side-by-side form setup, what event is used to control which child records are shown?
A. On current event of the container (parent) form
B. Timer event
C. On load event of the main form
D. On close event of the subform

Q9. What topic is the "crash course" of lesson four?
A. Arrays in VBA
B. SQL joins
C. Macro security
D. Exporting reports

Q10. What techniques for arrays are covered in lesson four?
A. Dim static arrays, load arrays manually, use recordsets, dynamic sizing, ubound/elbound functions
B. Splitting Access databases
C. Automatic backup strategies
D. VBA class modules

Q11. In lesson five, what is the main application of arrays?
A. Creating a breadcrumbs trail of supervisors in a hierarchy
B. Calculating totals for reports
C. Importing CSV files to Access
D. Encrypting database fields

Q12. What is the primary method for constructing the breadcrumb supervisor list?
A. Go from selected employee up through each parent, loading each into an array
B. Load all supervisors directly into a combo box
C. Use a static query with all employees
D. Manually type in hierarchies

Q13. What is the purpose of composite keys as discussed in this course?
A. Ensure combinations of two or more fields are unique
B. Index tables for faster searches
C. Allow for table splitting
D. Enable cascading deletes

Q14. Which Microsoft Access versions are the class techniques applicable to?
A. 365, 2019, 2016, 2013, 2010, and most features back to at least 2007
B. Only the latest Office 365
C. Only Microsoft Access 2013 and lower
D. Only Access versions after 2016

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A

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 Microsoft Access Developer Level 21. I am your instructor, Richard Rost.

In this class, I will be teaching you how to clean up and organize data that may be handed to you in various formats, such as spreadsheets. This might involve scrubbing the data to prepare it for use in other tables, making sure you eliminate duplicates, checking for existing records, and performing similar data management tasks.

We will explore how to work with nested subforms in Access, specifically how to use a continuous form within another continuous form. I will then demonstrate how to create a layout where you have two continuous forms side by side. With this setup, when you select a record on the left - for example, a parent record - the corresponding child records will display on the right.

Next, I will introduce arrays and show you how to use them in Access. Arrays will be particularly handy for creating parent breadcrumbs. This is useful for visualizing hierarchical structures, such as employees belonging to supervisors, and tracing that relationship all the way up to the CEO or root individual. The array technique can be applied to other hierarchical data as well, such as product families.

Here's a detailed overview of what is covered in each lesson:

In lesson one, I will demonstrate how to use a recordset to clean up non-relational data. If you followed along in Developer 20, you may recall creating a form where boxes and their positions were stored in a non-relational way, with a lot of repeated data. In this lesson, I will show you how to break that out into two properly structured tables: one for containers (or boxes) and one for their positions. I will use a recordset to loop through the source data, add new container records when a new box appears, and then create corresponding position records. I will also explain an important technique for retrieving the primary key or autonumber value of a new record that you have just added using a recordset.

In lesson two, you will learn how to build nested continuous forms. The goal is to construct a continuous form for containers and another for positions, then embed the position form as a subform within the container form. Access normally discourages nested continuous forms and displays a warning message, but I will show you how to work around this limitation. We will review and rewrite some code from Developer 20, adapting it to work with two normalized tables. I will also cover the concept of composite keys, where more than one field is combined to enforce uniqueness and prevent duplicate records, such as not having two entries with the same container and position.

Lesson three builds on the previous topic. I find it is more attractive and manageable to arrange your forms side by side rather than stacking them one inside the other. To achieve this, we will use two continuous forms as subforms, both placed within an unbound parent form. The parent form will handle switching between child records by modifying the record source of the child form based on which parent record is selected. I will guide you through updating the subform's source object and setting up labels and interface elements to make this layout user-friendly.

In lesson four, I shift to a crash course on arrays. You will learn how to declare and work with static arrays, fill them manually, populate them by looping through recordsets, and handle arrays whose sizes you do not know in advance by using the Redim and Redim Preserve commands. I will also cover how to determine the size of an array with Ubound and Lbound functions.

Lesson five takes the concepts from lesson four and uses them to create a breadcrumbs trail for parent records. For example, in an employee database that contains supervisors in a hierarchy, you may want to see the full chain of command for any given employee, all the way up to the top. I will show you how to load parents into an array by starting with the selected employee and tracing back through their supervisors until you reach the root. Then, you can move forward through the array to build a display or list box showing the breadcrumb trail. I will also introduce you to the For Next Step loop in this process.

Throughout this course, I will be using Access 365, which is part of Microsoft 365. However, these techniques are compatible with Access versions as far back as 2007, and possibly earlier.

It is highly recommended that you have completed my previous Developer classes, especially from Level 16 onward, since we will be relying on concepts such as recordsets.

If you have questions, run into issues, or want to discuss anything further, please visit the Access forum on my website.

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

Scrubbing data using recordsets
Making non-relational data relational
Copying data into container and position tables
Using recordsets to loop and import records
Getting the autonumber ID of a new record
Building nested continuous forms
Embedding continuous forms as subforms
Creating composite keys for uniqueness
Setting up side by side continuous forms
Controlling subform record source with parent form
Changing a subform's source object
Creating dynamic labels on forms
Introduction to arrays in VBA
Declaring and populating static arrays
Loading arrays with recordset data
Creating and resizing dynamic arrays
Using ubound and lbound with arrays
Building parent breadcrumbs with arrays
Looping through parent-child hierarchies
Displaying breadcrumbs in a list box
Using For Next loops to access array data

Primary Topics

data scrubbing, recordsets, nested continuous forms, side-by-side forms, arrays, breadcrumbs, relational design, composite keys

Secondary Topics

form events, list boxes, parent-child relationships, dynamic arrays