Access 310 starts off by covering Conditional Formatting, where you can change the format of a field for each record based on the data within the field.

Next, we're going to learn how to resize our forms (height and width) using VBA code.

In lesson three, I'm going to teach you how to take your company logo that is on several different forms and reports, and move it into a table. The benefit is that you won't have to duplicate the logo in multiple places if it ever needs to change, and it will save space in your database (images take up a lot of space).

We'll also create a Settings table and form for different company settings (name, address, etc.) that we can look up for our forms and reports.

Now we can easily change that logo throughout our database by simply pasting it in our Company Settings form.

Next, we'll start tracking Payments for each order. We'll need a payment table and form, and then a button on our order form to show all of the payments for the current order.

This lesson will teach you about the DSUM function to add up all of the values in a different table (like all of the payments for the current order). I'll also show you the Modal and Popup properties for forms so a form stays on top of other forms - and so your users have to close the current form before they can click on anything else behind it.

Next, we'll calculate the amount due on the order, but to do that we'll have to learn how to read the order total off of the subform. This isn't easy to do. I'll show you the correct notation for it. Yes, the grey area below is the subform. Payments is calculated with DSUM, and AmountDue is just math, but it has to read the OrderTotal.

In lesson six, we'll use the MsgBox function to return a yes or no value. We'll do this specifically to warn the user if an invoice is already marked PAID and they try to put a payment on it - an "are you sure" event.

We will do more with If/Then statements and learn more about VBA programming logic. We'll also learn about the BeforeUpdate event which is very similar to AfterUpdate, except BeforeUpdate allows you to do things before the data is written to the table - like cancelling the event!

Next, we'll put the Payments and AmountDue on the Invoice report. We'll put some VBA code in the build event of the section to hide the Payments field and label if there are no payments.

We've learned about the CanGrow / CanShrink properties before, but what do you do when you have labels on your form? They can't grow or shrink automatically. I'll show you how to deal with that.

Next, we'll have the database notify us if enough payments have been received on an order to mark it paid - when we close the payments form. Of course, more VBA code in an event.

Finally, we'll fix our Accounts Receivable report, because that was built before the database handled payments - we want it to accurately reflect the amount due on an invoice.

ACCESS 310 - Course Outline

0. Introduction - 4:33

1. Conditional Formatting in Forms - 5:05
Changing form field formatting based on data

2. Resizing a Form with VBA - 6:58
Using Visual Basic code to resize forms when they open
Me.InsideHeight, Me.InsideWidth
What are Twips

3. Company Settings Table, Part 1 - 11:23
Moving our company logos from forms/reports into table
Benefits: easily changed, saves space
Creating a company settings table
Locked Property
Inserting a Bound Object Frame in a report
DLOOKUP in a report object control source

4. Company Settings Table, Part 2 - 7:17
Closing one form when another one opens
DoCmd.Close
OnClose Event
When an open record on one form locks another
Using DLOOKUP to get other values from company settings
Changing the form caption property
OnOpen Event for a form
Me.Caption

5. Tracking Payments, Part 1 - 21:31
Payment table and form (PaymentT, PaymentF)
Button on order form to show payments for current order
DoCmd.GoToRecord acNewRec to go to a new record
Using DSUM() to add up all of the payments for an order
How is DSUM different from DLOOKUP
Popup and Modal properties for a form
Refreshing the records on a different form
Forms!OrderF.Refresh and Me.Refresh
Calculating the amount due on an invoice
Accessing a value on a subform
Forms!OrderF!OrderSubform.Form!OrderTotal

6. Tracking Payments, Part 2 - 15:19
Warning users not to make a payment if invoice is PAID
vbExclamation
vbYesNo+vbExclamation
Creating a new value called MyReply in VBA Code
Exit Sub
Option Explicit
Explicitly declaring variables
Using DIM to create your own variables
Adding more logic - don't allow a payment if it's a QUOTE
Using the BeforeUpdate Event
Don't allow a user to change an invoice to a quote if PAID
Cancelling a BeforeUpdate Event
Don't allow a user to mark a QUOTE as PAID
Don't allow a user to put payments on a Quote
Refreshing the form record before printing an invoice
Putting payments and amount due on the Invoice report: DSUM
When [OrderT].[OrderID] doesn't work use [OrderT.OrderID]

7. Tracking Payments, Part 3 - 18:28
Putting payment info on Invoice report
Hiding the payments field and label if there are no payments
OrderT.OrderID Footer Section Build Event
GroupFooter1_Format section
Invalid Use of Null
What to do if there are no payments?
On Error Resume Next
If IsNull(Field) Then
Hiding a line
Creating a "fake" amount due for orders with no payments
Getting rid of empty space for invisible fields
Labels don't have CanGrow or CanShrink properties
Changing labels into text boxes with ChangeTo
Making the label caption into a control source
Hiding the payments and amount due if the invoice is paid
Hiding the amount due if it's a quote

8. Tracking Payments, Part 4 - 12:14
Having the database notify us if enough payments received
Do you want to mark this invoice PAID?
vbQuestion
Notify the user if the customer has overpaid
Fixing our accounts receivable report
Adding the total payments to accounts receivable: DSUM

9. Review - 5:27