Asking too much?? Form-subform based on 4 tables, one is a junction table.

[Sam]

I have a problem with creating a form from tables involving a junction
table. What I learned from this group is: Create a form based on one
one-table and a subform based one many-table, and have the combobox in
the subform be based on query on the-other one-table.

My situation:

tblCustomer one-many tblSales
tblSales one-many tblReceipt_Allocation
tblReceipt one-many tblReceipt_Allocation

One receipt from a customer can be used to pay more than one invoice.
And one invoice can be paid by more than one payment/receipt.

I have created a form frmReceipt (recordsource: tblReceipt) and a
subform fsubReceipt (recordsource: tblReceipt_Allocation).

In the subform, a combobox is used to pick out invoices that will be
paid. Then the user will enter the payment amount for each invoice,
manually splitting the payment amount (tblReceipt doesn't have a field
for total receipt amount).

Currently this combobox is not filtered by customer. Instead, it will
list all open/not fully paid sales. So, a payment can pay for sales of
more than one customer, which isn't right.

What I would like the user be able to do when receiving a payment is:
Open a form, pick a customer, enter payment info (date etc.), pick from
a list of open invoices, allocate the payment to one or more invoices.

Could somebody please help. Thank you in advance.

-Sam

 

[Sam]

No replies? Anybody? Suggestion? Comment?

-Sam

 

[Larry Linson]

Basically, you need a Subform for tblReceiptAllocation, in which you select
a Sale using a Combo Box and enter an Amount/Percentage to allocate. The
Sale Combo Box should have Customer as a criteria in its Row Source.

Perhaps someone else will jump in with some details.

Larry Linson
Microsoft Access MVP