Edit data from one table and store it into a different one

[YFH]

I have a database that keeps track of my company's orders. The main
"order" table in the database includes fields like Order ID, Customer
ID, Shipment Dates, etc., etc. I'm creating a "contract" form based on
the order table. My problem is - some of the data included in the
contract form (e.g., shipment dates) is based on the shipment dates
field in the order table but may need to be changed by the user at the
time of the contract. The data in the order table should not be changed
but the updates made in the contract need to be stored in another table
so that reports can later be printed based on the updated values. In
other words, the data retrieved from the order table is simply a
"suggestion."

Any ideas as to how I can do this? Thank you.

 

[Guest]

Hi,

I think you have already realised you need two tables - Order and Contract
- with a zero to many relationship between them; that is each Order can have
zero to many Contracts. Once the Contract record exists on the table
everything is straightforward; I believe your question relates to how you
first create a related Contract record.

I can think of two basic strategies and which you use depends upon your
exact circumstances. The first is to copy all pertinent information from the
Order table into the Contract table before you display the Contract form for
the first time. The second is to determine when the Contract form is
displaying a new record and then look up all the individual values from the
Order table displaying them in the Contract form and allowing the user to
overwrite them as appropriate.

In both cases your Contract form is bound to the Contract table. In both
cases you will probably need to do some programming.

Use of the Default Value property is a possibility. Something along the
lines of:

=DLOOKUP("ShipmentDate","Order","OrderId = " & myvalue)

should work but you may find it difficult to identify a reliable source for
myvalue.

Regards,

Rod

 

[Guest]

I have a database that keeps track of my company's orders. The main
"order" table in the database includes fields like Order ID, Customer
ID, Shipment Dates, etc., etc. I'm creating a "contract" form based on
the order table. My problem is - some of the data included in the
contract form (e.g., shipment dates) is based on the shipment dates
field in the order table but may need to be changed by the user at the
time of the contract. The data in the order table should not be changed
but the updates made in the contract need to be stored in another table
so that reports can later be printed based on the updated values. In
other words, the data retrieved from the order table is simply a
"suggestion."

Any ideas as to how I can do this? Thank you.