Welcome

In this class, we begin with getting report criteria from an open form. We built a customer search form in previous classes. Today, we'll learn how to take the SQL from that form to use for the criteria of a report. Next, we'll create customer-specific pricing for products. This way if you have a particular customer who gets special pricing for specific units, you can set that up.

Next we'll create an entire Time & Billing section, so you can keep track of the billable hours you spend on each customer. We'll build a timer that you can start and stop as you are working for them. Watch the billable hours click up. At the end of the month, generate invoices for all of your clients with one press of a button, and you can batch print or email them out.

Resources

Topics Covered

In Lesson 1, we will learn how to create a report and have it get its data from the criteria specified on our Customer Search Form. We will learn about the Report Open event (On Open) and how to set the report's RecordSource property based on the Customer List Box's RowSource property. We'll also learn about the On No Data event.

In Lesson 2, we will begin working with customer-specific pricing. If you have a certain customer who you want to give specific pricing to for specific products, this will be very helpful to you. We will construct a table to hold this data. We will use DLookup and IIF to determine if custom pricing exists for this customer.

In Lesson 3, we are continuing with customer pricing. We will add this new pricing to the product select combo box on the order form. So when your user goes to add a product to a customer's order, they get the correct updated pricing.

In Lesson 4, we will finish up with customer pricing. If the user changes a price on a customer's order, the database will ask them if they want to save that as a custom price for that customer. If so, it will add it. If it exists already, it will be edited. We'll use a Recordset with the BOF and EOF markers to determine if the customer specific pricing exists. We will also build a subform on the customer form to make it easy to edit their custom pricing.

In Lesson 5, we will begin working with Time & Billing. We will create the table, query, and form to track our billable hours for each client. We'll calculate the total billable time and the total unbilled so far. We'll make a button on the customer form so you can see each.

In Lesson 6, we will build a timer with start and stop buttons. This will allow you to track your billing for each customer to the second if you want to, however we'll generally round up in 15 minute increments. You can change the billable hours manually if you want. We'll also learn how to lock all of the controls on the form with a loop so they can't be edited while the clock is running, if that's what you want. Some people do.

In Lesson 7, we will use recordsets to generate an invoice for a customer based on the amount of billable work we've done. One invoice will be created with each item from the Time & Billing form as a line item on the invoice.

In Lesson 8, we will see how to generate invoices for ALL of the customers in the database who have unbilled hours. This way you can create all of your month-end invoices with one click.

In Lesson 9, we will learn how to batch-print, or batch-email our invoices out. If the customer has an email address, they will get their invoice by email. If not, you can print it out and mail it 90s style. You will learn how to send email via any SMTP server, including Gmail, using CDO. You'll learn how to open a form hidden.

Enroll Today

Enroll now so that you can watch these lessons, learn with us, post questions, and more.

Questions?

Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.

Comments for Access Developer 28

Age	Subject	From
2 years	specific sold price to certain customers	Liem Khen
2 years	without generating	John Gemayel

Subscribe to Access Developer 28
Get notifications when this page is updated

Intro

In this lesson, you will learn how to use Microsoft Access to get report data based on form criteria, set up and manage customer-specific pricing, build a time and billing system to track work for clients, and batch print or email invoices. Topics include creating reports from form filters, assigning special prices for individual customers, tracking billable hours with start/stop timers, generating invoices from work records, and automating the process of sending or printing invoices for customers based on their preferred delivery method. This is Access Developer Level 28.

Transcript

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

Developer 28 is going to cover getting report data from form criteria, customer-specific pricing, time and billing, and batch printing or emailing of invoices.

Report data from form criteria: We are going to take the customer search form that we made before, where we can put in some different criteria like state or email address, and use that to populate a report based on those selections.

Customer-specific pricing is where we can take a particular customer and say for this product they pay this price, for that product they pay that price, and so on. We can do that for each customer.

Time and billing will allow us to track the time that we spend working for a client. We will have a little stopwatch or clock where we can click on start and stop. You will see the time go up. From there, we will see the total work unbilled. We will be able to generate invoices for all of our customers where the actual work items become items in the order.

Then we will batch print or email all of the invoices out - email if we have their email address and printing them on paper, 1990 style, if we do not.

This class covers Access Developer 27. I strongly recommend you take this and all the previous classes before taking this class. You should definitely know all the material in the beginner, expert, advanced levels, and developer up to level 1. Specifically, I would recommend taking up to developer level 16, when we start covering recordsets. We will use a lot of recordsets in this class. If not, make sure at least you watch my Access VBA Introduction class.

I will be using Access 365, which is roughly equivalent to Access 2016 or 2019. The material in this class should work all the way back to about Access 2007.

As always, if you have questions pertaining to the material covered in today's class, post them down below at the bottom of the page. If you have other questions about Access that do not pertain to today's class, feel free to post them in the form.

Let's take a look in more detail at what is covered in today's class.

In lesson one, we are going to learn how to get data in a report from a form. In Developer 26, we built that really cool customer search form where we can pick some criteria and then have the list of customers filtered by that criteria. Well, what if you want to print that stuff out? In this lesson, I will show you how to make a report that gets the same set of data from the row source of that list box by just clicking one button.

In lesson two, we are going to start working with customer-specific pricing. We are going to set it up so that if you have a customer that you want to have special pricing for certain products, we will set up a table for that. Instead of a blanket discount across the board, if you have this product, that product, this product for this particular customer, then you can set up a customer price. We will start doing it in lesson two.

In lesson three, we are continuing with the customer pricing and we are going to add the customer pricing to the product selection combo box on the order form.

In lesson four, we are finishing up customer pricing. If the user changes the price on an order, it will ask them if they want to make this custom pricing permanent and put it in the table. If it exists, we will update it. If it does not, we will add it new with a recordset. We will check for BOF or EOF to see if that record exists. Then we will make a subform on the customer form so we can edit the custom pricing for each customer.

In lesson five, we are going to begin working with time and billing. We already have invoicing built, so now we are going to build a system where we can track the time that we spend working for a client. We will create a work table and form. We will track what was done, how many hours, and what billable rate. Then, we will be able to see the total amount of time and the total amount of unbilled time. We will make a checkbox so we can indicate when something has been invoiced or not. Then later, we will go through and click a button and send all our invoices out.

In lesson six, we are continuing with time and billing. We are going to add start and stop timer buttons to the work form. You will see a little clock counting up for each second that you are billing this customer for. It will then update the billable hours for that record and I will show you how to round it up so that you bill in 15 minute increments. I will also show you how to lock all of the controls on the form so that they cannot be edited while the clock is running. That is sometimes important.

In lesson seven, Time and Billing Part Three, we are going to create a button to take all of our time and billing details for the customer and generate an invoice. We will do it with a couple of different recordsets. We will create a recordset to create an order entry. We will then loop through all of the work table items and create detail items for them. Then we will update the customer's address and open up the order form.

In lesson eight, we are going to bill everybody who has unbilled work. We are going to make an aggregate query so we can see all of the customers and the total amount that they have to be billed for. We will put that in a form with a list box. You can double-click to open any particular customer or click the Invoice All button. Then, we will take the code that we generated in the last lesson, loop through all of these records, and automatically generate all of those invoices at once.

In lesson nine, we are going to learn how to print and email all of the unpaid invoices in our system. We just added a bunch of invoices based on our time and billing. Well, I do not have to open them all up individually and send them. This lesson will show you how to print all the ones. Well, first we will learn how to email all of the invoices out that we have email addresses for that customer. Then, I will show you how to print first. We will have to print first. We will print them all out. Then, we will learn how to email. Then, I will show you how to skip the people with email addresses when you print. So, you can email everybody weekly: here is your invoice, here is your invoice, here is your invoice. Then, once a month or whenever you decide, you can print out invoices and mail them for people who you do not have email addresses for.

Here we go. Lesson nine.

Quiz

Q1. What is the main purpose of using report data from form criteria in Access Developer 28?
A. To manually enter customer details into every report
B. To filter the data shown on a report based on user selections in a form
C. To print all records without any filtering
D. To export forms as reports

Q2. How does customer-specific pricing work as covered in this class?
A. Sets a single discount rate for all customers
B. Allows each customer to have a specific price for each product
C. Prevents pricing changes on the order form
D. Automatically applies the lowest price possible

Q3. What feature is added to the product selection combo box on the order form in lesson three?
A. Permission settings for sales reps
B. Automatic restocking notifications
C. Display of customer-specific pricing for products
D. Refund processing

Q4. In managing custom pricing, what happens if a user changes the price on an order?
A. The price change is ignored
B. The system automatically reverts to the original price
C. The user is asked if the new price should be made permanent for the customer
D. Access closes the form

Q5. Why are BOF and EOF checked when updating or adding customer pricing records?
A. To format numbers as currency
B. To ensure a record exists before updating or add it new if it does not
C. To export records to Excel
D. To encrypt customer data

Q6. What is the main goal of the time and billing features introduced?
A. To generate newsletters for all customers
B. To track work done for clients and bill them based on actual time spent
C. To automatically close orders after 30 days
D. To create coupons for frequent buyers

Q7. What additional elements are added to the time tracking form in lesson six?
A. Password protection
B. Start and stop timer buttons with a visible clock
C. Invoice printing preview
D. Social media integration

Q8. How are billable hours handled in the billing system?
A. Only full hour increments are allowed
B. Hours are rounded up to the nearest 15 minutes
C. Minutes are ignored
D. Fractions of hours are always rounded down

Q9. In lesson seven, how are time and billing entries turned into invoices?
A. By creating a recordset for order entry and looping through work items to add details
B. By emailing each billable item directly
C. By printing each record manually
D. By manually typing each invoice

Q10. What does the batch invoice creation process allow you to do?
A. Print each invoice individually for each client
B. Automatically generate invoices for all customers with unbilled work at once
C. Send reminders for overdue invoices
D. Backup the database

Q11. What is the function of the aggregate query in lesson eight?
A. To add up all customer contacts for a mailing list
B. To combine order totals for all customers with unbilled work
C. To merge multiple databases
D. To import new products

Q12. When batch processing invoices, how does the system distinguish between printing and emailing?
A. It prints all invoices and ignores email addresses
B. It sends all invoices by email, regardless of customer preferences
C. It emails invoices to customers with email addresses and prints for those without
D. It exports all invoices to PDF only

Q13. Why is it suggested to take Developer Level 16 (or earlier classes) before this one?
A. They teach how to use PowerPoint with Access
B. They provide essential knowledge of recordsets and prior concepts required for this course
C. They are optional and unrelated
D. They cover only advanced report formatting

Q14. Which versions of Microsoft Access will the material in this class work with?
A. Only Access 365
B. Only Access 2016 and above
C. All Access versions from 2007 and later
D. Only Access 2019 and 2021

Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-B; 8-B; 9-A; 10-B; 11-B; 12-C; 13-B; 14-C

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 focuses on Microsoft Access Developer Level 28. In this class, I will cover several advanced topics, including retrieving report data based on form criteria, implementing customer-specific pricing, setting up time and billing systems, and managing batch printing or emailing of invoices.

To start, I will show you how to generate a report using the criteria selected in a customer search form. Previously, we built a customer search form that let us filter customers by fields like state or email address. Now, we will use that same form to produce a report containing only the customers that match the selected criteria. Essentially, you will be able to transfer your search results directly into a printable report with just one action.

Next, we will tackle customer-specific pricing. This feature allows you to assign unique prices for different products to individual customers, instead of applying a standard discount or flat pricing structure. I will demonstrate how to create a table to store these custom prices, making it easy to specify that, for example, one customer pays a different price for a certain product compared to everyone else.

Continuing with customer-specific pricing, I will show you how to integrate these prices directly into your order form. When you select a product for a customer, the form will automatically display the correct custom price, if there is one on record.

We will then expand this to give users more control. If someone changes the price on an order manually, they will be prompted to decide if they want to save that new price as the permanent custom rate for that customer and product combination. We will handle situations where a new custom price record needs to be added or an existing one updated. To support this, I will show you how to check for existing records efficiently and add a subform to the customer form, making it easier to view and edit all custom prices for each customer.

After finishing customer-specific pricing, we will move into time and billing. Since invoicing is already set up, the next step is to let you track the time you spend working for a client. We will design a table and form for logging work details such as the tasks performed, hours spent, and billable rates. I will teach you how to display total hours and the sum of unbilled work, and how to use a checkbox to indicate which tasks have been invoiced. Later, you will be able to quickly generate invoices based on this work.

Building on that, I will add start and stop buttons to your work form, so you can run a timer while you are working for a client. The form will update the billable hours for each session, and I will explain how to round times to the nearest 15-minute increment for billing purposes. It is also important that form fields cannot be edited while the timer is running, so I will show you how to temporarily lock all controls during an active timing session.

In the next lesson, we will automate the process of generating invoices from time and billing entries. With a single button, you will be able to create a new invoice for a customer, using all linked time entries as line items. This involves working with multiple recordsets to transfer work details into a new order and ensure that the customer's information is correct before opening the invoice for review.

Then, I will show you how to identify all customers with outstanding, unbilled work. We will use an aggregate query to display each customer's total billable amount in a list. From there, you can open any customer's details or choose to invoice everyone at once, using code to generate all necessary invoices automatically.

Finally, I will guide you through batch printing and emailing of unpaid invoices. You will learn how to send email invoices to all customers for whom you have email addresses and print paper invoices for those who do not. This process allows you to email your invoices on a regular schedule, such as weekly, and print and mail paper copies, for example, once a month or as needed.

This class builds on concepts introduced in Developer 27 and previous levels. I recommend completing all prior beginner, expert, advanced, and developer courses up through at least Developer Level 16, which introduces recordsets. Familiarity with Access VBA, especially as covered in my VBA Introduction class, will be very useful, as we will make extensive use of recordsets in these lessons.

I will be using Access 365 for these demonstrations, but the topics and examples should work well with Access 2016, Access 2019, and even back to Access 2007.

If you have any questions about the material covered in this class, please post them in the comments section on my website. For other Access topics not related to this course, there is a dedicated form on the site.

A complete video tutorial with step-by-step instructions for everything discussed here is available on my website at the link below. Live long and prosper, my friends.

Topic List

Getting report data from form criteria
Creating a report based on form list box selections
Setting up tables for customer-specific pricing
Applying custom pricing per product for each customer
Adding customer pricing to the product combo box on orders
Prompting to save changed pricing as permanent custom pricing
Updating or adding custom pricing using VBA recordsets
Creating a subform to edit customer-specific pricing
Building a time and billing work table and form
Logging billable hours and work details for clients
Displaying total and unbilled work hours
Using checkboxes to mark billed/unbilled work
Adding start and stop timer buttons for tracking work time
Rounding billable hours for invoice increments
Locking form controls during active billing
Generating invoices from time and billing records
Looping through work records to create invoice details
Updating customer address and displaying the order form
Creating aggregate queries for unbilled work per customer
Batch invoicing all customers with unbilled work
Printing all unpaid invoices
Emailing invoices to customers with email addresses
Skipping printed invoices for customers already emailed