|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Introduction Welcome! Order Buttons, Block Child Rows, Captions Welcome to Microsoft Access Advanced Level 2. In this course we will focus on building and using macros in Microsoft Access, continuing from Advanced Level 1. We will discuss how to add a button for new orders, handle adding child records before parent records using events like Before Insert, and use commands such as GoToControl and SetValue. We will also cover creating a toggle button that displays either Quotation or Invoice based on form values, with captions that update dynamically using macros as you interact with your database. NavigationKeywordsAccess Advanced, macros, macro construction, event handling, button to add new order, Before Insert event, GoToControl, SetValue, GoToRecord, RefreshRecord, toggle button, Quotation or Invoice, IsQuotation field, dynamic button caption, Access 2016
IntroWelcome to Microsoft Access Advanced Level 2. In this course we will focus on building and using macros in Microsoft Access, continuing from Advanced Level 1. We will discuss how to add a button for new orders, handle adding child records before parent records using events like Before Insert, and use commands such as GoToControl and SetValue. We will also cover creating a toggle button that displays either Quotation or Invoice based on form values, with captions that update dynamically using macros as you interact with your database.TranscriptWelcome to Microsoft Access Advanced Level 2, brought to you by AccessLearningZone.com. I am your instructor Richard Rost.Welcome to the Advanced Series of Lessons. This is Advanced Level 2, which gives you more information on macros that we started covering in, guess what, Advanced Level 1. We will be focusing primarily on macro construction for the next couple of lessons, including events and event handling, and linking certain macros and different things while running in your database. I strongly recommend that you take Advanced Level 1 and, if you are not familiar with Access, my beginner and expert classes. There are a total of 9 beginner lessons and 32 expert lessons. There are, so far, two advanced lessons; Advanced 1 covered macro basics. This is Advanced 2. We are going to go into more details of macros, and pretty soon, when these are done, we are going to go into developer lessons, which will cover VBA code and programming. This lesson was recorded using Access 2016. Everything should work with 2007 and 2013 as well. If you find something that does not work, please let me know. The sample database for this class, if you want to download and use mine, if you have not been building it yourself along with my lessons, you can download it here at xslinnesome.com/databases. You will need the password that was provided to you for the course when you purchased this lesson. Let's talk about what we are going to cover in today's class. In lesson 1, we are going to add a button to add a new order. We can already click on a button to view all of the orders for the customer, but this button will allow us to open up the list of orders and then go to a blank new record. In lesson 2, we will deal with the problem of someone trying to add details or line items before they add order information. So they are trying to add child records before there are parent records. We will learn about the Before Insert event and some different commands like GoToControl, SetValue, GoToRecord, RefreshRecord, and so on. In lesson 3, we are going to make a toggle button that will say either Quotation or Invoice based on the value of the IsQuotation field. We will have that caption change dynamically using a macro, either when the button is clicked on or when we move from record to record or open a new form. QuizQ1. What is the primary focus of Microsoft Access Advanced Level 2?A. Advanced table design B. Macro construction and events C. Creating relationships between tables D. Advanced reports formatting Q2. Which of the following topics was already covered in Advanced Level 1? A. VBA programming B. Macro basics C. Query optimization D. SQL pass-through queries Q3. What is recommended before taking Advanced Level 2? A. Taking Advanced Level 1 and the beginner and expert classes B. Only basic Access knowledge C. Just downloading the example database D. No prerequisites are necessary Q4. How many beginner and expert lessons are available before the advanced lessons? A. 3 beginner, 12 expert B. 5 beginner, 20 expert C. 9 beginner, 32 expert D. 2 beginner, 5 expert Q5. What will be handled in Lesson 1 of Advanced Level 2? A. Adding a button to delete orders B. Adding a button to add a new order and go to a blank new record C. Creating a report for orders D. Exporting orders to Excel Q6. In Lesson 2, what issue does the lesson address? A. Deleting line items without deleting an order B. Adding child (detail) records before parent (order) records exist C. Printing invoices automatically D. Sorting records automatically Q7. Which macro commands are mentioned as being covered in Lesson 2? A. SetTabOrder, HideColumn, BackupDatabase B. GoToControl, SetValue, GoToRecord, RefreshRecord C. ImportFile, ExportFile, MergeData D. CreateTable, DeleteTable, AppendRecord Q8. What is the purpose of the toggle button discussed in Lesson 3? A. To sort orders by date B. To display either Quotation or Invoice based on a field value C. To delete multiple records at once D. To change the color scheme of the form Q9. What version of Access was used to record this lesson? A. Access 2003 B. Access 2021 C. Access 2016 D. Access 2010 Q10. What are students required to have to download the sample database for the course? A. An administrator account B. The course password provided upon purchase C. A subscription to Microsoft 365 D. An email confirmation Answers: 1-B; 2-B; 3-A; 4-C; 5-B; 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 is part of my Advanced Level 2 Microsoft Access course, where we continue to expand on the use of macros. I am Richard Rost, your instructor, and in this series, we're taking a deeper look at how to construct macros, building on concepts from Advanced Level 1. Over the next few lessons, I will focus on macro construction, event handling, and connecting various macros within your Access databases.If you think you might not have all the prerequisites you need, I strongly suggest completing Advanced Level 1 and, for those new to Access, working through my beginner and expert classes as well. In total, there are 9 beginner lessons and 32 expert lessons available, which provide a solid foundation. Advanced Level 1 introduced the basics of macros, while Advanced Level 2 will go further into their use and applications. Once we finish this advanced series, we will move on to developer-level topics, including VBA code and more advanced database programming. This lesson was recorded with Access 2016, and all of the material should apply to Access 2007 and 2013 as well. If you run into anything that does not work as expected, please let me know. For those who want to use my sample database, you can find the download at xslinnesome.com/databases. You will need the password that came with your course purchase. If you have been building the database alongside me, you can keep using your copy. Now, let me outline what you will be learning in today's class. In the first lesson, we are going to add a button to the database that allows you to create a new order. While we already have a button to view all orders for a customer, this new feature will let you pull up the list of orders and move directly to a blank new order record. In the second lesson, I will address a common issue: users trying to add order details or line items before entering basic order information. This means they are attempting to add child records without a parent record in place, which is not allowed. We will solve this by using the Before Insert event and macro commands such as GoToControl, SetValue, GoToRecord, RefreshRecord, and others. In lesson three, we will set up a toggle button that automatically updates its caption to show either 'Quotation' or 'Invoice' depending on the value of the IsQuotation field. Using a macro, we will make sure the caption changes not only when the button is clicked, but also whenever the user moves to a different record or opens a new form. 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 ListAdding a button to add a new orderOpening order list and going to a new blank record Handling child records before parent records Using the Before Insert event Using GoToControl in macros Using SetValue in macros Using GoToRecord in macros Using RefreshRecord in macros Creating a toggle button for Quotation or Invoice Changing toggle button caption dynamically with a macro Updating button caption on click, record change, or form open ArticleWelcome to this advanced Microsoft Access tutorial where we explore deeper macro techniques to enhance your database applications. If you are not already comfortable with the basics of Microsoft Access, or if you have not yet learned about macros, you will benefit greatly from reviewing the beginner and expert level lessons first. Once you are ready, this series will take you further into automating your database, specifically focusing on working with macros and event handling.This guide uses Microsoft Access 2016 as its reference, but the steps and concepts will also work with Access 2007 and 2013. If you encounter any command or feature that behaves differently, it is likely due to version differences, but most of what we discuss should translate smoothly across these editions. In this tutorial, you will learn how to streamline the process of working with orders and related data in your database. The goal is to make forms more user-friendly and to ensure that data is entered in the correct order. Let us begin by addressing a common workflow. Suppose you have a form listing customers, and you want to add the ability to enter a new order for a selected customer. While it is likely you already have a form that displays all orders for a customer, we can take this further by adding a button that, when clicked, opens the order form and jumps straight to a blank new order record ready for input. This is especially useful because it eliminates the need to scroll through existing orders just to add a new one. To do this, you can use a macro attached to a command button on your customers form. In the macro, use the OpenForm action to open your orders form, and include arguments to filter the form or to move directly to a new record. For example, you might use the following actions: OpenForm Form Name: Orders View: Form Where Condition: [CustomerID]=[Forms]![Customers]![CustomerID] Data Mode: Add This setup ensures that when the button is pressed, the Orders form opens showing only the orders for the current customer, and positions itself ready to create a new order. The macro can be designed using the macro builder in Access, with the above details filled in for each step. Another common issue that arises is that users might attempt to enter order details or line items before entering or saving the order header information. In a relational database, you must create the parent record (the order) before you can add child records (the line items). If someone attempts to add a line item before the main order exists, it can cause errors or confusion. To prevent this, you can use the Before Insert event on your form. This event triggers before a new record is actually saved. In your macro, you can use actions like GoToControl to move the user's focus back to the main order fields, SetValue to provide default values or clear fields as needed, GoToRecord to control the navigation, and RefreshRecord to update the displayed data. For example, in the Order Details subform, you might create a macro for its Before Insert event that checks whether the parent order exists. If it does not, the macro could warn the user and set the focus back to the Orders form for the necessary entry. You can achieve this with the following steps: 1. In Design View, select the Before Insert event for the subform. 2. Use a macro or code like: If IsNull([Forms]![Orders]![OrderID]) Then MsgBox "Please enter the order information before adding details." GoToControl [Form]![Orders]![OrderDate] End If This forces the user to complete the order header before adding any line items. In another scenario, you might want to improve the visual cues on your form, such as toggling a button's caption between "Quotation" and "Invoice" depending on a value stored in the form's data. Let us say you have a field called IsQuotation on your orders form. You want your button to dynamically update its caption based on the value in this field, not just when clicked but also as you scroll between records or when a new record is created. You can do this using a macro triggered by multiple events, such as the button's OnClick event and also on the form's OnCurrent and OnLoad events. In your macro, use the SetProperty action to change the button's Caption property according to the IsQuotation field. Here is an example of how you could set this up: If [IsQuotation] = True Then SetProperty Control Name: MyToggleButton Property: Caption Value: Quotation Else SetProperty Control Name: MyToggleButton Property: Caption Value: Invoice End If Make sure this macro runs when the button is clicked, when the form loads, and every time the current record changes. This ensures your interface is always accurate and responsive to your data. By leveraging these macro techniques, you make your Microsoft Access applications not only more functional but also much more user-friendly. These methods help guide users to enter data in the right order and clearly indicate current record status, all without needing VBA programming at this stage. Later, you may choose to move into developer features and start working with VBA code for even greater flexibility and power in your Access database solutions. Remember to keep your sample databases and always test each macro on copies before adding them to your production files. As you get more confident, you will find these tools invaluable for building professional, robust applications in Microsoft Access. |
||
|
| |||
| Keywords: Access Advanced, macros, macro construction, event handling, button to add new order, Before Insert event, GoToControl, SetValue, GoToRecord, RefreshRecord, toggle button, Quotation or Invoice, IsQuotation field, dynamic button caption, Access 2016 PermaLink How To Add New Order Buttons, Prevent Child Records, and Create Toggle Captions in Microsoft Access |
