Renew Multiple records via a form

[Phil Knapton]

I have a quotations database where a quote form writes to 2 tables (Quote table and quote line table through a subform). How do I put a renew button on the quote form which will create a new record in the quotation table and multiple new records in the quote line table

[RuralGuy]

Assuming your MainForm and SubForm are linked properly and the SubForm is in Continuous form view, then simply moving the mainForm to a new record should do what you are asking. No code required.

[Phil Knapton]

If I view a record (a quotation that was entered 12 months ago) in the form then I would want to raise a new unique quote and possibly change the prices - how would i go about doing that? I may be misunderstanding your reply here but I can see any preset controls to create a new record based on details in a form and a subform that cover 2 tables.
Form details fields :- tblQuote
Quote ID (primary)
Client id
User id
Quoting company id
Site id
date
quote status
quote intro text
quote exit text

Subform: -tblquotelineitems
quotelineitemid
quoteid
catagoryid
description
qty
value

I dont want to have to re-enter all the details in the renewal quote as the qty of lines in the quote can be up to 50 and they are memo fields.
This is a services quote rather than a product so the line detail is very specific to the client and site

[RuralGuy]

It sounds like you really want to duplicate the quote as a new quote. Is that true?

[Phil Knapton]

yes that is true

[RuralGuy]

Hmm...you need a new Quote record that duplicates a current record but has a new QuoteID and then all of the quote detail records need to be duplicated with the new QuotID number. Is that an accurate description of what you need?

[Phil Knapton]

Yes that is exactly what I am looking for.
Are you able to point me in the right direction?

[RuralGuy]

I'll give it some more thought but it sounds like a job for a code procedure. How comfortable are you with code?

[RuralGuy]

Is the QuoteID field in the tblQuote table an AutoNumber field? I assume the quotelineitemid in the tblquotelineitems table is also an AutoNumber field.

[Phil Knapton]

QuoteID is autonumber and primary
QuoteLineItemID is autonumber and primary

[RuralGuy]

Good design! I'll give it some thought as to the Procedure needed. Memo field huh? I hope they are not a problem. You need more that 256 characters for these descriptions?

[Phil Knapton]

Thanks
The justification of text in the memo field was a pain to start with until I found Lebans code and that seems to work fine.
The majority of the quotations are for service contracts that cover the control of legionnaires disease in buildings. There needs to be a lot of text to cover the exact services offered. 1 "lineItem" of the quote can be a page of text.

[RuralGuy]

I'll take your word for it. Here's some issues that may crop up and need to be delt with: http://allenbrowne.com/ser-63.html

[RuralGuy]

BTW, as I'm writing this procedure it becomes obvious you have use DATE and VALUE as Field names. Not a good idea. You can work around that with [brackets] but better to rename them. http://www.allenbrowne.com/AppIssueBadWord.html

[RuralGuy]

Remember this is <<< AIR CODE >>> so use at your own risk! This is my idea of the MainForm button code for what you requested. I used the field names you supplied in post #3.

Private Sub cmdReQuote_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_cmdReQuote_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 = [Quote ID]     '-- 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 ( [Client id], [User id], [Quoting company id], [Site id] , " & _
           "[Date], [quote status], [quote intro text], [quote exit text]) " & _
           "SELECT [Client id], [User id], [Quoting company id], [Site id] , Date, " & _
           "[quote status], [quote intro text], [quote exit text] FROM tblQuote " & _
           "WHERE [Quote ID] = " & 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 tblquotelineitems ( quoteid, catagoryid, Description , qty, [Value]) " & _
           "SELECT NewQuoteID, catagoryid, Description , qty, [Value] FROM tblquotelineitems " & _
           "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_cmdReQuote_Click:
   On Error Resume Next
   rs.Close
   Set rs = Nothing
   Set db = Nothing
   Exit Sub
Err_cmdReQuote_Click:
   MsgBox "Error Number: " & Err.Number & vbCrLf & _
          "Description:  " & Err.Description
   Resume Exit_cmdReQuote_Click
End Sub