Access 2007-2013
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)
 

 


 

 
 

Student Interaction: Access Payables Seminar

Richard on 11/10/2010:  Microsoft Access Payables Seminar Build a database to manage the accounts payable and bill payments for your small business or personal finances. 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 Click here for more information on this seminar.
 Richard Rost on 11/10/2010: MORE BUG FIXES that didn't make it in the videos:

If a payee doesn't have TERMS selected, and you add a bill or process a payment for him, it will generate an error. You can fix this by adding:

On Error Resume Next

to any subroutine that generates this error message.

Alex Hedley on 11/11/2010: Intellisense in Access, brilliant addition!
Will save all that time looking for reasons why queries don't work due to silly spelling mistakes

Alex Hedley on 11/11/2010: Video 13. Payee List Sorting
Missing content?
The start skips to another point in the video

Reply from Richard Rost:

Yep. This is a bug. Somehow the video components were added out of synch. I'm fixing it now. Thanks for bringing this to my attention.

Update: it's fixed.

Julius Bratton on 11/12/2010: FYI, when i downloads this course. The order of the lessons in the ini file is incorrect 00 - 09 is at the bottom. I was able to fix it but other users may not be able to.

Reply from Richard Rost:

See what happens when you forget the "ORDER BY" clause in your SQL statements? Videos download in the wrong order. :) Thank you for bringing this to my attention. It's fixed. You can re-download the lessons if you like and they should come in the right order this time. I'm surprised it's worked this long without any other courses having this problem.

Brent on 11/14/2010: I don't know if I am the only one having this problem, but I download the videos and I watch with Windows Media Player, I don't want to have to install more players and they have been working, but when I got up to Lesson 5 It seems to freeze on one screen and the audio continues on.

Reply from Richard Rost:

Brent, it's always possible that THAT specific video didn't download 100%. Try downloading it again and let me know. It's the EXACT same video file that appears in the Theater, which nobody else has complained about. Is anyone else having this issue? If you can give me the time index of where it freezes for you, I'll check it out myself.

Brent Rinehart on 11/14/2010: Lesson 5 I'm getting the title screen, and the audio in the amicron video player, the video part is not working

Reply from Richard Rost:

I just re-watched the first 5 minutes of the video, and it's OK for me. Again, try re-downloading it.

Kenneth Lange on 11/16/2010: I am Trying to follow along and build your Accounts Payable Database to see how it works, but when I got to AP-23 Seminar at timestamp 5:54 the "PayeeListF" showed an "Add Payee Button" that I did not have on my form.

In Seminar AP-24 @ timestamp 3:45, my recolor code is not working as yours does it the video.

In Accounts Payable Seminar AP-25 @ timestamp 1:30, the code in the Video Seminar shows 'Dim S' & 'Dim C'. My code from going through the Seminar to this point shows 'Dim R' & 'Dim C'.

Is there something that I did Incorrectly, or am I missing something?

I am a Newbie with Access Programming.

Thanks.


Reply from Richard Rost:

I'll have to go through and re-watch the videos to see exactly what you're talking about. Is anyone else having this problem?

Kenneth Lange on 11/16/2010: Greetings Richard,

I do not know why, but the Video Seminars (Currently viewing Accounts Payable), playing for me in the Amicron Video Player seem to play audio, but the Video seems locked up when I try to play them sequencially. I have found that when the video is locked up, Pressing the 'Stop' Button, then wait for a few moments and then Press the 'Play' Button and everything seems to work Fine again.

Yes, I also tried to install your updated video player.

Iam using windows 7 with the latest updates from MS.


Reply from Richard Rost:

Kenneth, I don't know what the problem could be. You say it works fine in the online theater, but you have problems with the download version. I, too, have a PC here with Windows 7 and all of the latest updates (IE, WMP, etc.) and it works fine. Can you bypass MY video player software and play the video files directly from Windows Explorer? They should be in your Documents/599CD folder.

Kenneth Lange on 11/16/2010: Richard,
As I have been following along with your Accounts Payable Seminar Series, I am in Video 45 on Referential Integrity at timestamp 7:37 and you are adding Forms!Payee!BillList.Requery to the Form_Close Event, but I already have that in the Form_Close() event from a Previous Lesson. Did I miss Something?

Reply from Richard Rost:

That's always possible. :) It could also be that I added it, accidentally deleted it, and then re-added it again in the video. Most of the time while I was building this seminar, I had TWO copies of the database going. I would make the change in DB1 to make sure it worked, and then walk through it on video for DB2. It's always possible I goofed and added it twice. The important thing is that it works.

jimmy on 11/16/2010: Greetings Richard,

please let us know what software you will focus by the next courses i don't like surprises


Reply from Richard Rost:

Ha ha. Excel 2010 is coming up next.

 jimmy on 11/19/2010: it's a excellent course but why you are so tired

Reply from Richard Rost:

Recording videos for 6 hours straight until 3am. Not enough coffeeeeee. :)

jackie on 11/21/2010: do you plan to make a handbook for this seminar?

Reply from Richard Rost:

Yes. If enough people sign up for it, I will produce a handbook for it.

Brent Rinehart on 11/28/2010: In this lesson around 10:50 you talk about changeing the default value of the sortby box to display the form by due date, how would you show due date desc, I tried typing d"uedate DESC" in the default section, but still showed due date with the oldest unpaid bill at the bottom I would like the next bill due to show on top.

Reply from Richard Rost:

You can make it "DueDate DESC" if you want. That should work. However, keep in mind that the OLDEST dates are the SMALLEST values, so if you sort it that way you'll see the bills due furthest in the FUTURE first. Remember to put this as the value in our Sort Box - not the listbox itself.

Brent Rinehart on 11/29/2010: After going through Lesson 19 - Bill Summary Form 5 when I double click on the billsummaryF I get a error that opos up that says "syntax error in FROM clause" I double checked everything I did could not find anything wrong even restarted with the backyp database before lesson 19 and redid it and got the same error. What would cause this?
 lynn robbins on 12/5/2010: Regarding deleting or changing data in an object: query, form.report. In which does this change or delete data in original table or throughout database?

Reply from Richard Rost:

Lynn, I don't understand your question. Can you elaborate please?

Lynn Robbins on 12/22/2010: In what objects is the data live. If I change data in a query or report will it change or update the original data. If i make an error entering the data or mistakingly edit it, Will it change the original data?
In which objects do I have to be careful about this?
In objects other than original table where entered

Reply from Richard Rost:

Tables, queries, and forms are all LIVE. You can't change data in a report. If you make a mistake, you can usually undo it (with the newer versions of Access). Once you commit the record to the table, however, you could be out of luck. Of course, Rick's #1 Rule is ALWAYS, ALWAYS, ALWAYS make a good backup.

Steve Henderson on 2/14/2011: I use Access 2007. When I try to set a Record Source on the All tab of the Property Sheet it asks which table I want to attach it to, but it does so inside a Query, not as you show. Is there a problem with my Access settings or is it another issue? Thanks.

Reply from Richard Rost:

Steve, I'm not sure what the problem is. You should be able to put any table, query, or even an SQL statement in the Record Source property of a form. Can you tell me, more specifically, what you're doing and what is happening?

Mubeezi Micah on 5/18/2011: Dear Richard,

It seems there is some logic that i don't understand at 6:45. Much as minus-day sorts the error, i cant exactly understand how it solves the problem

Please shed a bit more light on this

MICAH

Reply from Richard Rost:

You're adding a month and then subtracting a day. So if you have 9/1/11 it will add a month, bringing you to 10/1/11 and then subtract one day to 9/30/11. This gives you from the FIRST to the LAST day of the month.

Kenneth Lange on 6/3/2011: Maybe I did something wrong here, but I thought the bills would have shown up in the Recurring Bills to show that I havePayments Due. After looking at the code in my program, I see where the Recurring Bills are Seperate from the Stand Bills in my Database. Is this the way Your BillsT and RecurringT work?

Leroy A Diaz on 10/4/2011: i have Bought Your Access Payables And Have Tried To Build It As You Go BUT I Have Come To A Wall On It. When You Build The "PaymentsForCurrencyBillQ" And In The Criteria Section Put "Forms!BillF!BillID" it works fine when you run it. The problem i am having is when you add a listBox to the "BillF" and use the wizard to build it. I am using access 2000 and the wizard for this part gives me an error, saying "No Value Is Given for one or more parameters" so is there a way to do this for us who still use access 2000?
Jim Gray on 11/22/2011: I am building a database for our volunteer ambulance service. We need to submit an annual budget to the city and I would like to use this seminar to keep track of our payments for services, medical supplies, etc. Would I use the vendor categories to tie payments to specific payables accounts assigned by the city and then put these payments into our budget for the year? We need to keep track of how we stand against our submitted budget, submitting these status reports to the city council on a monthly basis. One more little wrinkle..Each invoice could be spit into several accounts so I need to allocated partial payments via line items on the invoice. It looks to me that this seminar would accomplish all these goals and more.... Thanks as always Richard for your insight.

Jim Gary

 Jim Gray on 2/13/2012: Richard,
I just started this seminar and perhaps I am jumping the gun a little. I have constructed a database for our volunteer ambulance service using the lessons from your access 2003 lesson package. I therfore have the billing and accounts receivable side of our opertation pretty well designed. This seminar is going to help me put together the payables side and I need to tie each of our payables to a city fund code (simiar to a corporate budget or account number). If I put a field in the category table for that fund code, would I be able to then tie the payable back to this number in a budget? Seems like I should be able to...

Thanks for your great courses and help...

Jim Gray

Jim Gray on 2/16/2012: How exactly would you handle a payee that could have invoices in several different categories? (eg. a VISA business account) Would you set up that as a many to many relationship with a cross reference table? If so would that influece how you set up the remainder of the tables and forms? Thanks as always for your advice..
 Jim Gray on 2/20/2012: Couldn't you make the RecolorLabels Sub from before a public function and just pass the label names to it, rather than rewriting this code here?
 Jim Gray on 2/21/2012: Richard,
I noticed something odd using the DMax function on dates eg LastBillDate. It returns a value that does not sort correctly. I had to use the CDate function on that parameter to return a value that would sort properly....

 Jim Gray on 2/22/2012: When you use DMAX on date fields, does it return something other than a date formatted object? I had to use the CDate funcion on those formulas computing lastbill and lastpayment to get the list to sort properly....
 Jim Gray on 2/25/2012: Richard,

Not sure but I think that you forgot to add the last statement to your RecolorLabels sub---the Me.Controls(LabelName).Forecolor=rgb(200,100,50) statement?

 Jim Gray on 2/28/2012: I too have that error message pop up. I have researched the syntax error problem on your forum and found nothing applicable and I will go back like Brent did to find where I made an error. I was just curious if you folks had a chance to research this issue and had any results. Thanks for your continued time and patience with us who struggle a little with some of the intricacies of Access.
SteveHenderson on 10/2/2012: On lesson 19 of the Access Payables seminar I added the total for the Balance column (time index 4:33). I now receive an error stating 'Syntax error in FROM clause' whenever I run BillSummaryF. I've checked my code against that in the lesson and can find no difference in spelling, spacing, etc. It looks the same. What can be causing this error? Thanks.

Reply from Richard Rost:

"Syntax Error in FROM Clause" usually indicates that the table or query name in your SQL statement is incorrect. Also, make sure the fields you're performing calculations on are actually in the table/query. For example, if you say:

SELECT FirstName, LastName FROM CustomerT

and LastName isn't IN the CustomerT, you'll get an error.

SteveHenderson on 10/2/2012: The problem seems to have corrected itself when I continued with the lesson and applied the 'size to grid' formatting. Why would sizing eliminate the error? Thanks.

Reply from Richard Rost:

Who knows? Gremlins. :) Seriously... sometimes problems just fix themselves. When something CRAZY is going on: (1) close and reopen the form, (2) close and reopen the database, (3) reboot the computer. Sometimes code just needs to reset, or variables need to clear, or memory needs to wipe, or SOMETHING is going on behind the scenes that messes stuff up.

Bruce Reynolds on 12/7/2012: In the join property between BillID of the BillT table and BillID of the PaymentsByBillQ query, is this a one-to-one relationship? If not, is it a special join? Also, is this a local as opposed to a global join?

Thank you,
Bruce

Reply from Richard Rost:

PaymentsByBillQ only has one table in it: PaymentT. There are no joins. The BillID comes straight from the PaymentT.

Bruce Reynolds on 12/11/2012: In the private sub Command15_Click(), how come you did not use the dim statment to define the data type for the R variable for the line:
R = inputbox("The balance due on this...)

Is the default data type for the inputbox a variant data type?

Reply from Richard Rost:

Bad habit. You don't HAVE to DIM all your variables, but you should. Yes, if you don't DIM them, you end up with a variant, but VB is pretty good at realizing what kind of data you're working with. It's not like C/C++ where you have to DIM everything. That's what that "option explicit" statement does.

Bruce Reynolds on 2/4/2013: This was the most comprehensive Access Seminar that I have taken on 599cd.com.

Reply from Richard Rost:

Glad you enjoyed it.

khaja nizamuddin on 7/18/2013: Hi Richard,

I am using Access 2007 version, when I create a form it's coming full screen/page and not as I see in video a smaller type of form, I have tried searching over internet for a solution and cant find one, please guide me.

Reply from Richard Rost:

I cover this in my Access Beginner Level 2 class. You have to switch your database from the new TABBED VIEW to OVERLAPPING WINDOWS. It's under FILE (the round Office Button in 2007) then Access Options > Current Database.

khaja nizamuddin on 7/20/2013: Hi Richard,

Please guide me in setting up currency to Indian Rupees ( Rs ) instead of $ for entire project.

Reply from Richard Rost:

This is something that you do in your WINDOWS Control Panel under Regional Settings. It's not something that you set up in Access.

khaja nizamuddin on 7/28/2013: Hi Richard..

You have mentioned in Intro lesson that there is a lesson for receivables , I wish to know if there is a seminar similar to payable for receivables.

Regards

Khaja

Reply from Richard Rost:

There isn't a specific seminar for this topic because I covered it in my Access (2003) Advanced series in lessons 305, 310, and 312. CLICK HERE for links. I will also be covering this topic again in my upcoming Access 2013 lessons as I'm currently in the process of revising them and rebuilding the order-entry system.

Ramona Woitas on 9/7/2013: 08:25
Up to this point it all worked out fine. However when I reopen the Bill Summary Form, it is blank. The Due Today and Unpaid Bills ONLY are the defaults like yours, but there is no payee information.

Ramona Woitas on 9/7/2013: 7:22
My Total and Past Due columns sort beautifully. However my last bill and last payment sorting doesn't work. I have checked the SQL statements to make sure they were right, but I don't know what I did wrong / missed!

Ramona Woitas on 9/7/2013: Richard I am so sorry!
I guess a good nights sleep does wonders. I must have been going cross eyed last night.

When I open the Bill Summary Form, I can now see the payees and the details. I forgot to change the Where statements from WhereStr to WhereSQL in Lesson 18
Thanks Anyway!

Reply from Richard Rost:

Glad to help. :)

Ramona Woitas on 9/7/2013: 2:58 Lesson 22
When I go to open the payee list from the main menu and select National Fuel, I get a Run-Time error '2447': This is an invalid use of the .(dot) or !operator or invalid parenthesis.


This is what comes up when I hit Debug:
BillList.RowSource = "SELECT BillID, DueDate, AmountDue, Balance " & _
"FROM BillsWithPaymentsQ WHERE " & WhereStr & _
" ORDER BY " & Forms!PayeeF!SortBy

Reply from Richard Rost:

I need to see your WhereStr and SortBy to tell you what's wrong. MsgBox the complete SELECT statement (set it to a variable first then MsgBox it) and that should show you where the problem is.


Ramona Woitas on 9/7/2013: 4:19 - Lesson 24
When you made a mistake for the Form_Open (Cancel As Integer)

RecolorLabels was ("DueDateLabel") and you changed it to ("BillDateLabel")

Where did you go?


Reply from Richard Rost:

What do you mean?

Ramona Woitas on 11/5/2013: I'm just reviewing the payables seminar again as there is some very good information in it that I can use for my database. However I have one question!

You were creating the "BillsWithPaymentsQ" and you brought in the PayeeID Field.

Is there a way to see the actual Bill Name and Payee Name in the query instead of the Bill ID and Payee ID number?


Alex Hedley on 11/17/2013: Hi Ramona,

Just add the 'PayeeT' to the Query, join on the [PayeeI] and pull in the [PayeeName].

Alex

Giorgio on 6/29/2014: The link that says, "You can click here to watch the first lesson absolutely free." doesn't work

Reply from Richard Rost:

Thank you. I'm still in the process of moving the video files to a new server. That's one of the pages I haven't gotten to yet. I'll get it fixed soon. You can find the seminar in the Online Theater and the first lesson (Introduction) for ALL of my courses is unlocked.

Darrell on 9/28/2014: In order to get my list to work I had to leave of the order clause

Reply from Rick Rost:

Your ORDER BY clause is probably typed in wrong. What do you have? Post it here.

Lynda Chase on 10/7/2014: Lesson 29 Payables seminar
Hi Richard, Just wondering, if I have a bunch of bills in the paymentunprocessedlistF, and I go to quickPay a selected bill, the form only shows me on a new blank form, and I have to manually scroll up to see the quick payment just made. Is there a way to restrict the visible records on this form? Hope you know what I mean.

Reply from Alexander Hedley:

Hi Lynda, I'm not exactly sure what you mean so you might need to explain a little more but normally if you want to restrict a set of records you can just create a QUERY with the ones you want to show and use that as your Record Source.

Lynda Chase on 10/9/2014: Payables Seminar Lesson 47
Hi Richard

Standard terms of trade here in New Zealand are for a specific date, e.g 20th month following - could you please tell me how I could enter those sorts of terms so they will automatically update the duedate field.

Thanks
Lynda

Reply from Richard Rost:

So you want the 20th day of each month instead of the first? That's easy. Find the first day and then add 19 to it.

Kalyana Keerthi on 11/23/2014: Dear Richard,

Can you explain how to format currency or number like 235,4575.23 and Align to right. All list box in Access payable Seminar lesson. If I use CCur(FiledName) It formatted with $ sign I cannot use $ sign for local currency for Rupees.

I have set all currency Field, Data Type Number, Field Size Double, Format Standers, Decimal Places 2 . I tried VB Code Amount Due like this Format( AmountDue , ###,###.00) But it Did not come right.

Can you please explain how to format Numbers and Date also like dd-mmm-yy to right Align.

Thank you
Kalyana



Reply from Alex Hedley:

What is 'Region and Language' set in Control Panel?

You can set the alignment of a Field in the Ribbon.
Select your Textbox, go to the Form Design Tools | Format and select the Align Right option.

For the DateFormat drag your Date/Time Field onto the Form, then in the Format Tab of the Property Sheet type in the Format you want (dd-mmm-yy)

Kalyana K on 11/27/2014: Dear Alex Hedley,

Thank you Reply for my Email. But I could not get the answer I need Align Right & Format Numbers in List Box Not a Text Box.

Thank You
Kalyana
Student


Reply from Alex Hedley:

Ah sorry I missed the Listbox, this has far less control, if you want a particular FORMAT in the Listbox add the Format to the Query then base the Listbox source on that Query.
Unfortunately you can't align the text in a Listbox.
You could make it a mono-space font and use some padding (extra spaces) to mimic an align right.

Lynda Chase on 7/17/2015: Hi Richard - At 12.49 I would like to be able to set the default value to the 20th of the month following, which is standard terms of trade in New Zealand. Can you please tell me how I would write that in the Default Value Field.

Reply from Alex Hedley:

Check out Expert 27 and Expert 28
These cover Date/Time Functions.

You could use the DateAdd Function
DateAdd ( interval, number, date )

DateAdd ("m", 1, #20/07/2015#)
Result: '20/08/2015'

If you need to get the Current Date and use that you could do the following:

You could use DateSerial to build up the date
=DateSerial(Year(Now()), Month(Now())+1, 20)

Lynda C on 7/19/2015: Thanks Alex, I'll give that a try
Mark on 5/6/2016: I'm having problems with my DoCmd.GoToRecord,,acNewRec - it opens the form, but does not go to the new record and populate the fields with the data.

If IsNull(PurchaseMainList) Then Exit Sub
DoCmd.OpenForm "PaymentsUnprocessedListF"
DoCmd.GoToRecord , , acNewRec
Forms!PaymentsUnprocessedList!PurchaseID = PurchaseMainList Forms!PaymentsUnprocessedList!PaymentDate = Date Forms!PaymentsUnprocessedList!PaymentAmount = PurchaseMainList.Column(6)

DoCmd.OpenForm "PurchaseListF"
RequeryPurchaseMainList

Any ideas why this is happening?

Regards, Mark

Reply from Alex Hedley:

Try doing one thing at a time.
Does the first line work
DoCmd.OpenForm "PaymentsUnprocessedListF"
Yes, try the second as well. Until you find the one that doesn't.

Mark on 5/27/2016: Hi, problem solved, I didn't realise I missed out the "F" at the end of the form names... works perfectly now. It took me hours to realise my mistake...

Mark B on 5/30/2016: Hi,

Is it possible to format my "Paid" field in a list box to show green and my "Unpaid" field to red, like you can in a continuous form?
Regards, Mark

Reply from Alex Hedley:

List boxes unfortunately don't have this capability.

Matthew Pattison on 12/7/2016: 8.55 or can u lock billDate with a AfterUpdate event? to prevent changing it?

Reply from Alex Hedley:

Do you want it locked after you set it?
You could toggle the .Enabled or .Locked Properties in the AfterUpdate.

 

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
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