Double-entry Accounting System
Double-entry accounting system, T-account, debit and credit, closing entries, general journal records.Exercise 4.1By Rob Pegoraro
Thursday, May 22, 2008; D03
Few types of desktop software should be readier for replacement by the Web than personal finance.
The concept behind these programs is sound: Track your income and expenses through automatic downloads from banks, credit card issuers and other financial institutions to show where your money's coming and going, and how much of it you're likely to have later on.
But the market long ago calcified into a duopoly of programs, Intuit's Quicken and Microsoft's Money. As they've piled on the features, their usability has suffered. Many users, fearing an ordeal of bookkeeping, avoid them entirely. Those who have bought either program have been forced to ante up for new versions, at $15 to $90 each, when "sunset" policies cut older releases off from account-data downloads.
It might seem that a simpler, cheaper alternative to these programs would have emerged on the Web, now that online shopping and bill payment have made people comfortable with managing money online. But the big Web companies have yet to craft such a thing.
Fortunately, the absence of a shiny new Web application from Google or Yahoo doesn't mean the absence of hope for online alternatives to Quicken and Money. It may just mean you'll have to wait longer to find one that suits you.
The most visible Web competitor so far has been the free Mint ( http://mint.com). Since its launch last fall, this Silicon Valley start-up has drawn 266,000 users, though founder Aaron Patzer did not say how many visit the site regularly.
Quicken or Money vets may find Mint insultingly simplistic. It only links to banks, credit cards and investments and ignores most people's biggest debts (mortgages and car loans) and assets (homes and vehicles), making net-worth estimates impossible.
Using Mint requires you to trust the site to safeguard your bank usernames and passwords, which you must save there before adding any accounts. You can't enter transactions by hand or upload Quicken or Money files. And you can't reconcile transactions against a monthly statement.
But within those limits, Mint provides soothingly simple money-management tools.
When tested with a bank account, two credit cards and three mutual funds, Mint automatically fetched the latest data, converted most gibberish in these accounts' downloads into real names and filed most entries in the right category. For example, a credit card charge to "WHOLEFDS ARL 10042 0ARLINGTON" became "Whole Foods," listed under "groceries."
It was even smart enough to split fees on ATM withdrawals into separate expenses.
Mint then broke down patterns of income and expenses into easy-to-read pie charts.
Mint aims to make money by suggesting better financial services, then collecting commissions. But its advice to drop an American Express card for a Chase Visa ignored the AmEx card's cash rebate.
Two other sites have begun grabbing users as well. The free Wesabe ( http://wesabe.com), an older Silicon Valley start-up, acts like a money-minded social network.
It makes the collective wisdom of its users part of its source code, comparing your spending and earning with the averaged habits of more than 100,000 other "Wesabeans." It also relies on their accumulated input to refine and sort statement entries and offer tips about better deals near you.
But Wesabe may need more users (at least near Washington) to do those jobs well. Most downloaded transactions came through in their original, cryptic bankspeak, and some tips showed a Bay Area bias.
This site's free-form system of tagging, in which you can slap multiple categories onto a single transaction, also yielded duplicate entries in its spending summaries.
Wesabe (which plans to underwrite its free service with a fee-based "pro" option) is even more limited than Mint. It couldn't connect to a Bank of America Visa credit card account, and it doesn't do investments or home or car loans.
In Wesabe's favor, it offers free Mac and Windows uploader programs that keep your account logins on your computer and offers multiple ways to get your data off the site. It even posts a toll-free number that you can call to reach its chief executive each afternoon.
Wesabe may appeal most to extroverts with specific financial goals who can easily set targets and solicit fellow users' advice in its forums.
One of the newest Web-based personal finance tools comes from Intuit, which in January launched the $2.99-a-month Quicken Online ( http://quickenonline.com). Although this site isn't as slick or quick as Mint or Wesabe, it supports investments and mortgages, not just banks and credit cards.
Unlike Mint and Wesabe, it also lets you add coming transactions -- no risk of forgetting the check you wrote to your contractor-- and set bill-payment reminders.
Some basic features, such as transaction breakdowns and the ability to upload Quicken files or download data from the site, still aren't there. But its simplicity makes the gridlocked complexity of desktop Quicken look painfully obsolete.
These sites and other competitors promise tantalizing upgrades. For example, Mint says it will soon add mortgages, with estimates of home values from Zillow.com or another assessment source.
With such improvements, some smart borrowing (picture Mint's interface plus Wesabe's social smarts) and a solid record of security, Web personal-finance software could become an alternative along the lines of Web e-mail. Some people might never accept such a thing, but others wouldn't think of using anything else.
Living with technology, or trying to? E-mail Rob Pegoraro atrobp@washpost.com. Read more athttp://blog.washingtonpost.com/fasterforward/
from: http://www.washingtonpost.com/wp-dyn/content/article/2008/05/21/AR2008052102672_pf.html
Provided by Allen Browne, allen@allenbrowne.com. Updated September 2007.
4. RecordCount without MoveLast
5. MoveNext without testing EOF
8. AddNew without LastModified
This article highlights ten common traps with DAO recordsets in VBA code.
Most of the traps yield no syntax error; they are bugs lying dormant in your code until particular conditions are met. Your program then fails, or returns inaccurate results.
The DAO and ADO libraries both have a Recordset object, but with different methods, properties, and options.
DAO is the native Access library (what Access itself uses), whereas ADO is a more generic library (now superseded by the vastly different ADO.NET library.)
Different versions of Access default to different libraries. See Solving Problems with Library References for details.
This article assumes DAO recordsets.
To ensure your code works reliably:
There are different types of DAO recordset, with different methods.
When you OpenRecordset() on a query or attached table, Access defaults to a Dynaset type (dbOpenDynaset). When you OpenRecordset() on a local table, it defaults to a Table type (dbOpenTable.)
The Table type has different methods (e.g. Seek instead of FindFirst), but it cannot be used with attached tables. So if you later split your database so the tables are attached, the code fails when you use a method that no longer applies.
Always specify the type you want. Dynaset guarantees your code will work for all queries and tables, local and attached. Example:
Set rs = db.OpenRecordset("Table1", dbOpenDynaset) Using any of the Move methods (MoveFirst, MoveLast, MoveNext, or MovePrevious) causes an error if the recordset has no records.
Test before using any of the Move methods. Either of these approaches works:
If Not (rs.BOF And rs.EOF) Then 'There are no records if Beginning-Of-File and End-Of-File are both true.
If rs.RecordCount <> 0 Then '100% reliable in DAO, but some ADO recordsets return -1 as the count.
For recordsets based on queries, SQL statements, and attached tables, the RecordCount property returns the number of records accessed so far. When you first OpenRecordset(), Access grabs the first record, and keeps processing your code while the others load. So, if you test RecordCount immediately after you OpenRecordset, you typically get 0 (if there are no records), or 1 (if there are any, regardless of how many will load.) This does not apply to recordsets of type dbOpenTable type (the default for local tables.)
If you need to know the RecordCount, use the MoveLast method first. This forces Access to wait while all records load, so the RecordCount reflects the entire recordset.
Don't MoveLast unless you really need to: this will be slow with a large recordset or a recordset drawn across a network. RecordCount will always be at least 1 if records exist, so there is no need to MoveLast if you only want to know if you have records to work with.
A MoveNext may take you to the end of the recordset (EOF) or a MovePrevious to the beginning of the recordset (BOF). Failure to test for these conditions means your code works for most cases, but generates an error one day when the last/first record is accessed.
The problem is prevalent when you have another exit condition in mind for your loop, so you are not thinking of EOF. Test for EOF (or BOF if moving backwards) before checking the real exit condition for your loop.
Use this construct for looping through Access recordsets:
Do while Not rst.EOF
If rst![MyField] <> Something Then 'The real loop exit condition.
Exit Do
End If
' Rest of your code here.
rst.MoveNext
Loop
When looping through records, it is easy to create an endless loop by omitting the line rst.MoveNext. Your code then becomes stuck in an endless loop.
VBA is more forgiving than most other languages: just press Ctrl+Break to break out of the loop.
Even in a quick'n'dirty procedure, a progress indicator lets you know if a loop is stuck. The simplest reports every 100th record in the loop like this:
i = i + 1
If i Mod 100 = 0 Then 'Mod gives the remainder of i divided by 100.
DoCmd.Echo True, i
End If
If you use Seek or one of the Find methods (FindFirst, FindLast, FindNext, FindPrevious), and do not test NoMatch, your code will appear to work until you strike a case where the find failed.
Always test for NoMatch after using Seek or a Find method. Example:
rs.FindFirst "City = ""New York"""
If Not rs.NoMatch Then
'It's okay to keep processing.
End If
When inserting a new record into a recordset, the new record does not automatically become the current record.
To access the new record, move to the bookmark LastModified:
rst.AddNew
'Assign fields here.
rst.Update
rst.Bookmark = rst.LastModified
' Work with the new record here.
It is poor programming to open anything without explicitly closing it. This problem is particularly acute in Access 97. Short of pressing Ctrl+Alt+Del, you may find that Access will not quit if recordsets or other objects are not closed and dereferenced.
Always close recordsets and set objects to Nothing in the error recovery of your procedure. Use this construct:
Sub MyProc
On Error Goto Err_MyProc
Dim db as Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("MyTable")
'Useful code here.
rst.Close 'Close what you opened.
Exit_MyProc:
Set rst = Nothing 'Deassign all objects.
Set db = Nothing
Exit Sub
Err_MyProc:
'Error handler here.
Resume Exit_MyProc
End Sub
Access 2007 introduced the possibility that a field in a recordset may itself be a recordset. This applies to complex data types - multi-value fields and attachments - in an ACCDB, not MDB.
Any code that examines the Fields of a recordset or applies criteria is affected. Test the field's Type, and introduce another loop to walk the fields within the recordset that is a field in your main recordset. For a list of the field types, see Field type reference.
Many professional developers avoid the complex data types, believing they introduce more problems than they solve. The hidden structure makes it harder to manage them, harder to apply criteria, harder to pass arguments, harder to determine the delimiters to use for a field, and harder to upsize since other databases like SQL Server don't use these complex types. Eschewing the complex data types is a perfectly value choice if you only deal with databases you created, but if you support end users or write generic utilities to work with any Access tables, you must learn to handle them.
Specifically test for and handle the complex data types if your code must work with databases in Access 2007 or later. Particularly, if you:
The Find-as-you-type utility contains a practical example of testing for and excluding the complex data types in the function FindAsUTypeLoad(), while ensuring the code still works in earlier versions of Access.
| Home | Index of tips | Top |