Customer Pricing, Time & Billing, Batch Invoicing
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.
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 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: