Copy from Quote form to Order Form

[Hulm1]

I have developed a quotation system which includes items, rates, discounts, quantities and calculated prices.

When I get an order from a client, I would like to simply press a button to copy the whole quote down to an Order Confirmation form which can be a) sent to the client and b) used as the budget for job costing.

A similar button would be really good to copy the quote down to a new quote if I need to make a revision. This would avoid either re-doing the whole quote or changing the existing one.

I would appreciate someones advice
Thank you

 

[Remou]

You can use append queries, for example, code behind a button on the quotation form might be:

If MsgBox("Copy to Order?", vbYesNo) = vbYes Then
   strSQL= "INSERT INTO Order (QuoteID, Quantity, Price) " _
         & "SELECT ID, Quantity, Price FROM Quote " _
         & "WHERE ID=" & Me.ID
   CurrentDB.Execute strSQL, dbFailOnError
End If[code]

Something very similar can be used to duplicate a quotation. You can also ask the user for additional information on an extra form and use that in the query, if you wish.


[URL unfurl="true"]http://lessthandot.com[/URL]

 

[Hulm1]

Hmm yes, I found your thread from 2005 on this as well. Thanks for this. I am looking at that and some examples by PHV which were appended to the same thread.

Could I simultaneously open a form based on the table that the records are appended to? Ideally, I would click a button which copied down the records and opened the form. I would then enter the client's Order Number and print it.

Can you advise on that?

Just as an aside, many of these records are on a continuous form which is a subform of the Quotation Form. As you can imagine, a quotation includes some or many items. Ideally, I would pass all those records to a subform of the Order Confirmation as well. I fear that is too complicated though, So my feeling is to simply have some summary fields on the Main Quotation form (taken from the subform) and copy these down to a simpler Order Confirmation,

 

[Remou]

It is not too complicated, you can run two append queries. You should be able to use the same ID, because the usual design of a table/detail table includes an ID common to both. It is probably simpler in the end to use Append queries than trying to open forms before adding the records. After the records have been added, it is simple to open the form to the relevant record using arguments of the OpenForm method of DoCmd.

http://lessthandot.com

 

[Hulm1]

OK a ran a simple test and the append works great for numbers. I can see that text fields and dates are a bit more complicated.

However, is there a way to pass the results of an expression to another table as a number?

In other words
Textboxname = costcode1*costcode2
The result will be a calculated value of course.

Herewith my code:

Strsql = "INSERT INTO tblOrderConfirmation " _
& "( [Costcode1], [Costcode2],[Costcode3] ) " _
& "SELECT '" & Me.Costcode1 _
& "', " & Me.Costcode2 _
& "', " & Me.Total
DoCmd.RunSQL Strsql

 

[Remou]

Yes. Use the Value keyword, rather than Select. Select is only for getting data from another table, so:

Strsql = "INSERT INTO tblOrderConfirmation " _
  & "( [Costcode1], [Costcode2],[Costcode3] ) " _
  & "VALUES ('" & Me.Costcode1 _
  & "', " & Me.Costcode2 _
  & "', " & Me.Total & ")"

Note that you need brackets for Value.


Regarding problems with numbers and dates, it works like so:

"INSERT INTO MyTable (Text, Number, Date ) " _ 
& "VALUES ('" & "Text" & "," & 100 & ",#" _
& "2010/12/31" & "#)"

"INSERT INTO MyTable (Text, Number, Date ) " _ 
& "VALUES ('" & Replace(Me.Text,"'","''") _
& "," & 100 & ",#" _
& Format(Date(),"yyyy/mm/dd") & "#)"

That is, dates are delimited with hash (#) and best in year, month, day format; text is delimited with quotes, generally single quotes, but watch out for text that contains quotes and replace them with two single quotes.

http://lessthandot.com

 

[Hulm1]

OK Thanks. I get a run-time error '3075'
Syntax error in string in query expression '400', 800)'.

Note that 400 is costcode2 and that 800 is the Total (calculated value).

Debug highlights:

DoCmd.RunSQL Strsql

 

[PHV]

Strsql = "INSERT INTO tblOrderConfirmation " _
  & "(Costcode1,Costcode2,Costcode3) " _
  & "VALUES ('" & Me!Costcode1 _
  & "'," & Me!Costcode2 _
  & [!]","[/!] & Me!Total & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hulm1]

That worked great. Then I realised there would be a duplication risk.

I just added another field called QuotationID in the Order Confirmation table and then revised the code (see below). I did this so I could index it and prevent duplicate order confirmations being created by hitting the button over and over.

However, it gets hung up on the Docmd line again

Strsql = "INSERT INTO tblOrderConfirmation " _
& "( [Costcode1], [Costcode2],[Costcode3], [QuotationID] ) " _
& "VALUES ('" & Me.Costcode1 _
& "', " & Me.Costcode2 _
& "', " & Me.QuotationID _
& ", " & Me.Total & ")"

DoCmd.RunSQL Strsql

Any ideas again?!

 

[PHV]

Strsql = "INSERT INTO tblOrderConfirmation " _
& "( [Costcode1], [Costcode2], [QuotationID],[Costcode3] ) " _
& "VALUES ('" & Me.Costcode1 _
& "', " & Me.Costcode2 _
& ", " & Me.QuotationID _
& ", " & Me.Total & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hulm1]

Luvvly Jubbly
That worked! Thanks
Could you please explain why the change worked? I see what you did, but I figured that because it was a number, it would the same as the Costcodes

 

[Remou]

Look at the order of the fields and values, they must be the same.

http://lessthandot.com