Introduction

Welcome! Search Dates & Setup Tuition Form

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Developer Level 13. In this course we will build a custom form to search records between, before, or after two dates, and discuss how to set up tuition levels for private school students based on discounts, family size, and selected options. We will cover prerequisites for this class, highlight the structure of the Access Developer series, and explain where to find the databases used. Subsequent lessons will cover creating billing forms with multiple products and options, and we will walk through building a pricing wizard for selecting grade level, tuition, and adding orders.

Navigation

Prev: D13
Next: Lesson 01

Keywords

Access Developer, custom search form, search between dates, date range filter, VBA date search, form criteria, header search fields, billing form, tuition levels, private school billing, pricing wizard, product options, conditional pricing, order entry

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Developer Level 13, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we are going to make a custom form where you can search between two dates for different records. I know we have done some searching between dates before with just basic query stuff, but I am going to show you how to make a cool form with some fields up in the header. You can put in some dates and pick either "show me records between those two dates," or "show me before one of the dates," or "after one of the dates."

Next, we are going to do something I am calling tuition levels. Basically, it is going to be students enrolled in a private school. The tuition is based on different factors. For example, do they get a discount? How many children does the family have enrolled? You can apply this to anything you want to. It can be products with different options, like colors or sizes. I just happened to pick tuition levels because that was requested. But this particular example fits many different scenarios, as you will see as we get into class.

For prerequisite, this class obviously follows Access Developer Level 12. You should have completed that and the beginner, expert, and advanced series classes.

In case you are new to my classes, I have the beginner series, which covers the basics of building tables and forms and so on. The expert level classes go into more detail on that stuff, plus relationships. Advanced covers events and macros. The developer series, which is what we are in now, covers mostly VBA with more advanced topics. Each series is broken up into different levels. This is level 13.

The databases for this class can be found at this webpage: 599CD.com/databases. You will need your password to open or download the databases. I do strongly recommend that you build the databases that I build in class. You get the most out of it that way.

I am using Access 2019 Office 365, but everything I cover in today's class should work just fine with everything from 2007 up.

Let's take a moment and see what is covered in today's class.

In lesson one, we are going to build a form that we are going to put some criteria on top and allow us to search between dates, either between, before, or after certain dates.

In lesson two, we are going to learn how to make a form to do tuition for a private school. Just my example, but you will get a lot out of this lesson, trust me. We are going to be able to pick the grade level, the tuition level, whether it is discounted or not, the number of the child that is going to the school, because it changes per child, and then their tuition level. So we are basically going to learn how to enter billing. You can consider tuition level being a product. We are going to learn how to do billing with multiple products, with multiple different options for those products. This is something that a lot of you have asked me how to do.

In lesson three, we are continuing to work with tuition levels. I am going to show you how to build a pricing wizard, where we can pick the grade level, then the tuition levels will show up just for that grade level. Then we will pick that, and then the list of children will show up just for that one. Then it will show you the tuition price. We click Add Order, and then it adds it right to our order. There is a lot of cool stuff in this lesson.

Quiz

Q1. What is the main focus of lesson one in this class?
A. Building a form to search between dates using criteria fields in the header
B. Creating reports with custom layouts
C. Designing new table relationships
D. Configuring user permissions

Q2. What concept is introduced as "tuition levels" in lesson two?
A. A way to calculate staff salaries
B. A system for determining product shipping fees
C. Assigning pricing/options to students based on factors like discounts and number of children enrolled
D. Tracking daily attendance of students

Q3. Which of the following is NOT mentioned as a factor affecting tuition in the example?
A. Student's grade level
B. Discounts
C. Number of siblings enrolled
D. Participation in sports

Q4. What is the benefit of building the databases as instructed in the class?
A. Access to exclusive advanced topics
B. Receiving automatic course completion certificates
C. Gaining the most understanding and benefit from the lessons
D. Guaranteeing job placement services

Q5. How does the pricing wizard described in lesson three function?
A. It allows users to enter all students manually
B. It filters available tuition levels and children based on grade level selections
C. It generates random prices for tuition
D. It automatically enrolls new students

Q6. Which versions of Microsoft Access are compatible with the material presented in this class?
A. Only Access 2019
B. Access 2019 and above
C. Access 2007 and up, including Office 365
D. Only Office 365

Q7. In lesson two, what general skill is being taught besides managing tuition?
A. Creating complex reports
B. How to do billing with multiple products and options
C. Linking Access to an Excel spreadsheet
D. Managing user logins

Q8. What is recommended if you wish to obtain the databases for this class?
A. You should complete a short quiz
B. They are freely available online
C. Use the link provided and your password to access them
D. Contact support to request them

Q9. What should students have completed before taking Access Developer Level 13?
A. Only the beginner series
B. Developer Level 10 or higher
C. All beginner, expert, advanced, and Developer Level 12 classes
D. Only the advanced series

Q10. What kind of example other than tuition levels did the instructor say this lesson could be applied to?
A. Library catalog management
B. Products with different options like colors or sizes
C. Time tracking for employees
D. Budgeting household expenses

Answers: 1-A; 2-C; 3-D; 4-C; 5-B; 6-C; 7-B; 8-C; 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.

Summary

Today's video from Access Learning Zone is from Access Developer Level 13. I'm Richard Rost, your instructor for this course.

In this lesson, I'll show you how to create a custom form that allows you to search for records between two dates. While I've covered basic date search queries in previous classes, this time I want to demonstrate how to design a more advanced form. We'll include controls in the header where you can input a start and end date, and then choose whether you want to find records between those dates, before the first date, or after it.

Next, I'll introduce a new topic that I call "tuition levels." This focuses on managing tuition for students in a private school, where tuition depends on various factors such as family discounts and the number of children a family has enrolled. Although I'm using tuition as the example, this approach can be applied to any situation where products have options like different colors or sizes. The process we'll use here will fit many different scenarios beyond just school tuition.

For prerequisites, you should have completed Access Developer Level 12, as well as the beginner, expert, and advanced series of my courses. If you're just joining, my beginner series covers the basics like how to create tables and forms, while the expert classes delve deeper into these concepts and cover relationships. The advanced level introduces events and macros, and the developer courses focus on VBA and higher-level topics. Each is broken down into multiple levels, and this is level 13 in the developer series.

You can find the example databases for this class at 599CD.com/databases. You'll need your password to access them. I strongly encourage you to build these databases yourself along with me, as that's the best way to truly grasp the material.

For reference, I'm using Access 2019 with Office 365, but everything I'll be covering in this class will also work in versions going back to 2007.

Here's a quick overview of what's covered in today's class:

In lesson one, we'll create a form with date entry fields at the top, allowing you to search for records between two given dates, or to narrow the search to just before or after a particular date.

In lesson two, we'll set up a form for entering tuition for a private school. This is just my example, but it will be useful to anyone who needs to handle billing for products or services with multiple options. You'll learn how to select a grade level and tuition level, choose whether there should be a discount, and specify which child in the family is being enrolled, since pricing can vary based on the number of children a family has in the school. This lesson will teach you how to set up a versatile billing system with multiple products and options - something a lot of you have asked for.

In lesson three, we'll continue working with tuition levels and I'll show you how to create a pricing wizard. We'll set it up so that you pick a grade, see only the tuition options available for that grade, and then select the child you want. Once all selections are made, the system will display the appropriate tuition price. Pressing a button adds the selection right to the order. This lesson contains a lot of practical, powerful techniques.

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

Building a date range search form
Adding criteria fields to form headers
Searching records between two dates
Filtering records before or after a date
Setting up tuition levels for private school students
Applying discounts to tuition
Handling multiple children per family
Selecting grade level and tuition options
Calculating tuition based on child order
Implementing billing with multiple products and options
Creating a pricing wizard for tuition
Filtering tuition options by grade level
Displaying children based on selected tuition level
Adding calculated tuition price to orders

Article

Welcome to Access Developer Level 13. In this tutorial, we are going to take Microsoft Access one step further by building some powerful forms and features using VBA and advanced techniques. You will learn how to create a custom search form that allows users to find records based on date ranges, as well as how to implement flexible pricing options using a real-world example like private school tuition calculation.

Let us start with building a custom search form for dates. Most people know how to use simple queries in Access to search by date, but we are going to design a more user-friendly form. Imagine a form with a couple of textboxes or date pickers at the top where users can enter a start date and an end date. There will also be options to find records that are before a certain date or after a certain date.

The concept is to create unbound controls (fields that are not tied directly to data in your table) in the form's header section. For example, you might add two controls called txtStartDate and txtEndDate. Next to those, you can have radio buttons or a combo box to select whether you want to search between the two dates, before the start date, or after the end date. When the user clicks a Search or Apply Filter button, you will use VBA code to dynamically build the filter for your form or underlying query.

Here is a basic example of how you might do this in VBA. In the click event of your Search button, you could use code like this:

Dim strFilter As String

If Not IsNull(Me.txtStartDate) And Not IsNull(Me.txtEndDate) Then
If Me.cboSearchType = "Between" Then
strFilter = "OrderDate >= #" & Me.txtStartDate & "# AND OrderDate <= #" & Me.txtEndDate & "#"
End If
ElseIf Not IsNull(Me.txtStartDate) Then
If Me.cboSearchType = "After" Then
strFilter = "OrderDate >= #" & Me.txtStartDate & "#"
ElseIf Me.cboSearchType = "Before" Then
strFilter = "OrderDate <= #" & Me.txtStartDate & "#"
End If
End If

Me.Filter = strFilter
Me.FilterOn = True

This code builds a filter string based on the user's inputs. It assumes you have a combo box for the search type (Between, Before, After) and the relevant date fields. You can adapt this filter logic for any table or field you need.

Next, let us dive into a more complex scenario: managing tuition levels for students in a private school. Imagine you run billing for a school where the tuition amount depends on several factors. There may be discounts applied, the tuition rates may vary by grade level, and the price might change depending on how many children from a single family are enrolled. This setup resembles many business situations, as you can apply the same logic to products that have options like color or size.

To tackle this, start by building a form for entering orders, or in this context, tuition billing. You will want to set up tables for students, families, grade levels, tuition levels, and possibly a separate table to track billing or orders. Your tuition levels table might include fields for grade, standard tuition, discounted tuition, and which child number these prices apply to (for example, first child, second child, etc.).

The form you create will let users select the student, choose the grade, pick the tuition level, and specify which child this is for that family. Once you make these selections, the form should automatically calculate the correct tuition price. This is typically done by having the AfterUpdate events on your combo boxes or other controls trigger VBA code that looks up the right price.

Here is a simplified snippet of what that code might look like:

Dim dblTuition As Double

If Me.chkDiscount = True Then
dblTuition = DLookup("DiscountedTuition", "TuitionLevels", _
"GradeID=" & Me.cboGradeLevel & " AND ChildNumber=" & Me.txtChildNumber)
Else
dblTuition = DLookup("StandardTuition", "TuitionLevels", _
"GradeID=" & Me.cboGradeLevel & " AND ChildNumber=" & Me.txtChildNumber)
End If

Me.txtTuitionAmount = dblTuition

This code checks if the discount checkbox is checked. If it is, it retrieves the discounted tuition for the selected grade and child number. If not, it gets the standard tuition. The value is then displayed in a textbox.

Let us take this even further by building a small pricing wizard in your form. The idea is to make the data entry process easier and less error-prone. When you select a grade level, the form only shows you the tuition levels related to that grade. When you select the tuition level, you can choose the child (for example, first, second, third child), and then the form displays the correct amount. Once everything is set, you press a button like Add Order, and the record is saved to your Orders or Billing table.

To implement this, use child forms or cascading combo boxes. When the user selects a grade level in the first combo box, use the AfterUpdate event to requery the second combo box so it only shows relevant options:

Private Sub cboGradeLevel_AfterUpdate()
Me.cboTuitionLevel.RowSource = "SELECT TuitionLevelID, TuitionName FROM TuitionLevels WHERE GradeID=" & Me.cboGradeLevel
Me.cboTuitionLevel.Requery
End Sub

The same logic can apply to updating the available child numbers or other details. Once all selections are done, you can use code like this on your Add Order button:

Private Sub cmdAddOrder_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Orders")

rs.AddNew
rs!StudentID = Me.cboStudent
rs!GradeID = Me.cboGradeLevel
rs!TuitionLevelID = Me.cboTuitionLevel
rs!ChildNumber = Me.txtChildNumber
rs!TuitionAmount = Me.txtTuitionAmount
rs.Update

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

This will save the selected information to the Orders table, completing the transaction. The resulting system is very flexible and can be adapted to many business cases beyond just school tuition. You can substitute product options for grade levels and so on, following the same pattern.

With these techniques, you can build forms in Access that let users search for records between, before, or after selected dates, and you can implement complex product or service pricing using VBA to make your Access applications more user-friendly and powerful. Remember to always test your forms and validation thoroughly to ensure users get accurate results. If you build your forms step by step and understand how VBA interacts with your controls and tables, you will be able to create robust Access applications tailored to your needs.