Select and updates records based on previous record selections

[pcslave via AccessMonster.com]

Have 2 tables:
INVOICE DATA: consisting of Invoice Number (key, autonumber generation),
customer number, sales order number, amount, sales order date, invoice date,
status, comments
PAYMENT DATA: consisting of Invoice Number, customer number, sales order
number, check number, pay date, pay amount.
Relationship defined for two tables on Invoice Number (one-to-many)

When enter data in INVOICE DATA, customer may prepay all or some of what is
due, or no payment is received (depends on customer type).

Want to be able to select customer number, then select Sales order number and
obtain listing of data from PAYMENT DATA table if total of payments < amount.
From this resulting list, need to be able to edit any entries already made
(say for example , pay amount incorrectly entered) and add any new payment
entry data.

Part of problem is that there may not be corresponding entries in PAYMENT
DATA for all entries in INVOICE DATA. Another part of my problem is that any
records from PAYMENT DATA that exist are not updateable.
Hopefully, this explains what I want and what I need. I figure there is some
simple thing I am overlooking, but have spent quite of few days
"experimenting" with various queries and forms, plus have scanned this great
site for similar topics. Help would be much appreciated.

BTW, would like to do all of this via forms for ease in data entry and
correction, plus have 2+ years worth of data from older dbase process
(ALPHA4V6) which is to be imported and preserved.

 

[kingston via AccessMonster.com]

1) Create a form for PAYMENT DATA.
2) Create a form for INVOICE DATA.
3) In design mode for the INVOICE DATA form, drag and drop the PAYMENT DATA
form into the body of the INVOICE DATA form. Indicate Invoice Number as the
linked field.

Now you can use the built-in function Records -> Filter By Form to select a
customer and sales order in the main form. Or you can use the right-click
menu to perform the filter on the customer and sales order controls. Make
sure this works before you try to do something more sophisticated.

Next, you can add controls in the main form to perform the filters that you
want and you can change the data source for the main form from a table to a
query. So for example, if you add two combo boxes in the main form for
customer and sales order, the query would return records from the table with
those two parameters and your condition "total of payments < amount". HTH

 

[pcslave via AccessMonster.com]

Thank you very much for all the assistance. I'm sorry I haven't responded
sooner, but I've been busy. The solution you provided worked just great.
Again, Thanks!!!!

1) Create a form for PAYMENT DATA.
2) Create a form for INVOICE DATA.
3) In design mode for the INVOICE DATA form, drag and drop the PAYMENT DATA
form into the body of the INVOICE DATA form. Indicate Invoice Number as the
linked field.

Now you can use the built-in function Records -> Filter By Form to select a
customer and sales order in the main form. Or you can use the right-click
menu to perform the filter on the customer and sales order controls. Make
sure this works before you try to do something more sophisticated.

Next, you can add controls in the main form to perform the filters that you
want and you can change the data source for the main form from a table to a
query. So for example, if you add two combo boxes in the main form for
customer and sales order, the query would return records from the table with
those two parameters and your condition "total of payments < amount". HTH

Have 2 tables:
INVOICE DATA: consisting of Invoice Number (key, autonumber generation),

[quoted text clipped - 24 lines]

correction, plus have 2+ years worth of data from older dbase process
(ALPHA4V6) which is to be imported and preserved.