Create Duplication of information from a created quote to three tables

[kd2017]

The text in the "immediate" window at the bottom of the screen is what he's after. Can you copy and past the full text?



When you see a line like 'Debug.Pring strSQL' that prints what ever is in the strSQL variable on the immediate window, useful for debugging purposes.

[Blings]

INSERT INTO tblProvisionalQuotes ( Project, FollowUpDate, QuoteNo, Revision, RaisedbyID, QuoteValue, DateCreated, MonthExpected, StatsuID, SourceID, SalesEngineerID, Delivery, ValidTo) Values ('Georgia Province Lighting', #02-Jul-2021#, 1024,'1A', 3, '28000', #08-Jul-2021#, #16-Jul-2021#, 1, 1, 3, '4 working weeks, to be confirmed on receipt of official order.', #23-Sep-2021#)

INSERT INTO tblQuoteDetails (ProvisionalQuoteID, TypeID, CodeID, Description, Qty, DiscountID, SalePrice, Ref ) Values 1102, 20, 1039, 'Surface Mount CM Pendant - 100mm Diameter x 200mm High. GU10. C/w Ceiling Bracket. Finish: Powder Coated Matt Black or White', 17, 1, '519', 'Null' FROM tblQuoteDetails WHERE ProvisionalQuoteID = 1102;

[kd2017]

The second sql statement is missing parentheses around the values.

INSERT INTO tblQuoteDetails (ProvisionalQuoteID, TypeID, CodeID, Description, Qty, DiscountID, SalePrice, Ref ) Values (1102, 20, 1039, 'Surface Mount CM Pendant - 100mm Diameter x 200mm High. GU10. C/w Ceiling Bracket. Finish: Powder Coated Matt Black or White', 17, 1, '519', 'Null' FROM tblQuoteDetails WHERE ProvisionalQuoteID = 1102);

[Welshgasman]

I do not think you should have single quotes around Null either?
Nor 519 if that is meant to be the sale price?

[kd2017]

Or

FROM tblQuoteDetails WHERE ProvisionalQuoteID = 1102

We're mixing two different methods here.
If you can post a zipped copy of your database I'm sure we can clean this up for you.

[Blings]

Thanks for your responses, I really appreciate it! Can you please try amend the following for me?

strSQL = "INSERT INTO tblQuoteDetails (ProvisionalQuoteID, TypeID, CodeID, Description, Qty, DiscountID, SalePrice, Ref ) " & _
"Values " & lngID & " , " & TypeID & ", " & CodeID & ", '" & Description & "', " & Qty & ", " & DiscountID & ", " & SalePrice & ", '" & Nz(Ref, "Null") & _
"' FROM tblQuoteDetails WHERE ProvisionalQuoteID = " & Me.txtProvisionalQuoteID & ";"

[kd2017]

Yes, but first can you show us the relationship view of the 3 different tables involved here? Or at least something that shows us all of the exact field names.

I see some stuff in there that I was just using as a placeholder before (that was the pseudo part I mentioned)

[Blings]

It's actually just two tables, I don't need the third table involved anymore. The main form is from the tblProvisionalQuotes and the subform of the quote items is from the tblQuoteDetails (this is the seciton I am trying to duplicate.)

[Blings]

Or

FROM tblQuoteDetails WHERE ProvisionalQuoteID = 1102

We're mixing two different methods here.
If you can post a zipped copy of your database I'm sure we can clean this up for you.

I'm having difficult as my FE is 6,236 KB and BE is 2,324 KB.

[kd2017]

Try this:

Private Sub cmdDup_Click()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim sql As String
    Dim Revision As String
    Dim newQuoteId As Variant
    
    Revision = IncrementLetter2(Nz(Me.Revision, ""))
    
    'copy the quote
    sql = ""
    sql = sql & "INSERT INTO tblProvisionalQuotes ( QuoteNo, Revision, Project, RaisedBYID, QuoteValue, MonthExpected, StatsuID, SourceID, Specifier, Notes, SalesEngineerId, Delivery, ValidTo, OrderedBy, OrderDate, OrderNo, OrderValue, DateCreated, FollowUpDate, AreaID ) " & vbCrLf
    sql = sql & "SELECT tblProvisionalQuotes.QuoteNo, """ & Revision & """, tblProvisionalQuotes.Project, tblProvisionalQuotes.RaisedBYID, tblProvisionalQuotes.QuoteValue, tblProvisionalQuotes.MonthExpected, tblProvisionalQuotes.StatsuID, tblProvisionalQuotes.SourceID, tblProvisionalQuotes.Specifier, tblProvisionalQuotes.Notes, tblProvisionalQuotes.SalesEngineerId, tblProvisionalQuotes.Delivery, tblProvisionalQuotes.ValidTo, tblProvisionalQuotes.OrderedBy, tblProvisionalQuotes.OrderDate, tblProvisionalQuotes.OrderNo, tblProvisionalQuotes.OrderValue, tblProvisionalQuotes.DateCreated, tblProvisionalQuotes.FollowUpDate, tblProvisionalQuotes.AreaID " & vbCrLf
    sql = sql & "FROM tblProvisionalQuotes " & vbCrLf
    sql = sql & "WHERE (((tblProvisionalQuotes.ProvisionalQuoteId)=" & Me.ProvisionalQuoteID & "));"
    
    Debug.Print sql
    Set db = CurrentDb
    db.Execute sql, dbFailOnError

    'get the ID of the new revision
    newQuoteId = DLookup("ProvisionalQuoteId", "tblProvisionalQuotes", "QuoteNo = " & CLng(Me.QuoteNo) & " AND Revision = '" & Revision & "'")
    
    'make sure the first copy worked
    If IsNull(newQuoteId) Then Err.Raise 1001, , "Coping the quote failed"
    
    'copy the details (no need for a loop)
    sql = ""
    sql = sql & "INSERT INTO tblQuoteDetails ( ProvisionalQuoteID, TypeID, ElementID, CodeID, Code, Description, Qty, DiscountID, Tax, Delivery, SalesPrice, LineTotal, Ref ) " & vbCrLf
    sql = sql & "SELECT " & newQuoteId & ", tblQuoteDetails.TypeID, tblQuoteDetails.ElementID, tblQuoteDetails.CodeID, tblQuoteDetails.Code, tblQuoteDetails.Description, tblQuoteDetails.Qty, tblQuoteDetails.DiscountID, tblQuoteDetails.Tax, tblQuoteDetails.Delivery, tblQuoteDetails.SalesPrice, tblQuoteDetails.LineTotal, tblQuoteDetails.Ref " & vbCrLf
    sql = sql & "FROM tblQuoteDetails " & vbCrLf
    sql = sql & "WHERE (((ProvisionalQuoteID)=" & Me.ProvisionalQuoteID & "));"
    
    Debug.Print sql
    db.Execute sql, dbFailOnError
    
ExitHandler:
    Set db = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Sub

[pbaldy]

I'm having difficult as my FE is 6,236 KB and BE is 2,324 KB.

Have you tried doing a compact/repair and then zipping?

[Blings]

[Blings]

Have you tried doing a compact/repair and then zipping?

Yes, I've compact & repaired, it's a large FE - been working on it for a year.

[kd2017]

That usually means there's a typo in a field name somewhere

[Blings]

That usually means there's a typo in a field name somewhere

The Revision module is complicated. See the query for the main form of frmEditQuote. ProvisionalQuoteID 1146 - 1153 was me testing the code you wrote which threw the information as highlighted in yellow because we need to cater for some additional ALIASES I think. Attached is the query too.