Introduction

Welcome! Report Data & Custom Pricing Setup

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

Welcome to Microsoft Access Developer Level 28. In this course we will cover getting report data from form criteria, setting up customer-specific pricing, creating a time and billing system with start and stop timers, and batch printing or emailing invoices. We will discuss how to pull filtered data from a search form into a report, create and maintain custom pricing for individual customers, track billable hours and manage unbilled work, and generate or send invoices in bulk. This class builds on previous levels, and familiarity with concepts like recordsets is recommended.

Navigation

Prev: D28
Next: Lesson 01

Keywords

Access Developer, report data from form criteria, customer-specific pricing, time and billing, batch invoice printing, batch invoice emailing, recordsets, customer search form, invoicing system, work tracking, custom product pricing, aggregate query, time

Subscribe to Introduction
Get notifications when this page is updated

Intro

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 here, a little clock. 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 stuff 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 19. 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 forum.

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.

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, this 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. Do you want to put it in the table? If it exists, we will update it. If it is 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. Later on, 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. We do not have to open them all up individually and send them. This lesson will show you how to print all the ones.

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 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. 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 to people who you do not have email addresses for.

Lesson nine.

Quiz

Q1. What is the main purpose of retrieving report data from form criteria in Access?
A. To print customer lists filtered by user-selected criteria in a form
B. To update form controls dynamically
C. To import data from Excel
D. To export data to a web application

Q2. What feature allows you to set unique prices for each customer/product combination?
A. Customer-specific pricing
B. Bundle discounts
C. Product tagging
D. Bulk pricing

Q3. In the time and billing system, what is the role of the start and stop buttons?
A. To record the time spent working for a client
B. To switch users in the database
C. To add new products to an order
D. To reset all customer data

Q4. What is the benefit of rounding billable time up to 15 minute increments?
A. To standardize billing periods and potentially increase revenue
B. To make timesheet entry faster
C. To reduce database size
D. To avoid duplicate invoices

Q5. What action does the batch print or email feature perform?
A. Sends all invoices to customers by email or prints them if no email is available
B. Deletes all unpaid invoices
C. Exports invoices to Excel
D. Archives old invoices automatically

Q6. Why is it important to lock form controls while the timer is running during time tracking?
A. To prevent accidental edits to time and billing records while tracking
B. To restrict access for certain users
C. To accelerate report generation
D. To focus on sales orders

Q7. What function does the order form's product selection combo box gain after adding customer-specific pricing?
A. It displays customized prices for the selected customer and product
B. It limits product selection to best-sellers
C. It only shows out-of-stock items
D. It calculates shipping costs automatically

Q8. How does the system handle a custom price change on an order?
A. It asks the user if the price change should be made permanent and updates or creates a record accordingly
B. It sends an alert to the supervisor
C. It rejects the new price by default
D. It locks the price field until approved

Q9. What process is used to automatically generate invoices for all customers with unbilled time?
A. An aggregate query lists customers; clicking "invoice all" loops through and creates invoices via recordsets
B. Each invoice must be created manually
C. Only the highest-value customers are invoiced automatically
D. Invoices are generated when the database is closed

Q10. What is the advantage of separating customers with and without email addresses when sending invoices?
A. Emailing invoices to those with email addresses, and printing for those without, increases efficiency
B. It reduces database size
C. It improves security settings
D. It archives inactive customers

Q11. Up to which Developer Level is it strongly recommended to complete before taking Developer 28?
A. Up to Developer Level 16
B. Only through Beginner Level
C. Developer Level 2 only
D. Expert Level only

Q12. What is the main purpose of the work table and form in the time and billing module?
A. To record the tasks performed, hours worked, billable rate, and invoice status
B. To store customer payment methods
C. To generate weekly attendance sheets
D. To track employee vacations

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-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.

Summary

Today's video from Access Learning Zone covers the material in Microsoft Access Developer Level 28. I am your instructor, Richard Rost.

In this class, I'll be teaching you how to pull report data based on form criteria, set up customer-specific pricing, implement a time and billing system, and process batch printing or emailing of invoices.

First, we will look at how to produce a report from form-based criteria. Earlier, we built a customer search form that allowed us to filter customers based on criteria like state or email address. Now, I will show you how to use those selected values to generate a report that reflects exactly what you see in the search results, with the click of a button.

Next, we will work on customer-specific pricing. This involves creating a structure where you can assign special prices for particular products for individual customers. Instead of having a blanket discount, you can set specific prices for each product for each customer as needed. We will put together a table to handle this, and then we will extend it further.

Once the basic pricing is in place, we will enhance the order form so that when you select products in the order details, it pulls in any special pricing set for that customer. If the user changes the price on an order, the system will prompt them to decide if they want to save that new price as the customer's default for that product. If a record already exists, we will update it; if it does not, we will create a new record using a recordset. You will also learn to check for the existence of records in the table by checking for BOF or EOF. We will add a subform to the customer form for editing these custom prices directly.

Following that, we will move on to creating a time and billing system. Even though we have invoicing set up already, we will develop features to record hours spent working for each client, along with descriptions of the work and billable rates. You will be able to see both the total hours worked and the total amount remaining to be billed. There will be a checkbox to indicate which work entries have been invoiced, and eventually you will be able to generate invoices for all of your unpaid billable hours at once.

To go deeper into time and billing, we will add start and stop buttons functioning like a stopwatch on the work entry form, so you can track your time automatically. The hours will be updated in real-time, and I'll show you how to round up billable time to the nearest 15 minutes. Controls on the form will be locked while the timer is running to prevent unwanted edits.

After tracking time, we will cover how to create invoices from these work entries. Using recordsets, we will create new orders and fill them with billing items based on your recorded work. The customer's address will be updated, and the corresponding order form will be opened when done.

Batch processing is another key part of this class. We will build an aggregate query to show every customer with unpaid work and how much is due. These results will be displayed in a form with a list box, giving you the option to double-click into customer records or to invoice all customers in one action. We will reuse our code to create all these invoices automatically.

Finally, you will learn to print or email all outstanding invoices. For customers with email addresses, we will send invoices by email. For others, we will print them out so you can mail them. I'll also show you how to skip clients when printing if they have already received their invoices by email. This way, you can choose to email invoices on a weekly basis and print the rest as needed.

This course builds on concepts from Access Developer 27. I strongly recommend completing all the previous levels, and especially making sure you are comfortable with recordsets as introduced in Developer Level 16. A solid understanding of the beginner, expert, advanced, and earlier developer series is important for getting the most out of this class. If you are not up to speed, at least watch my Access VBA Introduction.

I am using Access 365, but all the material here is compatible with Access 2016, 2019, and should work in versions back to Access 2007.

If you have questions relating to the topics in this class, you are welcome to ask them on the course page. For unrelated Access questions, feel free to use the forum.

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

Getting report data from form criteria
Customer-specific pricing setup
Adding customer pricing to order form
Making custom pricing permanent
Editing custom pricing per customer
Time and billing system setup
Tracking work time and billable hours
Start and stop timer buttons for work
Rounding billable hours to 15 minutes
Locking controls during active timer
Generating invoices from work items
Aggregate query for unbilled work
Batch invoicing for multiple customers
Printing all unpaid invoices
Emailing invoices to customers
Skipping emailed invoices when printing

Article

Welcome to this Microsoft Access Developer Level 28 tutorial. In this lesson, I will show you a series of advanced database development techniques focusing on getting report data from form criteria, managing customer-specific pricing, tracking time and billing, and automating the process of batch printing or emailing invoices. Everything I cover here works in Access 365 and should also work for versions going back to Access 2007.

First, let's explore how to use criteria from a form to generate a custom report. Imagine you have a customer search form where you can filter your customer list based on fields like state or email address. Once you have the filtered results, you might want to print out a report that matches exactly what you see on the form. To achieve this, you can use the Row Source property of your list box, which contains the filtered SQL statement. By creating a report that uses this SQL as its record source, and linking it to a command button on your form, you can ensure your report always reflects the currently selected criteria.

For example, suppose your customer list box uses a query like this:

SELECT * FROM Customers WHERE State = 'NY'

When you press your "Print Report" button, you can assign this SQL statement to the report's RecordSource and then open the report. Here is an example of how you might do this in VBA:

Private Sub btnPrintReport_Click()
DoCmd.OpenReport "CustomerReport", acViewPreview, , , , Me.lstCustomer.RowSource
End Sub

Within the report's Open event, you would then assign the SQL like this:

Private Sub Report_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
End If
End Sub

Next, let us discuss customer-specific pricing. Normally, if you offer discounts, you might give all customers a blanket percentage off or change the price globally. However, sometimes you want individual customers to have specific prices for certain products. To do this, you can build a new table linking customers to specific product prices. For example, create a CustomerPricing table with fields like CustomerID, ProductID, and SpecialPrice. When entering an order, your product combo box can look up from this table: if a customer has a special price for that product, it uses that instead of the default.

When adding products to orders, check the CustomerPricing table using a DLookup function in VBA to see if a custom price exists:

Dim varPrice As Variant
varPrice = DLookup("SpecialPrice", "CustomerPricing", "CustomerID=" & Me.CustomerID & " AND ProductID=" & Me.ProductID)
If Not IsNull(varPrice) Then
Me.Price = varPrice
Else
Me.Price = DLookup("UnitPrice", "Products", "ProductID=" & Me.ProductID)
End If

You can take this further by allowing users to update custom prices directly from the order entry screen. When a user changes the price on an order, you can prompt them to see if they want to make this a permanent customer price, updating or inserting into the CustomerPricing table. Using VBA and recordsets, check if a record exists using EOF and BOF, then update or insert as needed. You can add a subform to the customer form to manage and edit all custom prices.

Time and billing management is our next topic. Suppose your business bills for time worked in addition to products or services offered. You can build a system to track time spent working for a client. Start by creating a table for work items, including fields for CustomerID, Description, HoursWorked, Rate, and a Billable flag. On a form based on this table, add Start and Stop buttons to track your actual working time. Use a timer control to count seconds and update the hours worked.

For example, to handle the timer logic:

Private Sub btnStart_Click()
Me.TimerInterval = 1000
Me.txtStartTime = Now()
End Sub

Private Sub Form_Timer()
Me.txtElapsed = DateDiff("s", Me.txtStartTime, Now())
Me.txtHoursWorked = Me.txtElapsed / 3600
End Sub

Private Sub btnStop_Click()
Me.TimerInterval = 0
Me.txtEndTime = Now()
End Sub

To bill in 15 minute increments, use this in your calculation:

Me.txtBillableHours = Round((Me.txtHoursWorked * 4) + 0.4999, 0) / 4

You can disable editing for the rest of the form controls while the timer is running by setting their Locked property.

Once you have work items tracked for each customer, you want to generate invoices from that work. Create a button on your work tracking form to generate an invoice. Use VBA recordsets to loop through all unbilled work items for the customer, create an order record, then create order detail records for each work entry, marking those as billed by updating their flag.

Here is an example using recordsets:

Dim rsOrders As Recordset
Set rsOrders = CurrentDb.OpenRecordset("Orders")
rsOrders.AddNew
rsOrders!CustomerID = Me.CustomerID
rsOrders.Update
Dim OrderID As Long
OrderID = rsOrders!OrderID

Dim rsWork As Recordset
Set rsWork = CurrentDb.OpenRecordset("SELECT * FROM Work WHERE Billable=False AND CustomerID=" & Me.CustomerID)
While Not rsWork.EOF
' create detail record
' mark work as billed
rsWork.Edit
rsWork!Billable = True
rsWork.Update
rsWork.MoveNext
Wend

Now, you will want a form where you can see all customers with unbilled work. Use an aggregate query to sum the total unbilled amount per customer, then display those results in a list box. Double-click a customer to open details, or click a button to invoice all customers at once by looping through the list and creating invoices as above.

Once invoices are created, you can automate the process for batch printing or emailing them to customers. To do this, check if a customer has an email address. If so, email the invoice using Access's SendObject command. If not, add that customer to a print queue. For example:

If Not IsNull(DLookup("Email", "Customers", "CustomerID=" & Me.CustomerID)) Then
DoCmd.SendObject acSendReport, "InvoiceReport", acFormatPDF, CustomerEmail, , , "Your Invoice", "Attached is your invoice.", False
Else
' add to print queue
End If

To print only customers without email addresses, create a report filtered to those customers and send to the printer. You might email invoices weekly and do a paper mailing monthly, using smart logic in your code to separate the two types.

By following these steps, you will be able to filter and print report data based on form criteria, manage custom pricing for individual customers, track and bill time-based work, and efficiently batch print or email invoices, streamlining your business workflow using Microsoft Access and VBA.