Complex problem with subform?

Hi

I have a form that allows users to enter in a payment transaction. There is
a subform that should bring up the outstanding invoices when a user selects a
debtor, via a combo box. The subform is based on a query.

The main form includes the following fields:
TransactionNo = autonumber field
DebtorNo = is a combo box so users can choose a debtor
TransactionDate = date of payment
AmountPaid = total amount of payment
Unallocated = this is a calculated field, more of a check to ensure that the
amount in AmountPaid has been allocated to all invoices/credit notes. The
calc is AmountPaid - SumCurrentPayment (see below).

The subform contains the following fields:
TransactionRef = Invoice or Credit Note number
TransactionType = whether invoice or credit note
TransactionDate = Date of invoice or credit note
TransactionAmount = Amount of invoice or credit note
OutstandingAmt = amount still outstanding on the invoice/credit note
CurrentPayment = field for user to enter current amount to be paid against
that invoice or credit note
SumCurrentPayment = Hidden field totalling up the current payments.

I have 3 issues with this form.

1. The subform does not display/refresh/requery when the debtor number is
selected. I tried putting in Me![subformname].Requery in the AfterUpdate
event of the DebtorNo combo box, but this didn't work, ie. still not showing
any transactions when the debtor was selected.

2. A debtor can pay on two separate receipt transactions, but when that
happens, the subform shows two instances of the same outstanding
invoice/credit note record. I tried tweaking the underlying query to use
DISTINCT, but then the recordset became non updateable. I also tried putting
in the criteria of only showing inv/cn transactions with OutstandingAmt = 0,
but then I couldn't view past transactions because they had an amount in the
OutstandingAmt field and therefore weren't being shown.

3. Because of the above, the Unallocated field is showing the wrong amount
on those debtors that have two payment transactions. The subform will show
up all invoices, including the one/s that have been paid on a previous
receipt transaction, but then the sum of course will total all the payments
made, thereby throwing out the Unallocated because it is only calculating on
the current payment. For example: Debtor 1 has two invoices outstanding.
They send a cheque to pay one invoice (say $100) and this gets entered as
receipt transaction 1. The subform will show both outstanding invoices, the
user will enter in $100 against the first invoice and the Unallocated field
should show zero. Debtor 1 then sends another cheque to pay the second
invoice and this gets entered as receipt transaction 2. The subform still
shows both outstanding invoices, and there will already be an amount entered
in the CurrentPayment field for the first invoice. Here is where the problem
lies. Because there is already an amount, the SumCurrentPayment field will
show $100, and therefore the Unallocated field will show -$100 before any
amount paid is entered.

I hope I have explained my issues clearly, and hope that someone can help.
Sorry for the long windedness, but it's a bit of a complex problem.

Alternatively, if there is another way you can suggest for me to handle
receipt transactions, I would be very grateful!!!!

Regards