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  
 
 
 

Microsoft Access Payables Seminar
 
Manage your Accounts Payable & Bill Payments
for your Small Business or Personal Finances

 

 

This 11 hour video seminar will teach you everything you need to build a database in Microsoft Access to manage your Accounts Payable and Bill Payments for your small business or personal finances.

This seminar was developed because most of my other Access courses and seminars deal with managing the "in" part of your finances (invoicing your customers, tracking their payments, and so on). This seminars covers the "out" part of the equation.

If you have ever wanted to keep track of your bills and accounts payable, but find other "canned" financial programs too limiting for your accounting needs, this seminar is for you. Here is the perfect opportunity to custom-tailor a database solution for your needs. Don't change your business to meet the needs of your software. Your software should adapt to your business methods.

Learn More... Seminar Overview

Click on any of these videos for more information on exactly what's covered in this seminar. The first video is an overview of the entire seminar. The second is a preview of the database we build in class. This will let you see all of the features we'll be developing - to make sure it will meet your needs. The last video is an in-depth lesson summary showing each lesson's topics.

AccessLearningZone.com
  3 minutes
   
Database Preview Lesson Summary
AccessLearningZone.com AccessLearningZone.com
15 minutes 15 minutes

   

After you watch the preview videos above,
click here to download the database we build in this
seminar so you can see everything that is included.

 


 

 
Seminars - Access Work Orders
Description: Learn how to build a database to manage the accounts payable and bill payments for your small business or personal finances.
Versions: I will use Access 2010, however the lessons are valid for all versions of Access back to 2000 with some minor cosmetic differences.
Pre-Requisites: Access 101 - 104 a must
Access 201 - 207 highly recommended
Access 301 - 329 very helpful
Running Time: 10 hours, 55 minutes
Cost: $219 - Order multiple courses to receive a discount up to 50% off
Includes a sample, customizable, working database, as built in class.

 

This seminar has several main goals. You will:

1. Track payees / vendors and all related data
2. View account balances at a glance
3. Enter invoices and bills, including recurring bills
4. Process payments for each bill, even multiple payments
5. Print checks right from the database
6. Assign payment terms to vendors, calculate discounts like 2%10 NET 30

7. Generate various reports, such as an aged accounts payable

We will begin by creating a form to track your payees. A payee is anyone to whom you send money, such as a vendor or service provider.

 

You can assign each payee a default payment type (check, online BillPay, credit card, cash, and so on). You can also assign each payee a category (utilities, car payments, rent, etc.) and specify payment terms (NET10, due on receipt, etc.). You can also specify a priority for each payee - so you can pay the URGENT bills (like your mortgage) quickly.

 

You will be able to view all of your payees at a glance. You will see the total amount you owe them, how much of that is past due, the date of their last bill, and the date of your last payment to them. You can sort on any of these columns. You can hide inactive payees (companies you no longer do business with, for example).

 

 

You can add bills for each payee. Enter the amount, and the Bill Date defaults to today's date. The Due Date is automatically calculated based on your terms with that vendor.

 

When you're done entering bills, you'll see a summary of all of the bills for each payee on their Payee screen, along with a total balance. You can sort by any column.

 

Double-click on any one of the bills listed in the box above, and it opens up the Bill Detail form where you can see more information about this bill, such as the balance, any payments made, discounts taken, and so on.

 

 

To pay this bill, just click on the Make Payment button. The database will ask how much of this bill you want to pay. Enter the amount and click OK.

 

Your payment will then be noted on the Payment form. You can make multiple payments for one bill / invoice.

 

In addition to entering bills, you can also use the Bill List form to get a quick look at what you owe. It will show the vendor, due dates, amount of the bill, and highlight something that's past due.

 

There is also a Bill Summary form that displays all of this information in addition to the balance due and vendor priority.

 

 

You can sort this list by any column. You can also filter the list to show bills that are due now, due in 7 days, past due, and so on. You can also decide to see paid or unpaid bills.

 

Use the QuickPay Selected Bill feature to automatically enter in a full payment for the selected bill. You can do this to quickly go down the list of bills that are due and pay them all with one click.

 

If the system notices you qualify for a discount on one of your bills, it will prompt you. You can choose whether or not to accept the discount.

 

You can enter recurring bills that get automatically added to your list of bills to pay at regular intervals (weekly, monthly, quarterly, etc.).

 

 

You'll see a warning message on the Main Menu if you have payments to process, or recurring bills that need to be added to the system.

 

When it's time to sit down and process your payments, you can limit the list based on the payment method. For example, you can pick BillPay to show all of the items that you need to go onto your bank's web site to pay online.

 

Click on View Payment Report to print out a report showing all of your payments broken down by vendor, date, and payment amount.

 

When you're done processing your payments, click on the Mark Visible Payments as Processed button to indicate that you have sent out these payments. Optionally, you can click on the check boxes to mark/unmark these items as processed.

 

You can use the database to print checks on your printer. Click on the Print Checks button. Any payments for the same vendor will be consolidated onto the same check.

 

Yes, we'll even use a function to turn $18.97 into "Eighteen Dollars and Ninety Seven Cents."

 

 

And, of course, there are several different reports you can generate.

 

You can print an Aged Accounts Payable report showing how much is owed to each payee, how much is past due, and coming due in the next 30 days.

 

You can generate a report showing total expenses between two dates.

 

You can create a pie chart showing expenses by payee category between two dates.

 

 

Again, this seminar is perfect for anyone who wants to learn how to build a Microsoft Access database to manage payables for a small business or your own personal finances. You will be able to track payees, vendors, bills, payments, and more when you're finished with this seminar.

This seminar is very long - almost eleven (11) hours - but it's broken up into easily managed lessons of about 10 minutes each. You can sit down, watch a lesson, review the material, test the code out yourself, and experiment. Do a little bit each day. It's long, but it's comprehensive - you won't miss a single step as I've recorded everything from start to finish. 

All of the sample database files for this seminar are available on my Web site. They are available in Access 2007/2010 and 2000 formats (Access XP and 2003 users can download the 2000 version which is compatible). You can download the sample databases here.

This seminar is available to view online in the Amicron Theater. You can click here to watch the first lesson absolutely free.

Of course, if you have any questions about whether or not this seminar is for you, please contact me.
 

 

Access Payables Seminar Outline

00. Intro (3:58)
What's Covered
Pre-Requisites
Versions Used
Student Forums

01. Payee & Category Tables (11:41)
Create new database
Place in trusted folder
Overlapping windows setting
Application title
Collapsed Ribbon
Create the Payee Table
Create the Category Table

02. Payee Form & List (12:55)
Create the Payee Form
Combo box for category
Create the Payee List Form
On Double Click event to open Payee

03. Bill Table & Form (11:42)
Create the Main Menu Form
Make Main Menu the startup form
Create the Bill Table and Form
Conditional Formatting for overdue Bills

04. Bill Data Entry (9:56)
Form for quick data entry of new bills
Alternate row color
Tab Order
Data Entry property

05. Payments & Balances (10:44)
Create the Payment Table
Aggregate Query for Payments by Bill
Bills with Payments Query
Calculate a Balance for each Bill

06. Payments on Bill Form (11:08)
Put balance info on forms
CCur function
Payment listbox on Bill form
Requery listbox in OnCurrent event

07. Bills on Payee Form (9:37)
Listbox to show Bills for Payee
Dynamic SQL statement
Labels for listbox columns

08. Sort Bills on Payee Form (13:02)
Sort by columns
Click on label to sort
RequeryBillList private sub

09. Show Open Bills for Payee (7:51)
Show All Bills checkbox
WhereStr variable in SQL
DblClick event to open bill form

10. Add New Bill (13:41)
Add new bill button on Payee form
BillF made non-updateable
DLOOKUP
IsNull
IIF
Check to see if a form is loaded
OnClose form event

11. Balances on Payee List (12:31)
DSUM function
NZ function null to zero
CCUR function convert to currency
Add balances to Payee List

12. Last Bill & Payment (11:07)
Last Bill Date
Last Payment Date
DMAX function

13. Payee List Sorting (16:45)
Sort functionality on Payee list
Recolor label to show sort column
Me.Controls collection
RGB color function

14. Revise Bill List Form (9:31)
Deleting embedded macros
GoToRecord
GoToControl
Button to open selected Bill

15. Bill Summary Form 1 (10:38)
Create bill summary listbox
Add header labels

16. Bill Summary Form 2 (13:27)
Add sorting capability
Requery the listbox
Sub to recolor labels
Double-click to open bill
Button to open payee
Listbox Column property

17. Bill Summary Form 3 (11:54)
See all, paid, or unpaid Bills
Combo box with set values
WHERE condition in SQL
OnOpen event to set initial listbox

18. Bill Summary Form 4 (12:09)
Add combo box for due date filter
What's past due? Past due 30 days?
What's coming up in the next week, month?

19. Bill Summary Form 5 (8:12)
Total for Balance column
DSUM with existing SQL where criteria

20. Payment Form 1 (8:40)
Create Payment Form
Get default values from previous form

21. Payment Form 2 (8:16)
InputBox to prompt for payment amount

22. Payment Form 3 (10:12)
Refresh all forms on updated balance
Hide payment button if balance is zero

23. Better Add Bill Method (13:29)
Add Payee Button
Refresh Payee List Form
Adding a Bill without an Amount
MsgBox prompt
Before Update event
Cancel Update event

24. Misc Updates (11:17)
Updates to Main Menu
Update color labels on open
Button to add bill on Bill Summary form
More refreshes when form closes
Refresh on change of Amount Due

25. Overpay Bills (11:29)
How to overpay a bill
MsgBox with vbYesNoCancel
Show total balance on Payee form

26. Quick Pay Bills 1 (11:36)
Processed field in Payment Table
Unprocessed Payments List Form

27. Quick Pay Bills 2 (14:04)
VBA code to quick add payments
Open the Payments List Form
Go to new record
Set amounts
Refresh listbox

28. Payment Methods 1 (11:18)
Payment Type Table
Check, Cash, BillPay, Credit Card
Payment Type Combo for Payees
Payment Type Combo on Payment Forms

29. Payment Methods 2 (14:18)
DLOOKUP Preferred Payment Type
Prevent user from adding bogus payment
After Delete Confirm event

30. QuickPay Next Item (7:36)
Set BillList to Null
ListCount property
Use Column property to pick a record
Row parameter

31. Process Payments 1 (11:53)
Add Processed to Payment Form
Edit PaymentWithPayeeNameQ Query
Add Payment Type Filter
Add Processed Filter
RequeryPaymentForm sub
Clear Filters label

32. Process Payments 2 (13:35)
Processed Filter Checkbox
Changing Form Name in All VB Code
Find and Replace in whole project
Add PaymentType to Query
Design Payment Form
Can Grow / Can Shrink

33. Process Payments 3 (14:04)
Sorting & Grouping
Group by Payee
Group Footer Total
Limit records based on Payment List Form
SQL Me.Recordsource for Report
Read filters from Payment List Form
DoCmd.OpenReport
On No Data
On Error Resume Next

34. Process Payments 4 (11:27)
Create a Check Report
Layout fields for printed check
Convert Currency to English
Five Hundred Ten Dollars and No Cents

35. Process Payments 5 (9:47)
Print Account Number on Checks
Mark Payments Processed
While Loop to Mark Processed
ProcessedDate

36. Recurring Bills 1 (12:55)
Recurring Bills Table
Frequency Table
Calculate next due date by frequency
NextDueDate public function
PayablesModule
Select Case statement
DateAdd function

37. Recurring Bills 2 (15:07)
Using NextDueDate in a Query
NZ for unprocessed (null) bill dates
Recurring Bills Form

38. Recurring Bills 3 (9:20)
Target Date
Loop to process recurring bills

39. Recurring Bills 4 (9:49)
Adding recurring bill to BillF form
Testing the loop
With VB command

40. Next Recurring Notice (8:29)
Show next recurring bill date on Main Menu
Modify color if past due
Popup warning message

41. Reports 1 (10:34)
Aged Accounts Payable
IIF to determine current v past due

42. Reports 2 (16:22)
Aggregate Query For Accounts Payable
Due in 30 days
Current Bills
Past Due 30, 60 days
Accounts Payable Report
Page Numbering
Report Footer

43. Reports 3 (10:21)
Bills Between Dates Query
Start End Date on Main Menu Form
DateSerial Function
DateAdd Function
+1/-1 Month Labels

44. Reports 4 (7:46)
Form with Embedded Chart
Pie Chart Summary of Expenses by Category
Format Chart Objects

45. Referential Integrity (8:20)
Setting up Global Relationship
One to Many
Enforce Referential Integrity
Cascade Deletes
Cascade Updates
Data Violating Rules Already
Find Bad Data

46. Payment Terms 1 (8:36)
Explanation of NET terms with discounts
2%10 NET 30
Setup TermsT Table
Add TermsID to Payee Table, Form

47. Payment Terms 2 (14:49)
Remove default dates from BillT
PayeeTermsNumDays Function
Set Bill, Due dates in Amount AfterUpdate
UpdateDueDate Sub
Update Due Date if Bill Date changed
Update same code in BillF
Update Recurring Form Loop
DueDate = NextDue + PayeeTermsNumDays(PayeeCombo)

48. Payment Terms 3 (17:29)
When payment is made, check for discount
Deduct discount from bill amount
Add DiscountTaken field to BillT, BillF
Code in BillF to take a discount

49. Payment Terms 4 (11:59)
Code in Quick Pay to take a discount
Convert listbox text to currency with CCUR

50. Qualified For Discount (6:34)
Query to show bills you can pay for discount
IIF Function to determine if qualified

51. Payee Priority (12:44)
Create Priority table
Add Priority to Payee Table
Adjust Bill Query
Add Priority column to Bill Summary Form

52. Repeat Until Date (11:45)
Recurring Bills Limited Repeat
Fix #ERROR on RecurringF
Add RepeatUntil date to table, form
Set default frequency

53. Bug Fixes 1 (7:35)
Set processed date on Payments form
Sort payments by Payee name
Add Payments to Selected Vendor to footer

54. Bug Fixes 2 (8:39)
Show only Active Payees
Warning if payee has a balance on inactive
Checkbox on payee form to filter payees

55. Bug Fixes 3 (9:54)
Payee Notes were Text not Memo
New Line in Field for Memo
BillSummaryF Due in 7 days
Fix Next Recurring on Main Menu
Add "there are payments to process" warning

56. Review (3:56)

57. Lesson Summary (14:34)

58. Database Walk-Thru (14:25)
 

 


 

 

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