| |
| |
|
Courses - Microsoft
Access 305 |
| Description: |
Advanced Access |
| Running Time: |
79 minutes |
| Pre-Requisites: |
Access 304 very strongly recommended |
| Versions: |
|
We use Access XP in this course,
but the lessons are valid for all versions of Access from 95 to 2003.
There are cosmetic changes in Access 2007. Order before 3/14/2010 to
get a FREE upgrade to our 2007 version when released!
|
|
|
|
|
| |
 |
Microsoft Access 305
Advanced Access Development
Aged Accounts Receivable, IIF
Function, MsgBox with vbYesNo, BeforeUpdate Event, Cancel an Event,
vbYesNoCancel. 79 Minutes |
|
|
|
AC305 Major
Topics |
- Aged Accounts
Receivable
- IIF Function
- MsgBox with
vbYesNo
- BeforeUpdate
Event
- Cancel an Event
- vbYesNoCancel
|
Access 305 continues our advanced Access
database development with VBA programming. We continue work on our sales
and order-entry system.
This lesson focuses on building an Aged
Accounts Receivable. You will learn a lot of new tricks including
using a MsgBox to ask questions, the vbYesNoCancel option, the
BeforeUpdate event, and much more.
We will begin by creating a query to hold our
Accounts Receivable data.
You will learn how to determine how old
certain invoices are - which are current, which are 30-days late,
60-days, etc.
This is the basis for an AGED Accounts
Receivable:
Then we'll create a Report to display our
AR information. We'll create form headers and footers to sum up the
data.
Next we'll learn how to give each order a
Percent Discount.
We'll give each item on the invoice a
possible discount as well (in case certain items are, for example, 50%
off).
We'll learn how to get information back from the
user by asking questions with the MsgBox command:
We'll learn about the BeforeUpdate event
which allows us to cancel an event in progress:
|
Access 305 Outline
|
| 1. Accounts Receivable
Query
Aged Accounts Receivable
Create a Query with OrderTotals
Add OrderDate to tell Age of Order
Use IIF Function to Add Aging
Current
LessThan30
LessThan60
MoreThan60
Add DaysOld Field
2. Accounts Receivable Report
Create the report
Format as Currency
Hide $0.00 values with VBA Code
The Visible Property
Put SUMs in the Report Footer
Create an AR Total
3. Percent Discount on Orders, Part 1
Discounts as a dollar amount
Percentage Discount
Add PercentDiscount to OrderT
Add PercentDiscount to OrderDetailT
Adjust SalesTax to figure Discount
Link the Discount default to OrderF
4. Percent Discount on Orders, Part 2
Add PercentDiscount to CustomerTable
Add PercentDiscount to CustomerForm
Add to AfterUpdate event CustomerCombo
Update DiscountRate on Line Items if Form Changes
Create an Update Query to change Rate
Create an AfterUpdate event to run Update Query
MsgBox to Get a Response
vbYesNo, vbYes, vbNo
Line Continuation Character in VB Code
Dim variables
Option Explicit
5. BeforeUpdate
The Cancel parameter of BeforeUpdate
OldValue property
vbYesNoCancel |
| |
|
Try a FREE Demo Lesson |
| |
| |
|
 |
Huge Discounts Available
When you purchase multiple classes together
Huge savings up to 50% off! Order Now. |
|
| |
Student Interaction:
Microsoft Access 305
|
Richard on 1/1/2007:
Accounts Receivable Aging, vbYesNoCancel MsgBox, BeforeUpdate Event, Cancelling Events, more |
|
Bin Chen on 3/21/2009: Hi Richard,
I have purchased and gone over Access 305 with respect to Accounts Receivable. but I am having trouble using the IIF function to count the aging of balances. I get an error. Please help. |
|
Richard Rost on 3/23/2009: Bill, I would need to know much more than this to help you - for starters, what is the specific error message you're getting? |
|
Bin Chen on 3/23/2009: Hi Richard,
Thanks for your response. I get an error msg " You may have entered an operand without an operator" error when applying the IIF function as per the lesson. I applied the following statement:
"LessThan30: IIF(Invoicce Date=Date()-30, Balance,0)"
I want to use the IIF to use the Invoice Date from the Billing Private Table to determine the aging of balances.
I don't know where to go from here-Please help.
|
|
Richard Rost on 3/24/2009: Bin, make sure you if you have SPACES in your field names that you enclose them in square brackets:
LessThan30: IIF([Invoice Date]=Date()-30, Balance,0) |
|
Bin Chen on 3/24/2009: Hi Richard,
I enjoy learning from your lessons. I have created my Accounts Receivable Report and there is one item that I need for collections that I can't get the data into the report. Customer Telephone number. This is nested in another table called Patient Data Table but the AccountsReceivableAgingQ does not have the patient telephone#. How do I bring another table into the current report so I can I the Telephone as a field list item to choose.
Thanks. |
|
Richard Rost on 3/24/2009: Either add the appropriate table to the query, or create another query based on the two and link it in. You can't bring another table into the REPORT, but you can bring it into the underlying query. |
|
Jonathon Mun on 9/11/2009: Hi Richard,
I was surprised that you did not use VBA code for the Aging Receivables. Can this be done? |
|
Richard Rost on 9/12/2009: Jonathon, what do you mean? I used a simple IIF() function in a query for the aging part (30 days, 60 days, etc.) How would you suggest using VBA code for this? Personally, I've always been a "use the easiest solution" kind of guy. If I can get away with a basic query, then that's what I'll use. :) |
|
|
| |
| |
|
You may want to read these articles from the 599CD Blog: |
|
| |
|
 |
| |
| Do you have
questions about
Word, Excel, Access, Web Design, or computers in general? Just
ask us anything you'd like.
Click here
for assistance. |
|
|
|
|
|
| |
|
CLICK HERE for a FREE
lesson |
Order
your first 599CD course now.
Your Satisfaction is Guaranteed!
|
|
|
|
|
|
| |
|
|
|
|
