How Do I ?

  • Thread starter Thread starter Hank
  • Start date Start date
H

Hank

I have a table "Quotes" and a form for the table Quotes. It contains most of
the same field names for the table "Orders" My question is how do I Accept a
quote and have it create a New Order for the Orders Table? I would like to
use a command button to "Quote Accepted" My Key Fields are QuoteNum and
OrderNum.
Please Advise......
 
I have a table "Quotes" and a form for the table Quotes. It contains most of
the same field names for the table "Orders" My question is how do I Accept a
quote and have it create a New Order for the Orders Table? I would like to
use a command button to "Quote Accepted" My Key Fields are QuoteNum and
OrderNum.
Please Advise......
Click to expand...

Perhaps you should consider a radical alternative. Rather than having two
tables with almost the same fields - a redundant and non-normalized situation,
by some analyses - how about a single table? You could have a "Confirmed"
yes/no field, defaulting to No; just changing it to Yes would turn the record
from a quote to an order. You would use Queries selecting records with
Confirmed = False to enter, edit and manage quotes, and Confirmed = Yes for
Orders.
 
Perhaps you should consider a radical alternative. Rather than having two
tables with almost the same fields - a redundant and non-normalized situation,
by some analyses - how about a single table? You could have a "Confirmed"
yes/no field, defaulting to No; just changing it to Yes would turn the record
from a quote to an order. You would use Queries selecting records with
Confirmed = False to enter, edit and manage quotes, and Confirmed = Yes for
Orders.
Click to expand...

but that would make things too easy!!! That's clearly cheating!!
 
Sounds Simple enough but I fear that much would have to change. This is a
large project and alot of queries etc. would have to be modified. I really
would like to keep quotes and orders seperate and in this situation they
really are 2 seperate situations, not even handled by the same departments
within the co. Please assist...
 
Sounds Simple enough but I fear that much would have to change. This is a
large project and alot of queries etc. would have to be modified. I really
would like to keep quotes and orders seperate and in this situation they
really are 2 seperate situations, not even handled by the same departments
within the co. Please assist...
Click to expand...
Hank

I've done this using an append query.
Append the quote to the orders table the delete the data from the
quote table.

Hope that helps
Rick
 
Sounds Simple enough but I fear that much would have to change. This is a
large project and alot of queries etc. would have to be modified. I really
would like to keep quotes and orders seperate and in this situation they
really are 2 seperate situations, not even handled by the same departments
within the co. Please assist...
Click to expand...

OK, then you'll need to write some non-trivial VBA code to run an Append query
(to append the Quotes record to the Orders table), and then run a Delete query
to delete the record from the quotes table (unless you choose to keep the
original quote for historical purposes). You can create an Append query in the
query design window, based on the quotes table and appending to the orders
table; use a criterion on QuoteNum like

=[Forms]![YourFormName]![QuoteNum]

to include only the one record currently displayed on the screen. The code in
the Click event of your Accept button would execute this query. The exact
details will depend on your database structure, so I'm a bit hesitant to
suggest specific code. In addition, this is a rather risky thing to test on a
production database - you could make a small mistake which would append ALL of
the quotes making them into orders, or append the wrong quote, or worse delete
all your quotes! Do your testing in a "scratch" copy of the database.
 
Okay John, Sounds like I can pull this off without destroying the entire db.
I will take your advise and try in a copy. Thank you for the assistance.

Hank

John W. Vinson said:
Sounds Simple enough but I fear that much would have to change. This is a
large project and alot of queries etc. would have to be modified. I really
would like to keep quotes and orders seperate and in this situation they
really are 2 seperate situations, not even handled by the same departments
within the co. Please assist...
Click to expand...

OK, then you'll need to write some non-trivial VBA code to run an Append query
(to append the Quotes record to the Orders table), and then run a Delete query
to delete the record from the quotes table (unless you choose to keep the
original quote for historical purposes). You can create an Append query in the
query design window, based on the quotes table and appending to the orders
table; use a criterion on QuoteNum like

=[Forms]![YourFormName]![QuoteNum]

to include only the one record currently displayed on the screen. The code in
the Click event of your Accept button would execute this query. The exact
details will depend on your database structure, so I'm a bit hesitant to
suggest specific code. In addition, this is a rather risky thing to test on a
production database - you could make a small mistake which would append ALL of
the quotes making them into orders, or append the wrong quote, or worse delete
all your quotes! Do your testing in a "scratch" copy of the database.
Click to expand...
 
Back
Top