|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Access Developer 40 Lessons Welcome to Access Developer 40. Total running time so far is 123 minutes plus 82 minutes of FREE bonus material. Lessons
Database FilesNotes
TechHelp Links
D40 Links
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 UpdatesIf 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.
IntroIn this lesson, we will work with Microsoft Access VBA to change conditional formatting, allowing users to set up custom color schemes and fonts for forms and reports. We will build an event countdown form, use the Windows Color Picker and Font dialogs, edit and save color schemes, handle hex and RGB values, and manage conditional formatting with VBA. You will also learn about user defined types and how to save and apply font selections for individual records and reports. This is Access Developer Level 40.TranscriptWelcome to Microsoft Access Developer Level 40 brought to you by AccessLearningZone.com. I am your instructor Richard Rost.Today's class is going to focus on changing conditional formatting using VBA. Now, this might not sound very impressive, but you can have your user set up custom color schemes for things on forms and even reports. We will learn how to work with the Windows Color Picker dialog where you can click a button that will open up the color palette. You can select the color and save that in your table. We will learn how to work with the Windows Font dialog box as well. We are going to build a countdown timer form which shows you how many days you have left until certain events are coming up. That is how we will give the user the ability to set the custom fonts and the custom colors for each of their custom color schemes. Lots of cool stuff in this lesson. This lesson follows Access Developer Level 39. I strongly recommend you have finished all of my previous classes: beginner, expert, advanced, and developer levels before continuing with today's class. My lessons are really designed to be followed one after the other. For example, this one relies heavily on recordsets which is covered in Developer 16. So make sure you have that one under your belt as well. This class is recorded with Microsoft Access as part of a Microsoft 365 subscription which I strongly recommend. It's currently August of 2022. Also, if you're using the retail version, this is roughly equivalent to Access 2021. However, the lessons covered in today's class should work with any version of Access going back to 2007. If you have any questions regarding the material covered in today's class, just scroll down to the bottom of that page and post a question on the page that you're on. Make sure you take a minute to read through any other questions that have been posted as your question may have already been answered. Be sure to click the subscribe button to get notified if any other questions or comments are posted for this class. If you have any questions not related to today's class, but they are about Access in general, feel free to post them in my Access forum. Now, let's take a closer look at exactly what is covered in Access Developer Level 40. In lesson one, we are going to build an event countdown form. So, if you have some events coming up - conferences, beach days, surgery, whatever - this will pop up whenever your database loads to remind you that you have this conference coming up tomorrow. Or you have to go to the beach in four days. We will create the display form and add a little conditional formatting for if things are coming up soon. One day is red; less than five days will be yellow. We will make buttons to add, edit, or delete events using code. We will make a little form to edit those events, a pop-up form, and more. In lesson two, we are continuing with our event countdown form. We are going to add some conditional formatting and let the user pick a color scheme, so they can choose red, blue, black, green, and that will be applied for each of the events, whichever one they want. In lesson three, we are going to build a custom color editor. It's a form to edit our color scheme. We will learn how to work with hex and RGB values and convert between them and use the Windows Color dialog so the user can select the color and then save it in our color scheme form. In lesson four, we are going to begin working with the conditional formatting VBA code. We will learn about the Format Condition object, the Format Conditions collection, and how to delete and add them. We will determine the maximum number of conditional formatting items that you can have. Then we will count the records in the current form so we do not add too many of them and generate errors, and I will show you how to do that with something other than DCount. In lesson five, we are continuing with the conditional formatting VBA. We will add the additional controls on the form to the conditional formatting loop, including the number box and the background box. We will add some code to open up a window to the right of another form. For example, we always want the event countdown form opening up to the right of the main menu form no matter where it is. That is pretty cool code. Then we will do the refresh events from the color form. If you have the color form open and you refresh that, it will close and reopen the event countdown form so you can see the updates in real time. Then we are going to see some updates to the color picker and the RGB and hex functions, and we will explore those in the code vault. In lesson six, we are going to learn about user defined types. We will learn what user defined types are in VBA, how to define them, how to pass a variable around that is a user defined type, and how it always uses ByRef. In lesson seven, we are going to learn how to use the Windows font dialog so that we can change fonts at runtime, so the user can pick the font for the event and for the days left. You can use the same technique to pick the font in a report, for example. We will learn how to use the Windows font dialog, how to save those changes in a settings table, and then load them back up when the form loads. In lesson eight, we are going to apply the font features that we learned in lesson seven and apply them to our letter writer. We will be able to specify a custom font for each letter. Each letter can have a different font if you want. You can save that in the letter settings in its record. There are some issues when applying these fonts to reports, and we will cover that in lesson eight. QuizQ1. What is the primary focus of Microsoft Access Developer Level 40?A. Learning about new database relationships B. Changing conditional formatting using VBA C. Working with Macros and automation D. Integrating Access with Excel Q2. Which dialog boxes will you learn to work with in this class? A. File open and save dialogs B. Date picker and filter dialogs C. Windows Color Picker and Font dialog boxes D. Print and export dialogs Q3. What will the event countdown form in lesson one display? A. A list of recent records added B. Days left until certain events occur C. User login activity D. Backup schedules for the database Q4. What is the purpose of allowing users to pick custom color schemes? A. To organize the records by category B. To set different backgrounds for different databases C. To let users customize the appearance of events and reports D. To improve database security Q5. When adding conditional formatting using VBA, what concepts will be covered? A. Working with the Format Condition object and collection B. Sorting and filtering forms C. Designing new queries D. Creating new tables automatically Q6. What numerical systems will you learn to work with in the color editor form? A. Binary and octal B. Hex and RGB C. Roman numerals and decimal D. Binary and base64 Q7. How will users be able to save their font choices for events? A. By saving them in a macro group B. By saving them in a settings table C. By emailing preferences to the admin D. By printing out their choices Q8. What advanced VBA concept is introduced in lesson six? A. Named parameters in queries B. User defined types C. Loop optimization D. Data validation rules Q9. How can users see updates to the event countdown form after changing colors? A. By restarting Access B. By clicking a special refresh button on the desktop C. By refreshing the color form, which closes and reopens the countdown form D. By recompiling the VBA project Q10. What is a possible issue covered when applying custom fonts to reports? A. Dates not displaying correctly B. Missing fonts not displaying as expected C. Summaries not calculating correctly D. Automatic emailing not functioning Answers: 1-B; 2-C; 3-B; 4-C; 5-A; 6-B; 7-B; 8-B; 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. SummaryToday's video from Access Learning Zone covers Microsoft Access Developer Level 40. In this lesson, the main topic is working with conditional formatting through VBA. While that might sound simple at first, what we are actually going to do is much more interesting for your applications. We'll give users the ability to customize the color schemes for forms and reports. They will be able to select colors using the Windows Color Picker dialog and have those preferences stored in the database. We will also cover how to use the Windows Font dialog so users can select their preferred fonts as well.One of the main projects in this class is building a countdown timer form, which will help users track the days left until certain events occur. This application will showcase how users can select custom fonts and colors for their own color schemes. There is quite a lot to learn in this lesson, and it all builds on skills from the previous Developer classes. This class picks up right after Access Developer Level 39. If you have not completed the earlier classes, including all of the Beginner, Expert, Advanced, and prior Developer levels, I strongly suggest you review those before starting here. My courses are designed to be followed in order. For example, this lesson makes significant use of recordsets, first taught in Developer 16, so you want to be comfortable with that before proceeding. The recording uses Microsoft Access as part of a Microsoft 365 subscription, which I highly recommend. As of August 2022, this is similar to Access 2021, but everything in today's class should work fine in versions as old as Access 2007. If questions come up about the material in today's class, scroll to the bottom of the appropriate page on my website and post your question there. Please spend a moment to check if your question has already been answered. If you want notices about new questions and answers for this class, make sure to subscribe to that page. If your question is general and not specifically related to today's class, you are welcome to bring it to the Access Forum on my website. Let me provide an outline of what we cover in Access Developer Level 40: Lesson one centers on building the event countdown form. This form notifies users about upcoming events when the database opens, such as conferences, deadlines, appointments, or any key dates you want to track. It displays how many days are left for each event. If an event is just one day away, it highlights it in red, and if it's less than five days away, it highlights it in yellow. We will program buttons so users can add, edit, or delete events, all through code. We will also create a pop-up form for editing events. In lesson two, we extend the event countdown form by including custom conditional formatting based on the user's preferences. Users will be able to choose color schemes such as red, blue, black, or green for their events, and those choices will be reflected in the display. Lesson three is about building a custom color editor. Here, users can edit their color schemes in a dedicated form. You will learn how to work with hex and RGB values, convert between them, and use the Windows Color dialog so colors can be selected intuitively and saved to the color scheme. Lesson four introduces writing VBA code for conditional formatting. We discuss the Format Condition object and the Format Conditions collection, exploring how to add and delete these formatting conditions. We will look at the maximum number of conditional formatting rules Access allows and learn how to count form records in a safe way to avoid errors, using an alternative to DCount. Lesson five continues the conditional formatting logic. We add extra controls to the formatting loop, such as number boxes and background boxes. We also write code that ensures when the countdown events form opens, it always appears to the right of the main menu form, no matter where that window is situated. We discuss refreshing events from the color form, so when you update color choices, the countdown form will automatically reflect the changes right away. There are also improvements to the color picker and conversion functions, all of which we will review in the code vault. Lesson six introduces user defined types in VBA. We explore what user defined types are, how to define them, and how to pass these types as variables, noting that they always use ByRef. Lesson seven focuses on integrating the Windows font dialog into our forms. This allows users to select custom fonts at runtime for their events and event countdowns. These chosen fonts are saved to a settings table and reloaded the next time the form loads. The same method can be extended to reports or other areas of your database. Lesson eight takes the new font features and applies them to the letter writer tool. This gives you the ability to set a different font for every letter, with the font saved in each letter's settings. We also cover some issues you may encounter when applying these custom fonts to reports and how to address them. 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 ListBuilding an event countdown form in AccessAdding conditional formatting to event forms Creating buttons to add, edit, and delete events with VBA Designing a pop-up form to edit countdown events Allowing users to select custom color schemes per event Building a custom color editor form Converting between hex and RGB color values Using the Windows Color Picker dialog in Access Saving selected colors in a table for color schemes Working with the FormatCondition object in VBA Managing the FormatConditions collection in Access Adding and deleting conditional formatting items with VBA Determining maximum allowed conditional formatting rules Counting records in a form without DCount Looping through controls for conditional formatting Synchronizing color scheme changes between forms Updating color pickers and RGB/hex functions Using user defined types in VBA Passing user defined type variables ByRef Integrating the Windows font dialog in Access forms Saving and loading font selections from a settings table Allowing runtime font selection for different form elements Applying custom fonts to a letter writer form Saving individual letter font settings Handling issues when applying fonts to reports |
||||||||||||||
|
| |||
| Keywords: access developer 40 lessons PermaLink How To Create Event Countdown Timers, Custom Color Editors, and Conditional Formatting in Microsoft Access |
