|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Access Developer 25 Lessons Welcome to Access Developer 25 Total running time so far is 1 hour, 51 minutes plus 23 minutes of FREE bonus material. Lessons
Database FilesLinksResources
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 25 of the Microsoft Access Developer series, you will learn about ByRef versus ByVal when passing values to subs and functions, how to return multiple values from a single function, and how to work with the active form and control in your database. We will also build product groups into the order entry system started in previous lessons, allowing you to bundle items together and add entire packages to orders with one click. Additional topics include working with object variables, setting up combo boxes to select product groups, and customizing invoice formatting to display group packages clearly.TranscriptWelcome to Microsoft Access Developer Level 25, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's class, we are going to talk about ByRef versus ByVal when passing values to a sub or function. We will learn how to determine the active form and control, and we are going to build product groups into our order entry system that we built in Developer 23 and 24. This way, we can put together packages and bundles. For example, you can have a computer package with specified components. You click one button and all of the components go into the current order. Normally, I do not say that a class has hard prerequisites, but this one kind of does. We build the order entry system in Developer 23 and then do more with it in 24. If you do not at least have 23, you will miss out on much of what we have built in this class. We built a very similar order entry system back in the Expert classes, but this one is much better. If you want to know what is going on, make sure you take at least 23, and of course, get all the rest of my classes as well. Do it for your health, for your country, and for the world. It is just the right thing to do. I will be using Access 365. I have a subscription, which is roughly equivalent to 2016/19. Who knows what Microsoft is doing with the versions nowadays? Get a 365 subscription; it is much better. As always, if you have questions, post them down below. Try not to email me unless you have something like screenshots, because I prefer everything going through the website. I will answer your question faster if you send it through the website. If you have questions about stuff not covered in this class, go ahead and post them in the forum. Let us take a look at what is covered in today's class. In lesson one, we are going to learn the difference between passing values ByRef or ByVal to your subs and functions. We are going to build an increment subroutine where we send it a variable x and have it increment by 1, and we will see how ByRef works. In lesson two, we are continuing with ByRef and ByVal. We will send some optional parameters to it so you can increment by 7 or 10 instead of just 1. I will show you how to use it with form controls so you can put a field on a form and use your subroutine with that. I will determine if it is a field or a number that we are working with. Then, we will talk about public subroutines and functions, which we have covered in previous classes, so this will be a good review. In lesson three, we are continuing with ByRef and ByVal. We will learn how to use ByRef to have a function return multiple values. Up until now, functions have always returned one value: a Boolean, a string, or whatever. In this lesson, I will show you how to have a function return multiple values. We are going to return a status code and indicate whether or not a ticket is valid (true or false). Then we will return a status code, such as "the first character is not a T" or whatever our validation happens to be. We will also return a third value: we will look up the customer's name and return that too. This is really cool stuff. In lesson four, we are going to learn about the active form and the active control. We will learn how to determine what form and what control the user is currently on. We do not have to send it as a parameter to a function. Then, we will learn how to access the previous control. For example, if you want to use a button to multiply a field by 10, the button can figure out where the user came from and what the previous control was. Instead of sending the name of the control, we are going to actually dim a control itself as an object variable and learn how to work with different control types. There is a lot of cool stuff in lesson four. In lesson five, we are going to build product groups. What is a product group? If you sell products that you want to bundle together as a package, you can create a product group. When you go to put an order in, instead of having to manually pick each part, you just pick the product group. In lesson six, we will click one button and it will automatically add the group and put all the line items in for you. In this lesson, we are going to set up the product groups. In lesson six, we are going to take the product groups that we built in lesson five. We will add a combo box onto our order form so you can pick a product group. It will add the product group up top into the order using a recordset loop, of course. Then we are going to go to the invoice and hide all the zeros, because you do not want to show zeros for all the items in the group. You just want to show the package price. We will indent those products that are zeros in the package and bold the package name. Then, although you do not see it in the screenshot, I also add some conditional formatting so those packages will show up in orange. This is a cool lesson. QuizQ1. What is the main programming topic covered in this class?A. Differences between Mac and Windows shortcuts B. Using queries to generate reports C. ByRef versus ByVal parameter passing in subs and functions D. Creating relationships between tables Q2. What prerequisite does Richard emphasize for this class? A. Developer 20 B. Developer 23 and 24 C. Excel Intermediate Class D. SQL Basics Q3. What is the purpose of building product groups into the order entry system? A. To increase storage space B. To allow users to bundle products into packages added with one click C. To restrict product selection D. To improve database security Q4. Which version of Microsoft Access does Richard recommend using? A. Access 95 B. Access 2013 C. Access 365 D. Access 2002 Q5. What concept does the class explore in handling form controls? A. Automatically formatting dates B. Determining the active form and the active control C. Sending emails via Access D. Encrypting Access databases Q6. How does ByRef allow functions to operate differently compared to ByVal? A. It prevents variable changes B. It only works with strings C. It allows a function to modify the original variable's value D. It restricts function use to private modules Q7. In lesson three, what does Richard show regarding function return values? A. Functions can return only one Boolean value B. Functions can return multiple values using ByRef C. Functions need not return any value D. Functions cannot work with status codes Q8. What does the class mention about accessing the previous control? A. It is not possible in Access B. You must always pass the control's name as a string C. It is possible to reference the previous control dynamically as an object D. Controls must be deleted after use Q9. What is done on the invoice with items that have a price of zero in a product group? A. They are deleted B. They are hidden and indented, while package names are bolded C. They are highlighted in red D. They are sorted alphabetically Q10. What additional formatting enhancement is added for product packages on the invoice? A. Green background B. Underlined text C. Shaded gray D. Conditional formatting to show packages in orange Answers: 1-C; 2-B; 3-B; 4-C; 5-B; 6-C; 7-B; 8-C; 9-B; 10-D 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 Developer Level 25 for Microsoft Access. I am your instructor, Richard Rost.In this class, I focus on working with ByRef and ByVal when passing values to subs and functions. We will explore how to identify the currently active form and control, and we will build product groups for our order entry system, which we put together in Developer Levels 23 and 24. This lesson introduces the ability to create packages and bundles in the order entry system. For example, you might have a computer package where you specify each component, and with a single button click, all of those items are added to the current order automatically. This class is a bit unique in that it really does require you to have taken Developer 23, and ideally 24 as well. We built most of the order entry foundation in Level 23 and enhanced it in 24. If you have not completed at least Level 23, you will miss a lot of the context for this class. Although we did something similar in the Expert series, the new order entry system is much more advanced. So, if you want to properly follow along, I strongly recommend working through Level 23 at a minimum, and of course, all the other classes. It is worthwhile, trust me. I am working with Access 365 for this class, which is roughly equivalent to Access 2016 or 2019. The naming and versioning can be a little confusing with Microsoft these days, so I highly recommend subscribing to Office 365 for the best experience and latest features. If you have questions about this class, please use the comment section below the video or post your question through the website. I do my best to respond to website questions as quickly as possible. Please avoid emailing me unless you need to send screenshots, since it is easier for me to track and answer questions through the site. For general, unrelated questions, feel free to use the forum. Here is a breakdown of what we will cover in this class: In the first lesson, you will learn the difference between passing values ByRef and ByVal to your subs and functions. We will build a simple increment subroutine that can accept a variable, increase it by one, and demonstrate how ByRef behaves. In lesson two, we will continue exploring ByRef and ByVal. I will introduce optional parameters, so you are not limited to incrementing by one; you could increment by, for example, seven or ten. I'll also show you how to use this subroutine with form controls, allowing you to target a field on a form as well as a variable. We will determine whether the argument is a form field or a number and act accordingly. After that, I will review public subroutines and functions, which you may remember from previous classes. In lesson three, we dig deeper into ByRef and ByVal by learning how to use ByRef to return multiple values from a function. Up until now, our functions have always returned just a single value, such as a Boolean or string. In this lesson, I will demonstrate how to have a function return more than one piece of information. For example, we will create a function that returns a status code and a validity check on a ticket—indicating if it is true or false. It will also return a status description, such as "the first character is not a T," based on whatever validation criteria we set. On top of this, the function will look up the customer's name and return that too. In the fourth lesson, we will look at how to identify the active form and control in your database at any given time. We will discuss how to determine what form and control the user is currently interacting with, without needing to pass those details as parameters to functions. I will show you how to access the previous control as well, so if there is a button you want to use to perform an action based on the user's last focus—such as multiplying a field value by ten—it will know exactly what the previous control was. Instead of passing the control name, we will work with control objects directly, learning how to use object variables for different control types. There is a lot to discover in this lesson. In lesson five, we will build support for product groups. Product groups are bundles or packages of products you want to offer together as a single item. Instead of adding each product individually to an order, you simply select the product group. In lesson six, we will automate the process—when you pick a product group and click a button, it will automatically add all the products in that group as separate line items on the order. We will set up product groups and make it easy to add them to orders. Continuing in lesson six, we will add a combo box to the order form so you can pick a product group easily. The selected group is added to the order using a recordset loop. On the invoice, you often do not want to show zero values for each included product, just the bundled package price. We will adjust the invoice view to hide zero line items, indent them under the main package, and make the group names bold. I will also include some conditional formatting so these packages display with a distinct color for better visibility. These lessons contain a lot of useful information and tools you can use to enhance your own database projects. 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 ListByRef vs ByVal when passing valuesCreating an increment subroutine with ByRef Using optional parameters in subroutines Applying ByRef and ByVal to form controls Detecting field vs numeric variables in subs Review of public subroutines and functions Returning multiple values from a function with ByRef Returning status codes and validation results Returning customer name from a function Determining the active form and active control Accessing the previous control programmatically Dimming controls as object variables Handling different control types Building product groups for order entry Selecting and adding product groups to orders Adding a combo box for product group selection Using a recordset loop to add group line items Formatting invoice to hide zero line items Indenting and bolding package products on invoice Applying conditional formatting to package items |
||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: access developer 25 lessons reservations PermaLink How To Use ByRef and ByVal, Active Form and Control, and Product Groups in Microsoft Access |
