Posting data from a form to underlying fields

[FJquestioner]

I have a form to record customer payment receipts into a Receipts table
indicating customer, amount, date etc... Within that form is a
subform based on a query that selects all open invoices to that customer
from the Invoices table. I want to be able to allocate the receipt among
those invoices that are being paid (in full or in part) into a Receipts
Allocation table.

Receipts table is joined to Receipts Allocation table by ReceiptsID.

Receipts Allocation table is then joined to the Invoices table by InvoicesID.

So the Receipts Allocation table sits in the middle of the other two and is
joined to both by their respective Primary keys.


So within the Form for entering Receipts I've got a subform in datasheet
view that shows the open invoices to the customer making the payment.
Ideally, beside each of these invoice records I'd like to have fields for
Previous Payments and Outstanding Balance (derived from a query on the
Receipts Allocation table) and Amount Being Allocated and a Checkbox to
indicate whether that payment closes the invoice or not (both fields which
will ultimately be fed into the Receipts Allocation Table).

However, the first time a receipt is received for a given invoice, there is
no previous record of the invoice in the Receipts Allocation table. So when I
run the query for previous payments it returns a null set. And similarly the
only way I can indicate the Amount Being Allocated into the Receipts
Allocation table is if I manually type in the associated InvoiceID first
which is repetitive and prone to error.

It seems to me that in other systems, a temporary holding table is used to
input the receipt allocation info which is then "posted" into the underlying
real table. I'm not sure if this intermediary step is necesary but in any
event I'd still need to somehow "post" the Primary key (InvoiceID) from the
subform of open invoices into a new record in the Receipts Allocation table
so I can then fill in the other fields of that record. [This "posting" issue
will occur several times in my accounting database.]

And the tough part is I know nothing about coding. I'm trying to accomplish
this using macros.

Any assistance or suggestions would be greatly appreciated !

 

[Steve Schapel]

FJ,

See the reply in .macros newsgroup.

Just a hint for the future... If you feel it is important for your
question to appear in more than one newsgroup (in practice this is
seldom necessary), please cross-post (i.e. address the same message
simultaneously to both groups), as against multi-post (i.e. send a copy
of the message separately to each group). Thanks.