Lessons

Starting off working with Misc Data. This is all of that "extra" stuff you want to collect about people, but you don't necessarily want to put it in the EntityT. For example: shoe size, favorite baseball team, nickname, etc. In lesson 1 we'll build those tables. In lesson 2, we'll make it so specific items are required, so if you want to get shoe size from everyone, it will show up there automatically and cannot be deleted. We'll also make it so you can pick from a list of helper data items to provide that data. Lesson 3 allows you to force the user to pick from the list of items, and we'll design the Misc Type Editor form. In Lesson 4 we create an admin menu and add some simple security, make helper lists system values, and add the OpenFormRight function. Lesson 5 we add a group editor and the address block for copy/paste. Lesson 6 is lots of little housekeeping items: force email to be the first tab displayed, show current age next to DOB, HowLong function, make the first email, phone, and address entered set to primary, set the focus on new records in the entity subform, create a SwitchTabs function, and hide flashing with the Echo command. Lesson 7 builds slide-out form sections for the EntityF and EntityListF.

Database Files

I'm only posting the 32-bit versions for now. That's what I use. If you are using 64-bit Access, just create a new blank database file and import all of my objects into yours. I'll post a 64-bit version when the Core is finished. If you experience any code that doesn't work under 64-bit let me know. Click here for more information about 32 v 64 bit.

Resources

ABCD Home Page
Video Lessons Download ZIP File (975 MB)

Learn More

Free Access Beginner 1
Access Expert 1, Expert 2, and Relationship Seminar
Access Developer 1 for VBA Beginners
Access Insider Circle

Enroll Today!

Enroll now so that you can watch these lessons, learn with us, post questions, and more.

Pre-Requisites Required

In order to purchase any level of the ABCD, you MUST have purchased all previous levels. For example, you cannot buy only Level 5. You must first have Levels 1 through 4, then you can buy Level 5. Sorry, but this is a hard pre-requisite.

Videos

I am recording videos showing how the database is built, but mostly so that you can understand the construction and be better equipped to make any modifications or additions you may need on your own. This will be marketed as a finished database without the need for any enhancements, but, of course, one of the main reasons to use Access is so that you can make changes yourself. The videos will be a quick run-through of how each feature is built, as I'm building it. I will be using advanced features like SQL and VBA from the start, so you may want some SQL or VBA Developer Lessons under your belt if you're going to try to follow along. This is NOT a teaching database. Free Technical Support will NOT be offered.

Tech Support

There is NO free Technical Support provided on the ABCD if you have not taken ALL of my previous Developer lessons. There are a lot of very difficult, advanced programming concepts used in the construction of this database. I do not have the time to provide free support if you get stuck trying to make modifications. All I will do is point you to the appropriate Developer lesson(s) that cover the issue. There is a reason why (a) I say this is NOT a teaching database, and (b) there are pre-requisites. Feel free to ask questions, but I can't spend half an hour explaining concepts that are already covered in my other lessons.

Licensing

Unlike my other courses and seminars which provide you with royalty-free databases that you can modify and distribute yourself, the ABCD does NOT come with a royalty-free license. You may only customize the database for your business needs and for use within your organization. You may not resell or distribute it to others without express written permission. In addition, if you are using it in an organization with more than 10 users, you must purchase additional licenses. Contact me for additional information. I will post additional details when the Core database is completed.

Ordering

There are two purchase options available for the ABCD.

You can purchase a fully editable and customizable copy of the database in addition to the video tutorials that show how it was built. This is the option you should pick if you want to make changes to the database for your own business needs, and you want to learn how the database was constructed to assist you. As a reminder, these are not teaching videos. They are simply provided to show you how the database was built. Free support is not guaranteed if you have questions. I do my best to answer questions, but I can't promise anything. You must purchase any pre-requisite parts or modules required. For example, you may not purchase Core Part 4 without first having parts 1 thru 3.
Once the Core is finished, you can optionally purchase a use-only version of the database. This is the best option if you only plan on using the database for your personal or business needs, but do not intend on modifying the structure of the database. Free instructional videos will be provided to teach your staff how to use it.

Your Feedback

I will be releasing new Core features and modules on a regular basis. You can see the list of features that are scheduled for development on the Coming Soon page. I would very much like your feedback on what features you think should be available. If you have a specific interest for your business type, let me know.

Comments for ABCD Core Part 3

Age	Subject	From
2 years	dbl click on blue colored fields	Dirk Merkel
2 years	Notes to Access team	Sami Shamma
3 years	Slide out form sections	Alejandro Juarez
4 years	Howlong code	Nils Markgraf
5 years	Fabulous Stuff	Chris Bezant
6 years	ABCD Part 3 Lessons 4 to 6	Richard Rost
6 years	ABCD Part 3 Lessons 1-3	Richard Rost

Subscribe to ABCD Core Part 3
Get notifications when this page is updated

Intro

In this lesson, we continue building the Microsoft Access Business and Contact Database (ABCD) in the core, focusing on managing miscellaneous customer data, locking and formatting default records, and creating force-from-list options. You will learn how to set up admin menus with password protection, use simple admin security, add system value prompts, and implement group editing features. We will also cover building a dynamic address block for copying, defaulting the primary contact info, customizing tab behavior, and making form slide outs for better user interfaces. This is part 3.

Transcript

Welcome to the Access Business and Contact Database Part 3, built in the core, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In lesson one, we are going to begin working with miscellaneous data. This is all that stuff you might want to save about some of your customers, but not all of them, like shoe size, nickname, or favorite baseball team. You want to put these in individual fields so maybe you can do some reporting or sorting on it, and you want to store this in something other than just a notes field. But it is not something you want to put in the entity table. You might not have it for everybody, but you want your people to be able to type in this information.

We will make a list of types: shoe size, nickname, favorite baseball team, and so on. The data table to hold that in will be thrown in a subform in our entity form, like the rest of those guys. We will put on the bottom the data was entered and that data was updated, so every time they make a change, you will also be able to see when that information was updated. That is good to know because you can also use this for questionnaires. If you are sending out questionnaires for people, you might want to know when it was entered or last updated. That is all in lesson one.

In lesson two, we are continuing to work with the miscellaneous data table. If any of these items are set as for everyone, those are the default items that everyone gets. We are going to lock them so you cannot change or delete them. Then we will use some conditional formatting to mark those ones as gray, so it is indicated that you cannot change those. When we go to a new entity, if they do not have those records yet, we will add them automatically. For example, right here, everyone will get shoe size and Starfleet rank automatically added to their account when you go to their miscellaneous tab the very first time. I finally get to use a record set to do that.

Then we will be able to specify certain items, like baseball team here, for example, can be picked from the helper data table. So we will make a list of baseball teams in there. We will join those two things together. You pick "favorite baseball team," then a combo box appears down at the bottom where you can pick your baseball team, and then the text goes up top for you automatically.

In lesson three, we are finishing up miscellaneous data. We are going to make a "force from list" option. If you say that the user has to pick from the dropdown list for this particular question, they have to pick from the list. They cannot just type in what they want. For example, college majors - they have to pick from a list of college majors.

Then we will make the miscellaneous type editor form, where we have our list that goes into the miscellaneous types. Here you can see, for example, shoe size, nickname, and so on. Favorite baseball team - you have to pick from a list of baseball teams. Where does that list come from? The helper table, of course. Then they can specify whether you have to force an option from the list and whether this is for everyone or not, which we did in the last lesson, so it automatically puts those in for new people. Lots to do in this lesson.

In lesson four, we are going to be doing some housekeeping. We are going to make an admin menu, and put those buttons like helper data and relation types on the admin menu. We are going to set up a password that you need to open the admin menu. We are going to put proper user logon and security in this database when it is done. It is easier to do at the end. For now, we will just make a single admin password. It will be stored in your code, so it will be secure. You will have to type that in to open the admin menu. Once the admin menu is open, you will be treated as an administrator, so just leave that menu open.

We will put the open form right code in here, which makes a form open to the right. Even with another form, it makes it look cleaner.

We will make the helper lists system values. The helper lists themselves, like groups and such, need to also be system values for the campaign change or be edited by a non-admin. We will set up simple admin security like I talked about. We will set up an is-admin function so that we can very easily see if the person using the database is an administrator, then we will do something where the admin users can change those system values.

The helper list items and the helper value items, you will get a prompt. If you are an admin, it will say, "This is a system value, are you sure you want to change it?" So you will be allowed to do it, even though you probably should not. That is in lesson four.

In lesson five, we are going to work on the group edit button. On our group list, we will make an edit button that only appears if you are an administrator since we have an admin form now. If you are not an admin, that will not show up there. Click on that and it will open up the helper data editor, which is where the group information is, put you right into groups, and let you edit that list. Close that form, and when it goes back to the group list, it will update that list for you.

Then we are going to work on an address block, or an address copy block. You can see down here in the bottom right corner. This is an address block that is put together based on all these fields. If these fields are blank, it will grab the first name and company name off the entity form if you leave these buttons here, auto-checked. So it will grab those.

If you do not want that, turn these off and it will not copy that. It will leave that blank. For example, for your home address, you might not want your company name to auto-fill in, and you might not want your personal name to auto-fill in for the address. That is what I put these here for, so you can change those.

The purpose of this is until we get the address writer going, or in general, if you want to use Microsoft Word to write a letter, all you have to do is click once on that and it will copy this block to your clipboard. So you can go over to Word or Excel or whatever you are using and paste that in. That is an address block. I am not sure you had to copy stuff to the clipboard.

Lesson six has a lot of crazy stuff in it. Some more housekeeping. We are going to force the email tab to be the first tab that is displayed. Just in case you leave it on a different tab, and you come back to that form, it may stick with a different tab there. So you want to force that to always be the first one, the email tab.

We are going to show the current age next to both date of birth and date created. Date created is like your customer "since," so you know this guy has been a customer for two months, for example, and you can see that I am 48. This way, if you are going to call the customer, you get a general idea of how old they are, and that could be handy on a sales call. I will give you my "how long" function that it took me hours to write to my code vault. We will put it in this database.

We will make the first email address, phone number, or address that is added for each person set as the primary automatically. Right now, you have to click it. We will do it so if you enter one and there is not a primary, it makes the first one primary.

Then we will make a "switch tabs" function because there is a lot going on. Now, when we switch from tab to tab, we are changing colors and changing what forms are underneath there. I am going to do some stuff with the focus to jump the focus to a new record on the bottom there, so you can just click on the email tab and then start typing.

Then we will turn the flash off with focus because as things are redrawing on the screen, you will notice a little bit of flashing. So we will turn that off with the echo command. Lots to do in lesson six, the long lesson.

In lesson seven, we are going to build form slide outs. That means we are going to start the form small, and then, for example, the entity form. When someone clicks on one of those tabs, the email, phone, address tabs, or the little button on the bottom, it will expand the form and then show the subform. We are going to do the same thing on the entity list form because we are preparing for a section on that form where we are going to have the advanced searching stuff in the next lesson.

As a reminder, the ABCD is not a teaching database. This is a production-quality database. I do record video with each lesson to show you exactly what I have done, so you can customize the database to fit your needs. However, I will not be covering the details of all of the concepts as I go over them. I will try to mention which other classes stuff is covered in. But if you have questions, please feel free to drop me an email or post a comment in the forum.

This database does require parts one and two. I will be using Microsoft 365, which is roughly equivalent to 2019, but all the techniques that I have covered so far pretty much work back to 2007.

Got questions? Post them right down below on the lesson page or in the access forum. If you want help customizing the ABCD for your own needs, contact me on my consulting page. And of course, there is my direct email.

Thank you.

Quiz

Q1. What is the purpose of the miscellaneous data feature in the database described in this lesson?
A. To store general notes about every entity
B. To store unique, optional data for some customers that is not suited for the main entity table
C. To log every database operation performed by the user
D. To manage password security for the whole database

Q2. How is the miscellaneous data incorporated into the user interface?
A. As checkboxes on the main form
B. As a subform inside the entity form
C. On a separate, stand-alone form accessed through the main menu
D. Directly inside the notes field

Q3. What information is tracked for each miscellaneous data entry?
A. Only the data value itself
B. The user who entered it
C. The date the data was entered and last updated
D. Whether it appears on printed reports

Q4. What happens to miscellaneous data items set as 'for everyone'?
A. These are hidden by default
B. These items are optional for users to add
C. These default items are automatically added to every new entity and cannot be changed or deleted
D. These items are never visible on the form

Q5. How does the system visually indicate that certain miscellaneous data items are locked for editing?
A. By changing their border color to red
B. By highlighting them in yellow
C. By marking them as gray through conditional formatting
D. By hiding them from the list

Q6. How are values, such as "favorite baseball team," selected for certain miscellaneous data items?
A. Users type them manually every time
B. A combo box linked to a helper data table is used for selection
C. They are hard-coded in the form's VBA
D. By right-clicking the field

Q7. What feature ensures that users must select from a list for certain miscellaneous data fields?
A. Required property set in the table
B. "Force from list" option
C. A system-wide security lock
D. Form password protection

Q8. What role does the miscellaneous type editor form play?
A. It handles admin security and passwords
B. It manages which miscellaneous types exist, their helper lists, and whether options should be forced from a list or be for everyone
C. It controls the address block behavior
D. It generates customer reports

Q9. What is added to the database for admin management in lesson four?
A. A backup and restore menu
B. An admin menu with password protection and security setup
C. Invoice printing and mailing functionality
D. An automatic license validator

Q10. What is the function of the is-admin function discussed in lesson four?
A. To display which user is logged in
B. To automatically generate usernames
C. To easily check if the current user is an administrator
D. To lock the database after a certain period

Q11. How does the database handle editing of system value items in helper lists for admin users?
A. Admins cannot edit system values at all
B. Admins get a prompt warning before they can change system value items
C. Admins can edit without any warnings
D. System value items are auto-archived

Q12. What special behavior is provided for the group edit button?
A. It is visible to all users
B. It only appears for administrators and allows them to edit group information directly
C. It forces a backup before editing groups
D. It emails the admin every time it is clicked

Q13. How does the address block/copy block feature work?
A. It automatically mails the address to all contacts
B. It copies a formatted address block to the clipboard for use in other applications
C. It prints mailing labels directly
D. It exports address data to Excel weekly

Q14. What enhancement is made to the email tab feature in lesson six?
A. The email tab is hidden from non-admin users
B. The email tab is always set as the first tab displayed when reopening the form
C. The email tab sends auto-generated messages upon entry
D. The email tab is used to reset passwords

Q15. Which routine is used to show how long a customer has been with the company or their current age?
A. DateDiff
B. CustomerSince
C. HowLong
D. DateFormat

Q16. What automatic behavior is set for primary email, phone, or address entries?
A. The last one entered is always set as primary
B. The first entry is made primary if there is not already a primary set
C. Users must always manually set the primary
D. All entries are set as primary by default

Q17. What solution is implemented to reduce screen flashing as forms are redrawn?
A. Turning off the computer's hardware acceleration
B. Using the echo command to suspend screen updates
C. Minimizing the form before updating
D. Disabling all animations in Windows

Q18. What do "form slide outs" accomplish in lesson seven?
A. They print the current form to a PDF
B. They start forms small and expand them when certain tabs or buttons are clicked, revealing subforms
C. They move the form to another monitor
D. They log all form activity to the database

Q19. What is NOT covered in detail in the ABCD lessons as per the instructor's disclaimer?
A. Step-by-step teaching of all underlying concepts
B. Video demonstrations of completed features
C. Information about related classes
D. Customization advice

Q20. What is required before using this part of the database?
A. A paid subscription to AccessLearningZone.com
B. Microsoft 365 only
C. Completion of parts one and two of the course
D. Knowledge of VBA programming

Answers: 1-B; 2-B; 3-C; 4-C; 5-C; 6-B; 7-B; 8-B; 9-B; 10-C; 11-B; 12-B; 13-B; 14-B; 15-C; 16-B; 17-B; 18-B; 19-A; 20-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 takes you through Part 3 of building the Access Business and Contact Database, or ABCD for short. I am Richard Rost, your instructor, and in this lesson series, we will cover a range of new features and techniques designed to make your database more flexible and user friendly.

In the first lesson, we begin by working with miscellaneous data. This is information you might want to store for only some of your customers and not others; things like shoe size, nickname, favorite baseball team, and other personal details. These bits of information should be stored in individual fields so you can analyze and sort them later, rather than being buried in a generic notes field. At the same time, they do not quite fit into the main entity table. These types of data entries are optional and may vary from person to person, but you still want users to have the ability to record them.

To organize this, we create a list of types, such as shoe size or favorite baseball team, and set up a data table to hold these specifics. This information is displayed in a subform within the main entity form, much like our previous setups. We also implement fields to track when each piece of data was entered and last updated. Knowing the date of the most recent update is particularly handy if you are using this information for questionnaires or other time-sensitive purposes. That is our focus in lesson one.

Moving on to lesson two, we continue with the miscellaneous data table. Certain data items might be defined as necessary for all users. These become default fields, automatically added to every record. We lock these standard items so they cannot be changed or removed, and we use conditional formatting to visually indicate which items are locked by graying them out. When someone creates a new entity and visits the miscellaneous tab for the first time, any missing default entries like shoe size or Starfleet rank are added automatically. We use a record set here to perform this action.

For specific items such as favorite baseball team, we use a helper data table containing the list of teams. When 'favorite baseball team' is selected as a category, a combo box appears, allowing the user to make a selection from the list. Once chosen, the selected team is displayed appropriately. This process links the miscellaneous data to the associated value from our helper table.

In lesson three, we finish the work on miscellaneous data by introducing a "force from list" feature. When this setting is enabled for a particular data field, users must choose from a predefined dropdown list rather than typing a custom response. This is useful for fields like college major, where you want standardized entries.

We also develop the miscellaneous type editor form, which manages the types shown on the miscellaneous data subform. Here, you can specify not only what data types are available, but also where their associated value lists should come from, whether or not a response must be picked from a list, and if this question is universal for all entities. As in the previous lesson, universal questions are automatically added for new entities. There is a lot to accomplish in this section.

Lesson four is focused on housekeeping and administrative tools. We create an admin menu and move buttons for tasks like editing helper data and relation types into this area. To secure the admin menu, we put a password on it; the password is stored securely in the code. For now, we will use a single admin password, which will allow us to implement more comprehensive logon and security protocols at a later stage. Once logged in, administrators should keep the menu open to retain access.

We will write code that opens forms to the right, resulting in a cleaner-looking user interface even when multiple forms are open. The system values for the helper lists also need to be protected. These lists and their values should only be editable by administrators, which is managed by marking them as system values and applying user rights controls. For any attempt to change these items, admins will receive a prompt confirming their action. This ensures that only authorized users can modify critical list values.

Lesson five covers the group edit functionality. We add an edit button to the group list, which only appears for administrators. Clicking this button opens the helper data editor directly to the group records, allowing admins to make updates. Once changes are made and the form closed, the group list automatically refreshes.

Additionally, we tackle the address block feature. This utility constructs an address block from the various address-related fields. If certain fields are left blank, the system can automatically pull in the first name or company name from the main entity record—unless you choose to disable that option. This is particularly useful if you do not want to always include a company or personal name in every address. The idea here is to allow quick copying of a complete address block to the clipboard, which can then be pasted into an email, Word document, or elsewhere. This provides a simple but effective address management tool until more advanced address writing features are in place.

Lesson six dives into more details and some finer points of usability. We ensure that the email tab always opens by default when accessing the record. That way, if you left the form on a different tab previously, it will reset to the correct starting tab the next time you open it.

We display the current age of an entity next to both the date of birth and the account creation date. This gives an immediate reference for how long someone has been a customer or how old they are, which can be beneficial for sales or customer service interactions. We implement a "how long" function to display this information clearly.

We also automate the designation of primary email addresses, phone numbers, and addresses. Now, the first entry for each will be marked as primary by default, removing the need for manual selection.

A new tab-switching function is introduced to handle changes in tab selection. This streamlines the form's appearance and allows for quick navigation, shifting focus directly to where information should be entered. We also minimize screen flashing during these transitions by carefully managing focus and using the echo command to control form redrawing.

Lesson seven introduces form slide outs. We design forms to start compact and then expand when a user selects items such as email, phone, or address tabs. This approach keeps the interface clean while providing easy access to subforms as needed. We do the same for the entity list form to prepare for upcoming advanced search features.

Remember, the ABCD database is designed for practical, real-world use and not specifically as a teaching tool. Although I make video recordings of each lesson to show you what I have built, I may not go into full theoretical depth on every topic. Where relevant, I point out which classes offer more details. If you have questions along the way, you can reach out via email or post a comment on the forum.

It is important to have completed parts one and two before starting this section. I use Microsoft 365 for the demonstrations, but you will find that the same techniques typically work with Access 2007 and newer versions.

If you need help or want to customize the ABCD for your specific needs, feel free to leave a comment, visit my consulting page, or reach out by email.

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 miscellaneous data tables for optional customer info
Adding miscellaneous data subforms to entity forms
Tracking data entry and update timestamps for miscellaneous data
Implementing default miscellaneous data items for all entities
Locking and disabling editing of default items
Applying conditional formatting to indicate locked items
Automatically adding default misc data items for new entities
Using recordsets to add records programmatically
Configuring dropdown lists linked to helper data tables
Creating combo boxes for specific miscellaneous data entry
Enforcing selection from pre-defined lists for certain fields
Building a miscellaneous type editor form
Managing "force from list" and "for everyone" options in editor
Creating and protecting an admin menu with password access
Restricting helper data and relation types to admins
Opening forms to the right for a cleaner interface
Designating helper list items as system values
Prompting admin users before editing system values
Implementing group edit functionality with admin-only access
Building an address block and clipboard copy feature
Controlling auto-fill behavior for address blocks
Forcing the email tab as default on entity forms
Displaying calculated age from birthdate and account creation
Automatically setting primary email, phone, or address records
Implementing a tab switching function for forms
Reducing form redraw flashes with echo command
Creating form slide outs for subforms and tabs
Expanding and contracting entity and entity list forms with user actions