Microsoft Access Time & Billing Seminar
Welcome to the Microsoft Access Time & Billing Seminar. You will learn how to 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.
We will begin by setting up a new database, including many of the options that I prefer for my projects. Then I'll show you how create a simple customer table, customer form, and customer list form.
We'll build an order entry form with the related tables and queries. We'll link it to our customer tables. We'll create a fully printable invoice report. We’ll cover some of the advanced preferences that I use for all of my projects. I'll show you which of the Access Options I like to change including setting up a Trusted Location, confirming document deletions and action queries, and more.
I'll show you how to create an order list so you can see all of your orders sorted by date and double-click on one to open it up. We'll copy the customer's address info to each order, so you know where the order was shipped or billed at the time it was placed. We'll also create a product list so you can pick a product and add it to the order with one click.
We are going to learn about a very powerful way to work with data directly in our tables using Recordsets. These are objects that we can use to read, write, add, delete, and manipulate data directly in VBA code without having to use SQL, which can be slow. We'll talk about what Recordsets are, why we want to use them, and we'll set up our first example. There’s really no way around it. In order to work with time and billing, you really NEED to learn Recordsets. I tried to do this with just basic SQL, but the end results are clunky and not the best. Don’t worry, I’ll show you the basics and what you need to get started. We will learn how to loop through the records in a table using a recordset, displaying whatever fields we want. We will then add those items from the recordset to an unbound "value list" list box. We'll also see how to manually add and delete records from the list box.
Now that we have the foundation for the database built, 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.
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.
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.
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.
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.
It is strongly recommended that you have completed my entire Access Beginner and Expert series (at least Expert 1 and 2). My Developer 1 class is highly recommended. If not, at least watch my free Intro to VBA video to make sure you understand the basic concepts of VBA programming.
I am using Microsoft 365, roughly the equivalent of Access 2016 or 2019. Everything in this seminar should work with every version of Access back to 2007.
Please note that this seminar is NOT downloadable. You must watch it online. Since it is made up of a collection of lessons from different courses and TechHelp videos, a download ZIP file is not available.
If you are a current Access Developer student of mine and have taken Developer 1 to 28, please contact me before you purchase this seminar. Several of the lessons here were taken from existing Developer courses and I will be happy to give you a discount based on which other classes you've already taken. No need for you to pay full price to receive duplicate lessons.
Enroll now so that you can watch these lessons, learn with us, post questions, and more.
Please feel free to post your questions or comments below. Thanks.
You may want to read these articles from the 599CD News: