copy quote to quote

[steve111]

hi,

with help from a member I can now copy a quote form and subfrom to an order. this works great

I have been ask if I can now copy a quote to a new quote, also the subform

I have tried to modify the macro to suit what the other member did for me but it is not working correctly
when I copy it the subfrom details as doubling up every time in the new quote records it creates

I have attached the sample database
record 3 has 2 records in it . if you copy that the new record will then have 4

thanks
steve

 

[gemma-the-husky]

the easiest way is to consider the data.

what you need to do is take the datatable you want to copy, and get the id of the record you are copying.

then do an append query to insert a copy of this data under the next/new ID

then reposition of requery etc, to get the new item visible.

of course if you have any unique keys on combinations of fields in the table, this may not work - because you probably do not really want an exact copy of the item

 

[steve111]

hi,

the copy of the main form works ok to the new quote. this transfers ok.
It then gets the new quoteID ok and puts it into the append query. but this is doubling up in my new quote that's the bit I am struggling with

this is the part of code that gets the new quote id

tempvars.Add "quoteID", Val(!QuoteID)

and this is in the append query

QuoteID: [TempVars]![QuoteID]

steve

 

[steve111]

hi ,

this is the code that I have tried to modify and sql I have but like I mentioned the subform details keep doubling up and I don't know why

[Private Sub Command112_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form

' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = dbs.OpenRecordset("Qoute")

'On Error GoTo Err_cmdcommand112_Click

'Add new record to end of Recordset object.
With Rst
.AddNew
!CustomerName = Me!CustomerName
!Received = Received
!Notes = Me!Notes
!exchangerate = Me!exchangerate
!contractNo = Me!contractNo
!Date1 = Me!Date1
![Sales person] = Me![Sales person]
!intcoterms = Me!intcoterms
![scheduled date] = Me![scheduled date]
!Terms = Me!Terms

' save
' the new Quote ID for use in append query

tempvars.Add "quoteID", Val(!QuoteID)



.Update ' Save changes.

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendQuotesDetailsTonewquote"
DoCmd.SetWarnings True

Exit_cmdcommand112_Click:
Exit Sub
Err_cmdcommand112_Click:
MsgBox Error$
Resume Exit_cmdcommand112_Click:
End Sub/QUOTE]


the sql is

INSERT INTO [Quote Details] ( Amended, materialid, stonetype, OrderQty, [sell Price], Required, Notes, [Currency], shipped, [Schedule date], orderno2, Discount, ItemDescription, QuoteID1 )
SELECT [Quote Details].Amended, [Quote Details].materialid, [Quote Details].stonetype, [Quote Details].OrderQty, [Quote Details].[sell Price], [Quote Details].Required, [Quote Details].Notes, [Quote Details].Currency, [Quote Details].shipped, [Quote Details].[Schedule date], [Quote Details].orderno2, [Quote Details].Discount, [Quote Details].ItemDescription, [TempVars]![QuoteID] AS QuoteID
FROM [Quote Details];

I still get double the records every time in copy it to a new quote in my subfrom

any help appreciated

steve

 

[HiTechCoach]

Steve,

I was the one that help you with the previous post (Click Here).

It looks like the issue is in the query "qryAppendQuotesDetailsTonewquote".

TIP: Use the "go Advanced" to switch modes. Now the toolbar has the # for CODE tags.

INSERT INTO [Quote Details] ( Amended, materialid, stonetype, OrderQty, [sell Price], Required, Notes, [Currency], shipped, [Schedule date], orderno2, Discount, ItemDescription, 

QuoteID1 )  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


SELECT [Quote Details].Amended, [Quote Details].materialid, [Quote Details].stonetype, [Quote Details].OrderQty, [Quote Details].[sell Price], [Quote Details].Required, [Quote Details].Notes, [Quote Details].Currency, [Quote Details].shipped, [Quote Details].[Schedule date], [Quote Details].orderno2, [Quote Details].Discount, [Quote Details].ItemDescription, 


[TempVars]![QuoteID] AS QuoteID  <<<<<<<<<<<<<<<<<<<


FROM [Quote Details];

You are naming the [TempVars]![QuoteID] AS QuoteID but you are append the value of QuoteID1 which is the original quite ID.

Try This:

INSERT INTO [Quote Details] ( Amended, materialid, stonetype, OrderQty, [sell Price], Required, Notes, [Currency], shipped, [Schedule date], orderno2, Discount, ItemDescription, 

NewQuoteID ) 

SELECT [Quote Details].Amended, [Quote Details].materialid, [Quote Details].stonetype, [Quote Details].OrderQty, [Quote Details].[sell Price], [Quote Details].Required, [Quote Details].Notes, [Quote Details].Currency, [Quote Details].shipped, [Quote Details].[Schedule date], [Quote Details].orderno2, [Quote Details].Discount, [Quote Details].ItemDescription, 

[TempVars]![QuoteID] AS NewQuoteID  

FROM [Quote Details];

 

[steve111]

hi ,

thanks for your help

I cannot get it working


I have attached the file for you to see what I am doing wrong

thanks
steve

 

[HiTechCoach]

I had to add a where clause to the append query. I also added an additional TempVar to deal with the navigation form. There were a few issues in the VBA code I had to fix.

EDIT: replaced attachment

 

[steve111]

hi,


thanks or trying to help me .

2 things

I cannot see where I can copy quote to quote
when I copy quote to order it is doubling up the order details
thanks
steve

 

[HiTechCoach]

hi,


thanks or trying to help me .

2 things

I cannot see where I can copy quote to quote

I only updated the VBA code in the command button you already had labeled "Create quote from Quote".

when I copy quote to order it is doubling up the order details
thanks
steve

Worked for me just fine. There is only a single quote with two lines that look like duplicates. That is what yu posted. The order ended up with same two lines.

Did you try creating a new quite and then create an order?

 

[steve111]

hi

thanks
the database that you sent back does not have any button on saying quote to quote
that why I am confused
could you please resend me one with both buttons on if it is not to much trouble

steve

 

[HiTechCoach]

My Bad. I posted the wrong one. I replaced the attachment in Post #7. Try that one.

 

[steve111]

hi ,

thank yo for all your help it work great really appreciated

steve

 

[HiTechCoach]

Steve,

Thanks for the update. Glad that worked for you.