Renew Multiple records via a form

[Phil Knapton]

Thanks for that. I'll try it and let you know how I get on.


My date field is called [Quote Date] and my quantity field is called [QLI Quantity] so I shouldn't have a problem with reserved names.
Thanks again.

[RuralGuy]

Excellent! Let us know how you get on.

[Phil Knapton]

I Have run the code and got the following message
Error 3061
Description: - Too Few Parameters. Expected 2.

I have edited the field name in the code you supplied (Below) Have I omitted something?
The tblQuote has updated but the tblQuoteLineItem has not.

Private Sub Command121_Click()
'-- Renew the current Quote by duplicating the existing records...
'-- but giving the new records a fresh QuoteID of their own.
On Error GoTo Err_Command121_Click
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MySQL As String
Dim MyQuoteID As Long
Dim NewQuoteID As Long
Set db = CurrentDb
MyQuoteID = [QuoteID] '-- Directly from the Current Record of the MainForm
'-- Append a new record to tblQuote table with *almost* all of the same values as the current record
'-- We let the table create a new AutoNumber [Quote ID] and use the current DATE
'-- BTW, DATE is a RESERVED word and should *not* be used as a FieldName
MySQL = "INSERT INTO tblQuote ( [Clientcontactid], [Userid], [companyid], [Siteid] , " & _
"[quote Date], [quote status], [quote intro text], [quote exit text]) " & _
"SELECT [Clientcontactid], [Userid], [companyid], [Siteid] , [quote Date], " & _
"[quote status], [quote intro text], [quote exit text] FROM tblQuote " & _
"WHERE [QuoteID] = " & MyQuoteID & ";"
db.Execute MySQL, dbFailOnError
'-- Get the *new* QuoteID
Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
NewQuoteID = rs!LastID
rs.Close
Set rs = Nothing
'-- Now we need to duplicate any LineItems associated with the previous quote
MySQL = "INSERT INTO tblquotelineitem ( [quoteid], [ProdTypeid], [QLI Description] , [QLI qantity], [QLI Cost]) " & _
"SELECT NewQuoteID,[ProdTypeid], [QLI Description] , [QLI qantity], [QLI Cost] FROM tblquotelineitem " & _
"WHERE [quoteid] = " & MyQuoteID & ";"
db.Execute MySQL, dbFailOnError
'-- You will still need to move your MainForm to the newly created quote
'-- You can do this with a FindFirst
'-- The SubForm should follow along with the MainForm
Exit_Command121_Click:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
Err_Command121_Click:
MsgBox "Error Number: " & Err.Number & vbCrLf & _
"Description: " & Err.Description
Resume Exit_Command121_Click
End Sub

[Phil Knapton]

For info:-
Fields in tblQuoteLineItem
LineItemID Autonumber & Primary
QuoteID - Number
ProdTypeID - Number
QLI Description - memo
QLI Quantity - number
QLI Cost - Currency

Thanks again

[Phil Knapton]

I have corrected an error of mine in the name of the QLI Quantity field and the error code I get now is 3061 - Too few parameters. Expected 1.
Thanks again

[RuralGuy]

Which line is highlighted by the Debugger when you get the error?

[RuralGuy]

Here's what the code looks like when you use the code tags, the "#" on the tool bar:

Private Sub Command121_Click()
'-- Renew the current Quote by duplicating the existing records...
'-- but giving the new records a fresh QuoteID of their own.
   On Error GoTo Err_Command121_Click
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim MySQL As String
   Dim MyQuoteID As Long
   Dim NewQuoteID As Long
   Set db = CurrentDb
   MyQuoteID = [QuoteID]   '-- Directly from the Current Record of the MainForm
   '-- Append a new record to tblQuote table with *almost* all of the same values as the current record
   '-- We let the table create a new AutoNumber [Quote ID] and use the current DATE
   '-- BTW, DATE is a RESERVED word and should *not* be used as a FieldName
   MySQL = "INSERT INTO tblQuote ( [Clientcontactid], [Userid], [companyid], [Siteid] , " & _
           "[quote Date], [quote status], [quote intro text], [quote exit text]) " & _
           "SELECT [Clientcontactid], [Userid], [companyid], [Siteid] , [quote Date], " & _
           "[quote status], [quote intro text], [quote exit text] FROM tblQuote " & _
           "WHERE [QuoteID] = " & MyQuoteID & ";"
   db.Execute MySQL, dbFailOnError
   '-- Get the *new* QuoteID
   Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
   NewQuoteID = rs!LastID
   rs.Close
   Set rs = Nothing
   '-- Now we need to duplicate any LineItems associated with the previous quote
   MySQL = "INSERT INTO tblquotelineitem ( [quoteid], [ProdTypeid], [QLI Description] , [QLI qantity], [QLI Cost]) " & _
           "SELECT """ & NewQuoteID & """, [ProdTypeid], [QLI Description] , [QLI qantity], [QLI Cost] FROM tblquotelineitem " & _
           "WHERE [quoteid] = " & MyQuoteID & ";"
   db.Execute MySQL, dbFailOnError
   '-- You will still need to move your MainForm to the newly created quote
   '-- You can do this with a FindFirst
   '-- The SubForm should follow along with the MainForm
Exit_Command121_Click:
   On Error Resume Next
   rs.Close
   Set rs = Nothing
   Set db = Nothing
   Exit Sub
Err_Command121_Click:
   MsgBox "Error Number: " & Err.Number & vbCrLf & _
          "Description: " & Err.Description
   Resume Exit_Command121_Click
End Sub

Make the change I highlighted in red.

[Phil Knapton]

That worked perfectly.
Thanks for all of your help

[RuralGuy]

Outstanding! Are you ready to use the THread tools and mark this thread as Solved yet?