Solved Public function help! (1 Viewer)

slharman1

Member
Local time
Today, 01:30
Joined
Mar 8, 2021
Messages
379
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.
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:30
Joined
May 7, 2009
Messages
14,648
have not tested yet.
 

Attachments

  • code.txt
    10.7 KB · Views: 219

Users who are viewing this thread

Top Bottom