Create a subform to track charges, payments & balance

[DebbZi]

I would like to create a subform that works like a ledger or spreadsheet ie
calculates a balance by adding charges and subtracting payments. I want it in
table form with a field/column for date, description of service, check #,
charge, payment & balance. Charges would be added, payments subtracted and a
new balace calculated on each line. For example: balance from Line1 plus
charges from Line2 minus payments from Line2 = balance for Line2...etc. Each
clients subform would reflect that client's history of charges & payments.
Is this possible?? I'm a novice at Access.

 

[Ed Robichaud]

Very briefly, you'll need a table for clients:

ClientID
Lastname
StartBalance
etc.

and a table for payments:

PaymentID
ClientID (foreign key - link to client table)
Date
Payment
Charge
etc.

The typical setup is to have a main form (single record type) based on the
client table, and a subform (either continuous or datasheet type) based on
the payments table. Set the parent-child relationship to "clientID" on both
main and subform. (Use the subform wizard if this is all new stuff for
you).

Your subform will show only those payment related to the clientID in the
main form. Controls such as balance are calculated (not stored anywhere).
You would also need some code in the AfterUpdate event of any payment to
recalculate the current balance.

This seems a little ambitious for a novice, but look at the sample invoice
template that ships with Access for some help and pre-done examples.

-Ed

 

[DebbZi]

Thanks Ed, It may well be too ambitious for me but I'm going to work on it.
I do have a form based on my clint table and have done ok following pre-done
temmplates. Which template do you think would best suit my needs? I haven't
found an invoice that looked like it would work.