Public Function AddNewOrder() Dim curDB As DAO.Database Set curDB = CurrentDb Dim varInput As Variant, neworder As Long varInput = InputBox("Enter quote number for new order:") If IsNumeric(varInput) = False Then MsgBox varInput & " is not a valid quote number" Exit Function End If neworder = CLng(varInput) Dim rsQuoteList As DAO.Recordset 'added quoteID to SQL for details and accessories Dim strSQL_QuoteRSL As String strSQL_QuoteRSL = "SELECT QuoteID, QuoteNumber, CustID, CustConID, CustLocID, JobName, ShippingID, TermsID, Notes FROM tblQuotes WHERE QuoteNumber = " & neworder 'Quote variables Dim lngQuoteNumber As Long Dim lngCustID As Long Dim lngCustConID As Long Dim lngCustLocID As Long Dim strJobName As String Dim lngShippingID As Long Dim lngTermsID As Long Dim strNotes As String Dim lngQuoteID As Long Set rsQuoteList = curDB.OpenRecordset(strSQL_QuoteRSL, 8) 'Dim lngQuoteID As Long 'lngQuoteID = rsQuoteList!QuoteID 'get QuoteID for details Do Until rsQuoteList.EOF lngQuoteID = rsQuoteList!QuoteID 'get QuoteID for details lngQuoteNumber = rsQuoteList!QuoteNumber lngCustID = rsQuoteList!CustID lngCustConID = rsQuoteList!CustConID lngCustLocID = rsQuoteList!CustLocID strJobName = rsQuoteList!JobName lngShippingID = rsQuoteList!ShippingID lngTermsID = rsQuoteList!TermsID strNotes = rsQuoteList!Notes strJobName = Chr(34) & strJobName & Chr(34) strNotes = Chr(34) & strNotes & Chr(34) Dim strSQLQuoteInsert As String strSQLQuoteInsert = "INSERT INTO tblOrders(OrderNumber, CustID, CustConID, CustLocID, JobName, ShippingID, TermsID, Notes) VALUES (" & lngQuoteNumber & ", " & lngCustID & ", " & lngCustConID & ", " & lngCustLocID & ", " & strJobName & ", " & lngShippingID & ", " & lngTermsID & ", " & strNotes & ")" curDB.Execute strSQLQuoteInsert Dim rsQuoteDetailsList As DAO.Recordset Dim strSQL_QuoteDetailsRSL As String strSQL_QuoteDetailsRSL = "SELECT QuoteDetailID, ItemNo, EstID, ModelNo, Description, Qty, Price, QuoteID, AccessPrice FROM tblQuoteDetails WHERE QuoteID = " & lngQuoteID 'Details Variables Dim lngQuoteDetailID As Variant Dim strItemNo As Variant Dim lngEstID As Variant Dim strModelNo As Variant Dim strDescription As Variant Dim lngQty As Variant Dim curPrice As Variant 'Dim lngQuoteID As Long Dim curAccessPrice As Variant Set rsQuoteDetailsList = curDB.OpenRecordset(strSQL_QuoteDetailsRSL, 8) Do Until rsQuoteDetailsList.EOF lngQuoteDetailID = rsQuoteDetailsList!QuoteDetailID strItemNo = rsQuoteDetailsList!ItemNo lngEstID = rsQuoteDetailsList!EstID strModelNo = Nz(rsQuoteDetailsList!ModelNo, "Custom") strDescription = rsQuoteDetailsList!Description lngQty = rsQuoteDetailsList!Qty curPrice = rsQuoteDetailsList!Price lngQuoteID = rsQuoteDetailsList!QuoteID curAccessPrice = Nz(rsQuoteDetailsList!AccessPrice, 0) strItemNo = strItemNo & "" strModelNo = strModelNo & "" strDescription = strDescription & "" strDescription = Replace(strDescription, "'", "''") 'strDescription = Chr(34) & strDescription & Chr(34) Dim strSQLQuoteDetailsInsert As String 'strSQLQuoteDetailsInsert = "INSERT INTO tblOrderDetails(ItemNo, EstID, ModelNo, Description, Qty, Price, OrderID, AccessPrice) VALUES (" & strItemNo & ", " & lngEstID & ", " & strModelNo & ", " & strDescription & ", " & lngQty & ", " & curPrice & ", " & lngQuoteID & ", " & curAccessPrice & ")" 'curDB.Execute strSQLQuoteDetailsInsert strSQLQuoteDetailsInsert = "INSERT INTO tblOrderDetails(ItemNo, EstID, ModelNo, Description, Qty, Price, OrderID, AccessPrice) VALUES (p0, p1, p2, p3, p4, p5, p6, p7);" With curDB.CreateQueryDef("", strSQLQuoteDetailsInsert) .Parameters(0) = strItemNo .Parameters(1) = lngEstID .Parameters(2) = strModelNo .Parameters(3) = strDescription .Parameters(4) = lngQty .Parameters(5) = curPrice .Parameters(6) = lngQuoteID .Parameters(7) = curAccessPrice .Execute dbFailOnError End With 'Accessories Start here Dim rsQuoteAccessList As DAO.Recordset Dim strSQL_QuoteAccessRSL As String strSQL_QuoteAccessRSL = "SELECT QuoteAccID, QuoteDetailID, ItemNo, EstID, ModelNo, Description, Qty, Price FROM tblQuoteAcc WHERE QuoteDetailID = " & lngQuoteDetailID 'strSQL_QuoteAccessRSL = "SELECT tblQuoteAcc.QuoteAccID, tblQuoteAcc.QuoteDetailID, tblQuoteAcc.ItemNo, tblQuoteAcc.EstID, tblQuoteAcc.ModelNo, tblQuoteAcc.Description, tblQuoteAcc.Qty, tblQuoteAcc.Price, tblQuotes.QuoteNumber FROM tblQuotes LEFT JOIN tblQuoteDetails ON tblQuotes.QuoteID = tblQuoteDetails.QuoteID LEFT JOIN tblQuoteAcc ON tblQuoteDetails.QuoteDetailID = tblQuoteAcc.QuoteDetailID WHERE tblQuotes.QuoteNumber = " neworder 'Accessories variables Dim strAItemNo As Variant Dim lngAEstID As Variant Dim strAModelNo As Variant Dim strADescription As Variant Dim lngAQty As Variant Dim curAPrice As Variant Set rsQuoteAccessList = curDB.OpenRecordset(strSQL_QuoteAccessRSL, 8) Do Until rsQuoteAccessList.EOF lngQuoteDetailID = rsQuoteAccessList!QuoteDetailID strAItemNo = rsQuoteAccessList!ItemNo lngAEstID = rsQuoteAccessList!EstID strAModelNo = Nz(rsQuoteAccessList!ModelNo, "Custom") strADescription = rsQuoteAccessList!Description lngAQty = rsQuoteAccessList!Qty curAPrice = rsQuoteAccessList!Price strAItemNo = strAItemNo & "" strAModelNo = strAModelNo & "" strADescription = strDescription & "" strADescription = Replace(strDescription, "'", "''") 'strADescription = Chr(34) & Chr(34) & strDescription & Chr(34) & Chr(34) Dim strSQLQuoteAccessInsert As String 'strSQLQuoteAccessInsert = "INSERT INTO tblOrderAcc(OrderDetailID, ItemNo, EstID, ModelNo, Description, Qty, Price) VALUES (" & lngQuoteDetailID & ", " & strAItemNo & ", " & lngAEstID & ", " & strAModelNo & ", " & strADescription & ", " & lngAQty & ", " & curAPrice & ")" 'curDB.Execute strSQLQuoteAccessInsert strSQLQuoteAccessInsert = "INSERT INTO tblOrderAcc(OrderDetailID, ItemNo, EstID, ModelNo, Description, Qty, Price) VALUES (p0, p1, p2, p3, p4, p5, p6);" With curDB.CreateQueryDef("", strSQLQuoteAccessInsert) .Parameters(0) = lngQuoteDetailID .Parameters(1) = strAItemNo .Parameters(2) = lngAEstID .Parameters(3) = strAModelNo .Parameters(4) = strADescription .Parameters(5) = lngAQty .Parameters(6) = curAPrice .Execute dbFailOnError End With rsQuoteAccessList.MoveNext Loop rsQuoteAccessList.Close Set rsQuoteAccessList = Nothing 'Accessories End Here rsQuoteDetailsList.MoveNext Loop rsQuoteDetailsList.Close Set rsQuoteDetailsList = Nothing 'MsgBox ">>>" & lngQuoteNumber rsQuoteList.MoveNext Loop If Not (rsQuoteList Is Nothing) Then rsQuoteList.Close End If Set rsQuoteList = Nothing Set curDB = Nothing End Function