Association 7
By Richard Rost
3 years ago
Microsoft Access Association Database, Part 7
This is Part 7 of my Microsoft Access Association Database. In this video we're going to build the combo boxes on our main forms to select helper data based on the specific type of data we need to pick for that combo box.
Pre-Requisites
Members
Members will learn how to double click on the combo box to open up the helper type form to the specific helper type ID, make any changes such as adding a new value, and then when they close that form it will update the combo box. Well then add a password and make the password sticky so it remembers that you're a manager for the rest of this Access session.
Links
What's Next
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, helper type combo boxes, password
Subscribe to Association 7
Get notifications when this page is updated
Intro In this video, we continue building our Microsoft Access Association database by adding combo boxes to forms that display filtered helper data, such as person type and payment type, based on their respective categories. I will show you how to adjust the Row Source property using SQL to limit list values to the correct helper type, rename controls for easier coding, and review the differences between control source and row source. I will also discuss best practices for managing backups and securing your helper tables. This is part 7.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, we are continuing with our Association database. This is part seven of how many, I don't know, so I guess until I get sick of it or until you guys tell me that you are done with it.
In this video, we are going to be continuing to work with our helper data. We are going to make the combo boxes on our other forms pick helper data based on what that field is, such as person type, payment type, and so on.
Starting out with part seven, as you've noticed, what I've been doing at the beginning of every video is making a copy of the database file. I am doing this for the Gold members so they can download their database files. This will be part seven. This is also something I do when I am developing a database, and it is not a bad idea for you to get in the habit of doing this too. When you sit down for the day to do any major work on your database, make a copy of your front-end file. That way, if you make a big mistake, as I have in the past many times, you can always go back to the previous version.
Of course, you should have nightly backups. I have a whole video on backing up, but this is just one more step to make sure that whenever you have in your head, "I am going to make a major change today," you make a backup copy of your front-end file. Make sure you've got good nightly backups running.
All right, so part seven. In part six, we built the helper forms. Where are you? The helper type form with the helper form inside of it. So now, what we are going to do in this part is actually put the combo boxes here on the other forms to use that helper data. How do we get a list of just what we want? Let's start with the person type ID.
We have our helper type, which is person type: member, mentor, clergy, leader, whatever. However you want to categorize your people. So I need to make a combo box on this form that shows the helper data right from the helper table where the helper type ID equals one.
Right click, design view. Let's put this down at the bottom and make the notes field smaller. Drop a combo box down here. Looking up the values from a table or query, next. We are looking up the data from the helper table, next.
Now, what data do we want in the combo box? I want the helper ID and I want the helper value. I do not need the helper type ID in the combo box. We are going to use it for a criteria in just a minute. Next. Let's sort this by the helper value. Next.
That is what it is going to look like. Don't worry that you can see everything in here. That's fine. Next. We are going to save that value in the person type ID. This is the data that we are capturing. Next. What label do you want? Person type.
There we go. Get the conditional formatting from that guy and use the conditional format painter. Slide this out as needed. Now save it and take a look at what we've got. Open it up, drop it down. Now you are seeing everybody in here; every bit of helper data is in here.
We have to limit this list. I only want to see helper items where the helper type ID equals one. How do we do that? Well, you could make a whole bunch of queries if you wanted to, based on the helper table, and say where helper type ID equals one, helper type ID equals two, etc. But that is why you want to learn SQL, because knowing SQL, we can go right into the Row Source for this combo box and put the criteria directly in here. This is what the wizard does - it builds this SQL statement.
First thing I am going to do: I don't like "Combo40." Let's change that real quick. Let's call that the "PersonTypeCombo." That is what I like to do so I know when I am coding later whether it is an ID (then it is just a text box), and if it is a combo (combo box).
Back to the data. This is why it is important to learn SQL. I am going to zoom in - Shift+F2. As a reminder, I have tons of lessons and seminars and all kinds of stuff on SQL. It is very important if you are going to be a serious Access developer to learn SQL. I have a free video that will get you started on how to use SQL with Access, as I mentioned in one of the previous classes.
So, we are going to clean this up a little bit. Again, since I am only bringing in data from one table, I do not need the "HelperT." there or these brackets since I do not have spaces in my names. This makes it a lot easier to read.
OK, so it is:
SELECT HelperID, HelperValue FROM HelperT ORDER BY HelperValue
What do I need to add to this? Just like before, I have to add a WHERE condition:
WHERE HelperTypeID = 1
I am going to hard code a 1 in there because it is specific to this box. Click OK. Close this, save it, close it down, open it back up again. Drop the box down and look at that - I am seeing only HelperTypeID 1. There are my person types.
Let's say Ben Sisko is clergy. He is the prophet, right? Emissary of the Prophets. If Quark was involved, it'd be "of the Profits," right? I spelled "Emissary" wrong. I can't do anything right today, can I? "Emissary," there we go.
Go to someone else, close that, let's go to Alex Lifeson. Drop it down, and he is a mentor - let's say his guitar teacher.
Let's do another one. Gender, marital status, all these we are going to do on the Demographics form, which we haven't built yet. But we do have PaymentTypeID and PaymentDetails, so let's do a PaymentTypeID.
Actually, we can unbold those. I bolded them earlier to remind me to do them; now we can unbold these. See my little notes down here.
This is another thing that I do - and I do this for classes, but I also do it when I am developing when I have my roadmap (my tables and stuff all in Excel). When I am done for the day, I say "Next up: what do I want to cover next?"
I don't always plan every step of it out as I am going. Sometimes things come up, and I have to remember to do that next. So this is what I am going to do next, and yesterday we will have an extended cut.
For PaymentType, let's come in here, design view. Now, from now on, I can copy these things, right? Copy, paste. PaymentType, and this will be the "PaymentTypeCombo." We have to change the control source, right? It is still PersonTypeID, but we have to change which field this is bound to. Did I not add that? Oh, there it is up here, PaymentTypeID.
I was looking on the bottom because that is where it was on the roadmap. This will be PaymentTypeCombo. Remember, control source is where this control is bound in the table, so when you pick a value, that is where it saves its value. Where it saves its value from and where it gets its list of values from are two different things: control source is where you save your data, row source is where you get your list of values.
I always mix up row source and control source. Row source is for combo boxes and list boxes. Now, this is going to change just slightly: just change the helper type ID.
What was it? I am going to have to go and take a look at my table because I forget the helper type table. Payment type is two. That is convenient. So, back in here, change this to two, that's all.
For every box you've got, you just have to put the right type in there. That is why it is important to make sure you do not just let anybody into your helper tables.
In fact, I usually do not allow users to access the helper type table at all, and as far as the helper data goes, you want to be very careful too, because some of this stuff might be hard-coded in your database. These are now hard-coded in your database. So, if someone accidentally deletes two, now you are just messed up because if you add another one down here, it is going to come in at six. There are tricks to get back missing autonumbers, but just do not let people in here.
Now, save it, close it, open it back up again. What is your payment type? Oh, look at that: cash, check, credit card, same. You can put the payment details in another box. I am not going to put PaymentDetails on here right now - you get the point, right? You could put the credit card number down here, expiration date, whatever else you want.
As far as editing this list goes, I do not like giving my end users the ability to edit this stuff. In my ABCD video, I show how you can make certain items locked, or you can use a List Items Edit Form if you want to. I do not recommend it. You could have that pop up the Helper Type form, but then you would have to go to the right one.
Hint: that is what I am going to be showing in the extended cut, by the way, for the members. You can double-click on this guy, have it pop up the Helper Type form to the correct helper type, and let them edit these things. Generally, I like to reserve that for an administrator, like you. You will have your own way to get into these forms.
Maybe a button on the main menu with a password, which again I have videos for. In my Input Box video, for example, I show how to make a manager menu, and when you click on the manager menu button, to open this, it asks you for a password. That is one way you can keep people out of your helper tables and helper forms.
Members, I am going to show you how to make it so you can double-click here, and it will open up the Helper Type form. Then you can make a change and come back here, very similar to what we did with the family stuff. We'll add a password in there, too.
In the extended cut for the members, we are going to make it so you can double-click on the combo box, it will open the Helper Type form to that specific helper type ID, let you change or add or delete items, and then, when you close this, it will bring you back to the combo box and even open it up for you to show the new item. It will require that box. Then, we will add a manager password, so when you double-click on that, you must type in a password to prove that you are a manager. Then we will make it so it is "sticky," so once you type in the manager password, it will assume you are the manager for the rest of that session and will not keep making you type the password in every time.
That is all covered in the extended cut for the members. Silver members do not get access to all of my extended cut videos, and Gold members can download these databases.
That is your Fast Tip for today. I hope you enjoyed it. I hope you learned something, and I will see you for part 8 soon.Quiz Q1. Why does Richard recommend making a copy of your database file at the start of each development session?
A. To avoid unnecessary work
B. To create more storage options
C. To protect against accidental mistakes or data loss
D. To save time when coding new features
Q2. Where should you store the backup copy of your Access database, according to the video?
A. On your desktop
B. As part of your nightly backups and before major changes
C. In the system's temporary folder
D. In a cloud-based spreadsheet
Q3. What is the main purpose of using combo boxes on forms in the context described?
A. To allow freeform user data entry
B. To display a fixed list of unrelated options
C. To select values from helper data based on specific field criteria
D. To perform mathematical calculations
Q4. In the example given, what information does the Person Type combo box display?
A. All possible helper data values
B. Only Payment Type values
C. Helper data from the table where HelperTypeID equals 1
D. Values from a separate unrelated table
Q5. How does Richard suggest restricting combo box results to certain helper data?
A. By manually editing form design each time
B. By applying a WHERE clause in the Row Source SQL statement
C. By using Macros
D. By deleting unnecessary table rows
Q6. What is the difference between Control Source and Row Source in a combo box?
A. Control Source stores the list values, Row Source saves the data
B. Row Source saves the selected value to the table, Control Source provides list values
C. Control Source binds the selected value to a field, Row Source determines the list of values shown
D. There is no difference
Q7. When creating a Payment Type combo box, what change is needed from the Person Type combo box?
A. Changing the combo box label only
B. Changing the hardcoded HelperTypeID in the WHERE clause to 2
C. Removing the SQL statement
D. Enabling editing for users
Q8. What does Richard advise regarding editing the helper tables?
A. Allow all users access for flexibility
B. Regularly delete unused records
C. Restrict access, especially since some values are hard-coded in the database logic
D. Leave them unprotected since errors are rare
Q9. What is the suggested method for administrators to access and edit helper data safely?
A. Give access to everyone through the main form
B. Use a button on the main menu with password protection
C. Only allow edits during database startup
D. Email changes to the developer
Q10. What additional feature does Richard mention for members in the extended cut?
A. Automatic report generation
B. Double-click to open the Helper Type form with password-protected manager access
C. Real-time data export to Excel
D. Auto-saving all changes immediately
Answers: 1-C; 2-B; 3-C; 4-C; 5-B; 6-C; 7-B; 8-C; 9-B; 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 continues our work on building the Association database. This is part seven in the ongoing series, and at this point, I am not sure yet how many parts there will be. I'll keep going either until I run out of topics to cover or when you tell me you have seen enough.
In this lesson, we shift our focus to enhancing our forms with combo boxes that use helper data. These combo boxes allow users to choose options such as person type, payment type, and similar items, drawing their choices from the helper table we set up earlier.
Before we get started, you may have noticed in every video that I begin by creating a backup copy of the database file. I do this primarily for my Gold members who want to download the databases after each lesson, but it's also an important habit when developing any database. I recommend that you also make a copy of your front-end file before you sit down for any major development session. That way, if you make a big mistake, you can always revert to an earlier version. Of course, you should also have nightly backups in place, but making a manual backup at the start of a session is an extra safeguard against losing your work.
Now, as we move into part seven, let's review where we left off. In part six, we created the helper forms. Now, it's time to apply what we've built and actually use helper data for combo boxes on other forms. The challenge here is to make sure each combo box only shows the options relevant to its specific purpose. For example, when selecting a person type, I only want to see options like "member," "mentor," "clergy," and so on.
To do this, I begin by opening the form where I want my combo box—for instance, one where I need to specify the person type. The process starts by switching the form to design view, then placing the combo box where I want it. I set it up so it pulls its list from the helper table, selecting only the helper ID and value, since the helper type ID will be used for the underlying criteria. Sorting the list by helper value makes it easier for users to find what they need.
Next, I make sure the data selected by the user will be saved in the correct field in the table—in this case, the person type ID. Once that's set, I clean up the label and any formatting to make the form user-friendly.
However, by default, the combo box is going to show every entry in the helper table, which includes all helper types. We want to limit it so that only the relevant options appear. To achieve this, you can certainly create multiple queries—one for each helper type—but it's more efficient to adjust the SQL statement in the combo box's Row Source property directly. That's why understanding SQL is so vital if you're going to get serious about Access development. I have a free tutorial on getting started with SQL for those who want to go deeper.
By modifying the SQL, I instruct the combo box to only display entries where the helper type ID matches the one I want—for example, showing only those with a helper type ID of 1 for person types. This approach keeps the combo box clean and relevant.
Of course, I make sure to rename my combo box from the generic name Access gives it—like "Combo40"—to something meaningful, such as "PersonTypeCombo." This makes it much easier to manage when adding more code down the line.
The same approach applies when creating combo boxes for other types of helper data. For instance, to handle payment types, I simply copy the existing combo box, change its name, adjust which table field it's bound to, and update the WHERE clause in the SQL so it points to the relevant helper type, such as PaymentTypeID equals 2. I double-check my table to confirm which helper type ID refers to which list. It's this little bit of extra attention to detail that prevents confusion later.
On a practical note, be careful about who can edit your helper tables. Since the choices in these combo boxes are hardcoded to specific IDs, deleting an entry can break the system. I usually restrict regular users from editing this data directly. If you allow editing, it's best reserved for you or a trusted administrator, and I have other videos discussing password-protected admin features.
For those interested in going further, in today's Extended Cut for members, I will show you how to enhance usability by letting an administrator double-click a combo box to open up the corresponding Helper Type form, where they can directly add or edit entries. This feature will let the user return right to the combo box, with the list refreshed to show any new or updated items. We'll also add a manager password to protect access, and I'll show you how to make it "sticky" so that once you log in as a manager during a session, you won't need to enter the password again until you close and reopen the database.
That sums up today's Fast Tips video. If you want a full video walkthrough with step-by-step instructions covering everything discussed here, you can find it on my website at the link below. Live long and prosper, my friends.Topic List Creating combo boxes for helper data on forms
Filtering combo box values based on HelperTypeID
Customizing the Row Source property using SQL
Hard-coding WHERE criteria in combo box SQL
Renaming combo box controls for clarity
Binding combo boxes to the correct table fields
Using the control source vs. row source properties
Copying and modifying existing combo boxes
Referencing lookup values with HelperTypeID
Controlling user access to helper tables and data
Understanding the importance of backups before major changes |
