I have a public function that I call from a cmdButton on a form.
1 - It copies a record from a recordset and insert into a table with the similar fields. (This part of the code is working)
2 - It copies records from a child table and insert them into a child table from the first insert on line 1. (this code is not working)
3 - It copies records from the child's child table and inserts them into the child's child table from line 1 (this code is not working)
In addition the description fields in both child tables have text similar to: 2'-6" long x 4'-0" wide.
I have used the Chr(34) form of quotes to my string variables and the immediate window give the results of the varibles correctly.
1 - It copies a record from a recordset and insert into a table with the similar fields. (This part of the code is working)
2 - It copies records from a child table and insert them into a child table from the first insert on line 1. (this code is not working)
3 - It copies records from the child's child table and inserts them into the child's child table from line 1 (this code is not working)
In addition the description fields in both child tables have text similar to: 2'-6" long x 4'-0" wide.
I have used the Chr(34) form of quotes to my string variables and the immediate window give the results of the varibles correctly.
Code:
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 Long
Dim strItemNo As String
Dim lngEstID As Long
Dim strModelNo As String
Dim strDescription As String
Dim lngQty As Long
Dim curPrice As Currency
'Dim lngQuoteID As Long
Dim curAccessPrice As Currency
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 = Chr(34) & strItemNo & Chr(34)
strModelNo = Chr(34) & strModelNo & Chr(34)
strDescription = Chr(34) & strDescription & Chr(34)
strDescription = Replace(strDescription, Chr(34), Chr(34) & Chr(34))
'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
'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 String
Dim lngAEstID As Long
Dim strAModelNo As String
Dim strADescription As String
Dim lngAQty As Long
Dim curAPrice As Currency
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 = Chr(34) & strAItemNo & Chr(34)
strAModelNo = Chr(34) & strAModelNo & Chr(34)
strADescription = Chr(34) & strDescription & Chr(34)
strADescription = Replace(strDescription, Chr(34), Chr(34) & Chr(34))
'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
rsQuoteAccessList.MoveNext
Loop
'Accessories End Here
rsQuoteDetailsList.MoveNext
Loop
'MsgBox ">>>" & lngQuoteNumber
rsQuoteList.MoveNext
Loop
End Function
