Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

form-subform based on 4 tables, too complicated? please help...

Discussion in 'Information Technology' started by Sam, Jul 28, 2006.

  1. Sam

    Sam
    Expand Collapse
    Guest

    Hi,

    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
     
  2. Loading...


  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Sam,

    why not just have payments automatically apply to the oldest
    balances instead of letting the customer pick what to apply
    it to? Instead of keeping track of individual invoices for
    payments, why not just track an amount due? Why make it
    more complicated?

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Sam wrote:
    > Hi,
    >
    > 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
    >
     

Share This Page