You can execute an Append query statement to copy the data from one table to
another.
You will need tables like this:
a) Client table (one record for each person/company you deal with), with a
ClientID primary key
b) Product table (one record for each thing you sell/supply) with a
ProductID primary key.
c) Enquiry table (one record for each enquiry/quote), with an EnquiryID
primary key, ClientID, and EnquiryDate.
- EnquiryID primary key
- ClientID who placed this order
- EnquiryDate Date/Time
d) EnquiryDetail table (the line items of the enquiry), with fields like
this:
- EnquiryDetailID primary key
- EnquiryID which enquiry this row belongs to
- ProductID what product the client enquired about
- Quantity how many of this product they wanted
- PriceEach the unit price you quoted
The point of table (d) is that a client could enquire about many products at
the same time. By creating a one-to-many relation between (c) and (d), an
enquiry can have as many line items as you need (just one row if they asked
about one product, or many rows if they asked about many.)
e) Orders table (one record for each order a client places with you) with
fields like this:
- OrderID primary key
- ClientID who placed this order
- OrderDate Date/Time
- EnquiryID the enquiry this order came from (blank if there was none.)
f) OrderDetail table (the line items of the order), with fields like this:
- OrderDetailID primary key
- OrderID which order this row belongs to
- ProductID what product the client ordered
- Quantity how many of this product they ordered
- PriceEach unit price
Since table (e) has a similar structure to (c), and (f) is similar to (d),
you can put a command button on your Enquiry form to turn the enquiry into
an order. This button will need to execute some VBA code that adds the
records to the other tables.
You need the OrderID of the newly created order so you can use it in the
OrderID field or the OrderDetail table. The simplest way to do this would be
to OpenRecordset() and AddNew with Update. Then get the new OrderID and use
it in the SQL statement that appends the related records to the OrderDetail
table.
Writing that code for you is beyond a newsgroup post, but here's an example
of some code that uses AddNew followed by an Append query execute:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html
Your code may want to do other checking as well (e.g. to warn if the enquiry
has already been turned into an order.)
