|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Access Developer 9 Welcome to Access Developer 9. Total running time is 2 Hours, 32 Minutes. 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 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 lesson 9, you will learn how to make additional improvements to the partial payments system in the Microsoft Access order entry database, perform housekeeping tasks like fixing issues and enhancing form functionality, and begin building a brand new Test-Taker database from scratch. We will walk through the process of table design, setting up relationships, and creating key forms, including a question form with subforms and a test-taking form using combo boxes, filters, and SQL. This lesson includes plenty of practical examples and techniques you can apply to any Microsoft Access project.TranscriptWelcome to Microsoft Access Developer Level 9 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's class, we are going to do some additional enhancements and modifications to the partial payments system that we worked on in our order entry database in previous classes, Level 8 and before. We are also going to start a new project today. We are going to start building a Test-Taker database. Even if your business or personal needs have no reason to build a Test-Taker, this lesson is going to have a lot of new information and a lot of cool stuff for you. I get a lot of requests from people who have been asking me to start a new project and to show some additional ways to set up tables and relationships. So we are going to get into starting a new project. We are going to go back to the order entry database, obviously, but we are going to take a little side trip here and build a brand new database. You can use this in a lot of different types of businesses, such as employee certifications and all that kind of stuff, but the lessons and concepts that are going to be covered are vital. Let me get back into our order entry system. So this is one of those things where, trust me, even if you think you do not need it, watch the Test-Taker lessons. There is a lot of cool stuff in there. For prerequisites, of course, you should have taken Access Developer Level 8 and everything that comes before it, including my Beginner, Expert, and optionally the Advanced series. The Advanced series covers macros, but there is still a lot of good stuff in there with event programming. For those of you who have never taken my classes before, they are broken up into Beginner, Expert, Advanced, and Developer. This is the Developer series, and every series is broken up into different levels. This is Level 9. The student databases for today's class can be found at 5NNCU.com/databases. I am recording using Microsoft Access 2016 with Office 365. However, everything covered in today's class should work all the way back to Access 2007. In fact, now that I am thinking about it, it probably will work with 2003 and before as well. I really did not do too much different stuff. I think you are good all the way back to 2003. But if you are using 2003, you really should consider upgrading to 2016. It is getting kind of old now. Alright, so let's take a look at exactly what is covered in today's class. In lesson one, we are going to take some time and do some miscellaneous housekeeping. A bunch of little things that came up over the last couple of lessons that I am going to fix today. For example, we are going to add our search form to our main menu. We are going to have it open a customer when you double-click on it. We will show or hide the manager button. We have some problems when we delete a payment - it needs to mark the order unpaid. It is a lot of little things like that that we are going to go over in this lesson. In lesson two, we are going to begin a new project called the Test-Taker database. I have gotten lots of emails from people throughout the end of the Expert series, the Advanced series, and even now in the Developer series. People have been asking me if I could take a little more time and go through setting up a new database with table layouts, structures, and relationships - just some different examples rather than the customers and orders. I do have the Relationship Seminar, which is a great seminar. Watch it. It covers all the different types of relationships. But I started recording a different seminar. I was going to make this Test-Taker thing a separate seminar a few years ago. I started it, in fact. I recorded a bunch of video for it, which you are going to see in just a few minutes. But I was thinking to myself, this is the perfect database to show you again how to set up a new database with some different tables. We are basically going to be building a database where students can take a test on the computer. It will ask questions, and you can have multiple choice, fill in the blank, and even though this might not be something relevant to your business or what you are using Access for, I am going to cover a lot of new techniques. The first lesson or two is just going to be some database setup, but do not worry. I am going to add extra lessons onto the end, so that is why today's course is going to be a little bit longer. The first lesson or two is just table setup and relationships and stuff. But watch it because I want you to follow along with it. If you are struggling at all with table structure and relationships, then take the time. I know this lesson is about 40 minutes long. Watch it because I go through setting up all the different tables and make sure you comprehend exactly what I am doing. As you can see here in the screenshot, you get departments like Math, English, Science, and they relate to certain classes like Math 101, Math 102, Math 103. Each class can have different tests, each test can have different answers. There are students as you can see in the bottom there. So I want you to see how all the relationships work between the different tables. I want you to understand the referential integrity and what is going on there. So this lesson is mostly going to be review. But when we get into the actual taking of the test stuff, we are going to get into some really cool VBA. I am going to show you how you can have the database actually ask the questions and store the results in the table. We will do some SQL and some really cool stuff. This is just going to be a little bit of setup involved before we get to the really cool stuff, which we will do very shortly. Alright, so enjoy this. You will notice the date. I actually recorded this back in 2016, but we are going to do more advanced stuff with this soon. So enjoy. In lesson three, we are continuing with the Test-Taker database. We started in part two. We are going to build some supporting forms. In lesson four, we are continuing with our Test-Taker. We are going to build a question form with an answer subform right below it so we can very easily and quickly add new answers or change them, delete them, whatever. We will have a check box we can indicate which answers are correct. In lesson five, we are continuing with our Test-Takers as part four. We are going to make a form to begin actually taking the test. We will have different combo boxes so you can pick a student and then we will drill down department, class, test. We will disable them as we go along. So when we pick Science, it will filter the class and show you just the classes from that department, and so on. We will make a begin test button that will assign the Test-Taker ID with a little INSERT INTO SQL statement. Then we will use DMax to look it back up again so we can use it for actually taking the test. So that is going on in this lesson. QuizQ1. What is the main focus of Microsoft Access Developer Level 9?A. Enhancing the partial payments system and starting a new Test-Taker database project B. Reviewing only the partial payments system in the order entry database C. Designing reports for employee management D. Focusing on importing and exporting data to Excel Q2. Why does the instructor encourage all students to watch the Test-Taker lessons, even if they do not need a Test-Taker system? A. The lessons cover essential database concepts and techniques applicable to many scenarios B. The Test-Taker database is required for all Access projects C. The examples are only relevant for schools and universities D. The Test-Taker database is the only way to learn about user forms Q3. Which of the following is NOT mentioned as a prerequisite for this class? A. Access Developer Level 8 B. The Beginner series C. Microsoft Access Mastery D. The Expert series Q4. What is a key topic covered in lesson one of this course? A. Database performance tuning B. Miscellaneous housekeeping and minor fixes in the order entry system C. Importing data from external applications D. Advanced SQL Server integration Q5. What is the primary purpose of starting the Test-Taker database project? A. To demonstrate new examples of table layouts and relationships B. To replace the order entry database completely C. To practice report design techniques D. To create a payroll management system Q6. What concept related to databases does the instructor emphasize when setting up the Test-Taker database? A. Referential integrity and relationships between tables B. Data encryption and security C. Automatic backup scheduling D. User interface customization Q7. In lesson four, what new feature is added to the question form in the Test-Taker database? A. A report generator for test questions B. An answer subform to easily manage answers and indicate correct ones C. A direct export to Excel feature D. A countdown timer for timed tests Q8. What technique is introduced for assigning the Test-Taker ID when beginning a test? A. Using an INSERT INTO SQL statement and DMax to retrieve the new ID B. Manually entering the Test-Taker ID into a form C. Generating a random number as the ID D. Importing the ID from an external file Q9. What motivation does the instructor give for revisiting table structures and relationships in this lesson? A. To ensure students understand these fundamental concepts B. Because it is required by Microsoft C. To prepare for exporting data to cloud services D. To eliminate the use of VBA entirely Q10. What version of Microsoft Access does the instructor use for recording the class? A. Access 2016 with Office 365 B. Access 2003 only C. The very latest preview version D. Access for Mac OS Answers: 1-A; 2-A; 3-C; 4-B; 5-A; 6-A; 7-B; 8-A; 9-A; 10-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. SummaryToday's video from Access Learning Zone covers Microsoft Access Developer Level 9. I am your instructor, Richard Rost.In this course, we will focus on further enhancing and refining the partial payments system that we developed in our order entry database in earlier classes, specifically Level 8 and prior. Alongside these upgrades, we are also embarking on something new today - the creation of a Test-Taker database. Even if you do not have a direct need for a Test-Taker system in your business or personal projects, this lesson still contains several useful concepts and new techniques that can benefit you in a variety of scenarios. Many students have reached out requesting fresh examples of database design, especially ones that go beyond standard customers and orders, so we will use this opportunity to start a new project. While we will return to our order entry system later on, this temporary shift will enable us to cover important principles related to table and relationship setup, which are valuable no matter what type of database you build. You might eventually use this structure for things like employee certifications, but regardless of the specific application, the underlying lessons are significant. Before we begin, I want to remind everyone about the recommended prerequisites. You should have completed Access Developer Level 8 and all prior courses, including the Beginner, Expert, and, optionally, the Advanced series. The Advanced series focuses on macros, but it also contains useful information about event programming. For those unfamiliar with my classes, they are organized into Beginner, Expert, Advanced, and Developer tracks. Each track is divided into multiple levels, and this is Level 9 in the Developer series. You can find the student databases used in this class on my website at 5NNCU.com databases. This tutorial was recorded using Microsoft Access 2016 on Office 365, but all material presented will also work with older versions, going back to Access 2007. In fact, much of what we will be doing today could even be adapted for Access 2003. However, if you are still using 2003, I strongly encourage you to upgrade, since it is becoming increasingly outdated. Let me run through the structure of today's course. Lesson one focuses on what I like to call miscellaneous housekeeping. We will tackle a number of small but important issues that have popped up in recent lessons. For instance, we will add the search form onto our main menu, enable it to open a customer record upon double-click, and introduce logic to show or hide the manager button as needed. There are also issues with deleting payments; when a payment is deleted, we must ensure the order is correctly marked as unpaid. These are just a few examples of the refinements handled in this segment. Lesson two marks the beginning of our Test-Taker database project. Requests for walkthroughs of new database builds and alternative table relationships have been coming in for a while, and this is the perfect way to address those. While I do have the Relationship Seminar, which is a comprehensive look at all types of Access relationships, I also began recording a separate Test-Taker seminar some time back. You will see some of those recordings in this course. This Test-Taker database provides an excellent opportunity to revisit from scratch how to plan and implement table structures and their relationships. We will build a system that allows students to take tests on the computer, using question types like multiple choice and fill in the blank. Even if you do not need a test system, follow along for exposure to new techniques. The first couple of lessons are all about laying the groundwork - designing tables, setting up relationships, and organizing our structure. I know this takes a bit of time, but even if you think you are comfortable, this review is helpful. Pay close attention as I explain all the relationships, including how different departments (such as Math, English, Science) connect to their classes (like Math 101, Math 102, Math 103), and how classes tie to individual tests and answers. Students are integrated into this system as well. My goal is to ensure you can confidently handle referential integrity and understand how everything fits together within the database. Much of this will be a review, but as we move into programming the actual test-taking interface, we will explore some advanced VBA techniques. The database will be able to present questions and record responses directly to the tables. We will use SQL and other practical skills to make this interactive and robust. The setup phase will be fairly brief, and then we will progress into more complex features very soon. You may notice that some of the sample content was originally recorded in 2016. Rest assured, we will build on that base and add more advanced components as we continue. So stay tuned. In lesson three, we keep working on the Test-Taker project and develop the necessary supporting forms. Lesson four continues this work, focusing on building a question form that incorporates an answer subform. This makes it easy to add, change, or remove answer options, and you will be able to select which answers are correct using a simple check box. Lesson five moves us forward to creating a form where the test-taking actually happens. This form will let you select a student, and then refine your selection by department, class, and test, using linked combo boxes. As users make selections, options will be filtered accordingly - for example, choosing Science will show only classes in that department. We will build a "begin test" button that creates a new Test-Taker record and makes use of DMax to retrieve its ID for the ongoing session. If you want a step-by-step walkthrough of all these features and more, you can find a complete video tutorial with detailed instructions on my website at the link below. Live long and prosper, my friends. Topic ListAdding a search form to the main menuOpening customer records with double-click Showing or hiding the manager button Handling deletion of payments to mark orders unpaid Introduction to the Test-Taker database project Designing table structures for Test-Taker Setting up relationships for Test-Taker tables Understanding referential integrity between tables Creating department, class, test, and student tables Building supporting forms for Test-Taker database Creating a question form with answer subform Indicating correct answers with a checkbox Building a form to begin taking tests Using combo boxes to select student, department, class, and test Filtering combo boxes based on selections Disabling and enabling fields dynamically Creating a Begin Test button with SQL INSERT INTO Using DMax to retrieve newly inserted Test-Taker ID |
||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: access developer 09 developer 9 Add Search Form Open Customer Show Hide Manager Button Delete Payment Problem Form Current Event Mark Unpaid Lock Unlock Enabled Locked Properties Subforms Build Tables Table Relationships Referential Integrity Multiple Cho PermaLink Microsoft Access Developer 9 |
