Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Subscribe to Access 307
Get notifications when this page is updated

Intro

In lesson 307, you will learn how to create custom search buttons on your Microsoft Access forms, set up dynamic combo boxes that filter based on previous selections, implement basic database security features like passwords and exclusive access, and split your database into front-end and back-end files for multi-user environments. These topics build on previous lessons and offer practical techniques to improve your Access databases.

Transcript

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

Today's class covers some topics that people have been emailing me to go over on the topic of course. First is creating custom search buttons, which are little buttons on your form that you can click on. It will ask you what you want to search for and then it will search for that information in the particular field you are in.

For example, you can put a little search button next to the last name field and if your user types in Smith, it will find the first Smith in the database, which is much easier than using the little binoculars.

Next is dynamic combo boxes. These are combo boxes that will limit other combo boxes based on the value that is picked. For example, let's say you are searching for a car part. You have to search for make, model, and the part number. The first combo box might have the make, and you pick Ford. That will limit the records in the second combo box to show just Ford cars. It will show a Taurus, Ranger, F-150, and so on. Then the third box will be limited to part numbers for that specific car.

Many people ask me how to do this, so we are going to do it in today's class.

Next, we are going to cover some simple database security. We are not going to get fully into total database security today, but I'm going to show you a couple of tips and tricks to secure your database. Database password, setting up exclusive access, creating an MDE and encrypted database file, and so on. So, some real simple database tips.

Now, I'm going to show you how to split your databases. This is handy if you want to create two separate database files: one with all your stuff in it, your code, your forms, reports, queries, macros, and a second copy with just your tables and your data. This is useful if you want to put your database on a network so that multiple people can access it. You can have all your databases, your tables stored on a server. There are lots of benefits to this and I'll talk about them as we get to that.

This class follows Access 306. I recommend you take that class first before watching this one, but the material I am covering in this class really is not dependent upon the material I covered in the last couple of classes. If you missed it, you should be fine.

I will be using Access XP and Windows XP in this class. Most of the material covered today should be valid for any version of Access.

The database files that we use can be found in the help folder if you installed off of our CD, or you can go to the website at that folder, AccessLearningZone.com/Access/307, and download a copy.

Of course, you will benefit most by doing the examples yourself. Do not just watch the videos, but try this stuff out yourself.

Quiz

Q1. What is the purpose of custom search buttons discussed in this class?
A. To allow users to filter reports
B. To let users click a button and search for information in a specific field
C. To export data to Excel
D. To print forms directly

Q2. What functionality do dynamic combo boxes provide?
A. They allow users to add new records to a table
B. They update their available values based on choices made in another combo box
C. They can be used to send emails from Access
D. They display large images in forms

Q3. Which example is given in the video for using dynamic combo boxes?
A. Searching for employees by last name
B. Selecting a car make to filter models and then part numbers
C. Sorting customers by sales volume
D. Printing customized invoices

Q4. Which topic related to security is NOT specifically mentioned in the video?
A. Database password protection
B. Creating an MDE and encrypted database file
C. Using exclusive access settings
D. Setting up two-factor authentication

Q5. Why might you want to split your Access database into two separate files?
A. To reduce file size for emailing
B. To separate code, forms, and reports from data tables for multi-user scenarios
C. To improve backup performance
D. To enable exporting data to web servers

Q6. Where can students find the database files used in this class?
A. Only on a provided USB drive
B. In the help folder from the CD or by downloading from the AccessLearningZone.com website
C. Sent via email after course registration
D. Only available from Microsoft directly

Q7. What version of Access is used in the class demonstrations?
A. Access 2000
B. Access 2016
C. Access XP
D. Access 365

Q8. What does the instructor recommend for students to get the most benefit from the course?
A. Watch each video twice
B. Complete written assignments
C. Try the examples themselves instead of just watching the videos
D. Only read the course notes

Answers: 1-B; 2-B; 3-B; 4-D; 5-B; 6-B; 7-C; 8-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 several important topics that many students have requested in the area of Microsoft Access development.

First, I start by discussing how to create custom search buttons for your forms. These are small buttons that you can place next to fields, such as the last name field. When a user clicks the button, they are prompted to enter a value to search for. For instance, if a user types in Smith, the button will find the first record with the last name Smith in your database. This approach provides a much simpler search feature than using the standard binoculars tool built into Access.

Next, I cover dynamic combo boxes. These are combo boxes that update the contents of other combo boxes based on the selection the user makes. For example, imagine searching for car parts. You might have a combo box to select the make of the car - if you pick Ford, the second combo box will only display models of Ford vehicles, such as Taurus, Ranger, and F-150. Once a specific model is chosen, the third combo box would be filtered to show only the relevant part numbers for that particular vehicle. Many students have asked me about this feature, so today you'll learn exactly how to set this up.

I also introduce some basic techniques for improving your database security. While we won't cover comprehensive database security in this class, I will show you a few useful tips. These include setting a database password, configuring exclusive access, creating an MDE file, and encrypting your database file. These simple steps can help you begin securing your Access applications.

Another topic addressed in this class is how to split your database. You'll learn how to separate your project into two files: one containing all your forms, reports, queries, macros, and code, and another that holds just the data tables. Splitting your database like this is ideal for putting your data on a network server so multiple users can access it while keeping the design and interface in a separate file. I'll explain all the benefits of this setup as we go.

This course is a continuation of Access 306, and I recommend watching that course first for best results. However, the concepts in Access 307 do not depend heavily on materials from the previous classes, so you should be able to follow along even if you missed them.

In the examples, I'll be using Access XP and Windows XP, but everything I cover today should work on any current version of Access.

The database files you'll need for the class are in the help folder if you installed from our CD, or you can download them directly from the website in the Access 307 section at AccessLearningZone.com.

Remember, you'll get the most benefit from this class if you follow along and perform the examples yourself rather than just watching.

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

Creating custom search buttons on forms
Prompting users for search criteria in forms
Searching records by specific fields using custom buttons
Setting up dynamic combo boxes
Filtering combo boxes based on other selections (cascading combos)
Limiting combo box contents using previous combo box choices
Basic database security tips
Setting a database password
Configuring exclusive access
Creating an MDE or encrypted database file
Splitting the database into front-end and back-end
Benefits of splitting a Microsoft Access database
Storing tables on a server for multi-user access