Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 
Courses - Microsoft Access 310
Description: Advanced Access
Running Time: 108 minutes
Pre-Requisites: Access 309 very strongly recommended
Previous Lesson: Access 309
Next Lesson: Access 311
Main Topics: Conditional Formatting, Payments on Orders, DSUM, Modal, Popup, If/Then
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    

Order before 11/24/2019 to get a FREE upgrade to our Access 2010 version!
Click here for details

 

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

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP