I have a quoting database.. alot of times we have quotes the are duplicates for same customer.. same pricing etc.. only thing changing is of course the quote number & the date created... I have Tables set up as below.
QuoteMain has one to many relationship with SubQuote & QuotePricing & QuoteLineItems & QuoteLog
For duplicating i need to get the following
QuoteMain, SubQuote,Quotepricing, Quotelineitems.
The shipment log wouldnt be duplicated..
so behind a command button i call function.. So far i have the below... Im stuck on how to create the new records in each of the 4 tables.. What is the correct way to do this? I cant use Form_Main.FieldName=Rs.Fields.FieldName because they arent all on the same form and some of the forms are closed...
Let me know..
Here is what i have..
Private Function DuplicateQuote(WhichID As Long)
On Error GoTo ErrorHandler
Dim IdToCopy As Long
Dim TmpSQL As String
Dim DB As Database
Dim RS As Recordset
IdToCopy = WhichID
TmpSQL = "SELECT SubQuote.TYPEOFQUOTE, SubQuote.Quantity, SubQuote.CargoType," & _
"QuoteLineItems.PricingID, QuoteLineItems.ItemQty, QuoteLineItems.ItemCode, QuoteLineItems.ItemDescription, QuoteLineItems.ItemCost, QuoteLineItems.ItemSold," & _
"QuoteLineItems.Brokerage, QuoteLineItems.PricingNotes, QuoteLineItems.ItemTotalSell, QuoteLineItems.ItemTotalCost, QuoteLineItems.Profit, QuoteLineItems.DateCreated," & _
"QuoteLineItems.PrimaryCarrier, QuoteLineItems.SecondaryCarrier, QuoteLineItems.Exclude, QuotePricing.PricingTotalSell, QuotePricing.PricingTotalCost, QuotePricing.PricingProfit," & _
"QuotePricing.TypeLabel, QuotePricing.DateCreated, SubQuote.CargoDescription, SubQuote.QuoteLen, SubQuote.QuoteWid, SubQuote.QuoteHgt, SubQuote.QuoteWgt, SubQuote.SubNotes," & _
"SubQuote.Datecreated, SubQuote.QuoteCuft, SubQuote.Exclude, QuoteMain.DateCreated, QuoteMain.DateOfQuote, QuoteMain.CustName, QuoteMain.CustAddress, QuoteMain.CustAddress2," & _
"QuoteMain.CustEmail, QuoteMain.CustPhone1 , QuoteMain.CustPhone2, QuoteMain.CustPhone3, QuoteMain.CustPhoneType1, QuoteMain.CustPhoneType2, QuoteMain.CustPhoneType3," & _
"QuoteMain.AaridServiceType, QuoteMain.CarrierServiceType, QuoteMain.DoorOrigin, QuoteMain.OriginPort, QuoteMain.DestinationPort, QuoteMain.OnCarriagePoint, QuoteMain.QuoteType," & _
"QuoteMain.Awarded, QuoteMain.Status, QuoteMain.Invoiced, QuoteMain.QuoteComments, QuoteMain.RequestComments, QuoteMain.QuoteManager, QuoteMain.AECREF" & _
" FROM ((QuoteMain INNER JOIN QuotePricing ON QuoteMain.ID = QuotePricing.QuoteID) INNER JOIN QuoteLineItems ON QuotePricing.ID = QuoteLineItems.PricingID) INNER JOIN SubQuote ON QuoteMain.ID = SubQuote.QuoteID" & _
" WHERE (((QuoteMain.ID)=" & IdToCopy & ") AND ((SubQuote.QuoteID)=" & IdToCopy & ") AND ((QuotePricing.QuoteID)=" & IdToCopy & ") AND ((QuoteLineItems.QuoteID)=" & IdToCopy & "));"
' Debug.Print TmpSQL
Set DB = CurrentDb
Set RS = DB.OpenRecordset(TmpSQL)
"Here is where i need to take all the data above and create the records in each table..."
RS.Close
Set RS = Nothing
ExitRoutine:
Exit Function
ErrorHandler:
MsgBox Err.Description
Resume ExitRoutine
End Function
QuoteMain has one to many relationship with SubQuote & QuotePricing & QuoteLineItems & QuoteLog
For duplicating i need to get the following
QuoteMain, SubQuote,Quotepricing, Quotelineitems.
The shipment log wouldnt be duplicated..
so behind a command button i call function.. So far i have the below... Im stuck on how to create the new records in each of the 4 tables.. What is the correct way to do this? I cant use Form_Main.FieldName=Rs.Fields.FieldName because they arent all on the same form and some of the forms are closed...
Let me know..
Here is what i have..
Private Function DuplicateQuote(WhichID As Long)
On Error GoTo ErrorHandler
Dim IdToCopy As Long
Dim TmpSQL As String
Dim DB As Database
Dim RS As Recordset
IdToCopy = WhichID
TmpSQL = "SELECT SubQuote.TYPEOFQUOTE, SubQuote.Quantity, SubQuote.CargoType," & _
"QuoteLineItems.PricingID, QuoteLineItems.ItemQty, QuoteLineItems.ItemCode, QuoteLineItems.ItemDescription, QuoteLineItems.ItemCost, QuoteLineItems.ItemSold," & _
"QuoteLineItems.Brokerage, QuoteLineItems.PricingNotes, QuoteLineItems.ItemTotalSell, QuoteLineItems.ItemTotalCost, QuoteLineItems.Profit, QuoteLineItems.DateCreated," & _
"QuoteLineItems.PrimaryCarrier, QuoteLineItems.SecondaryCarrier, QuoteLineItems.Exclude, QuotePricing.PricingTotalSell, QuotePricing.PricingTotalCost, QuotePricing.PricingProfit," & _
"QuotePricing.TypeLabel, QuotePricing.DateCreated, SubQuote.CargoDescription, SubQuote.QuoteLen, SubQuote.QuoteWid, SubQuote.QuoteHgt, SubQuote.QuoteWgt, SubQuote.SubNotes," & _
"SubQuote.Datecreated, SubQuote.QuoteCuft, SubQuote.Exclude, QuoteMain.DateCreated, QuoteMain.DateOfQuote, QuoteMain.CustName, QuoteMain.CustAddress, QuoteMain.CustAddress2," & _
"QuoteMain.CustEmail, QuoteMain.CustPhone1 , QuoteMain.CustPhone2, QuoteMain.CustPhone3, QuoteMain.CustPhoneType1, QuoteMain.CustPhoneType2, QuoteMain.CustPhoneType3," & _
"QuoteMain.AaridServiceType, QuoteMain.CarrierServiceType, QuoteMain.DoorOrigin, QuoteMain.OriginPort, QuoteMain.DestinationPort, QuoteMain.OnCarriagePoint, QuoteMain.QuoteType," & _
"QuoteMain.Awarded, QuoteMain.Status, QuoteMain.Invoiced, QuoteMain.QuoteComments, QuoteMain.RequestComments, QuoteMain.QuoteManager, QuoteMain.AECREF" & _
" FROM ((QuoteMain INNER JOIN QuotePricing ON QuoteMain.ID = QuotePricing.QuoteID) INNER JOIN QuoteLineItems ON QuotePricing.ID = QuoteLineItems.PricingID) INNER JOIN SubQuote ON QuoteMain.ID = SubQuote.QuoteID" & _
" WHERE (((QuoteMain.ID)=" & IdToCopy & ") AND ((SubQuote.QuoteID)=" & IdToCopy & ") AND ((QuotePricing.QuoteID)=" & IdToCopy & ") AND ((QuoteLineItems.QuoteID)=" & IdToCopy & "));"
' Debug.Print TmpSQL
Set DB = CurrentDb
Set RS = DB.OpenRecordset(TmpSQL)
"Here is where i need to take all the data above and create the records in each table..."
RS.Close
Set RS = Nothing
ExitRoutine:
Exit Function
ErrorHandler:
MsgBox Err.Description
Resume ExitRoutine
End Function
