Help with a Form design

[Joe Cilinceon]

I want to add a form for posting payments. This is for a storage facility
where 1 tenant can have more than one space. Below is what I was looking
for:

Check box (for marking those units he is paying at this time as he could
have several and may not pay all at the same time.)
Unit number
Rent owed
fees owed
balance due

The above will be on one line per unit. Now once a unit is checked to pay it
will show the totals at the bottom along with the amount paid, payment
method (Cash, Check, Credit Card, etc)

Once it is done I will need it to post the payments to the Tenant's ledger.
The above screen will be based on a Balance Due Query. Now the real problem
(for me at least is figuring a way to have a lines for each unit rented that
will show the current balances. There might be just one line or several and
I don't want to show anything lines for each unit.. Any help or point to a
method of doing this would be greatly appreciated.

 

[George Nicholson]

Hard to answer how your Form should work without knowing your data
structure. Something like this is what I would suggest:

Payments (one-to-many to Tenant table)
-TenantID
-PaymentID (PK)
-PaymentDate
-PaymentAmount
-PaymentMethod

UnitsFees (one to many to Unit table)
-UnitID
-FeeID (PK)
-FeePeriod (MonthYear)
-DueDate
-Fee
(Combination of UnitID & FeePeriod is unique, so maybe use as FeeID?)

PaymentApplyTos (many-to-many)
-PaymentID
-FeeID
-FeePaid (this is a specified portion of the PaymentAmount for the related
PaymentID)
Note that this structure allows for partial payments: any given fee can have
multiple payments received.

Monthly or Annually (?): use Append query to create a new set of UnitFees
that will become due during period (I assume prepayments would dictate
generation of this far into the future).
RecordPayment: when payment recieved, record which fees the payment applies
to.
BalanceDue: calculated field in query or form, not stored in a table

--
HTH (Sorry if this doesn't answer your question)

George Nicholson
(Please post responses to newsgroup but remove "Junk" from return address if
used)

 

[Joe Cilinceon]

Thanks that gives me a start.