Solved Copying records from multiple related tables to another set of similarly related tables. (1 Viewer)

slharman1

Member
Local time
Today, 00:03
Joined
Mar 8, 2021
Messages
467
Here is what I need to accomplish:

Table 1: Quotes - fields: PK=QuoteID, QuoteNumber, Desc - (there is only one record that needs to be copied)
Table 2: QuoteDetails - fields: PK=QuoteDetailsID, FK-QuoteID, Desc, Qty, Price - (there are multiple records that need to be copied)
Table 3: QuoteAccesories - fields: PK=QuoteAccID, FK=QuoteDetailsID, Desc, Qty, Price - (there are multiple records that need to be copied.

These tables are related by the obvious fields shown.
The description for both the QuoteDetails and the QuoteAcc tables typically have strings with this type of info - 2'-4" long x 4'-6" wide
I need to copy the related records from all three tables using QuoteNumber (or even QuoteID if that's easier) to an Orders set of tables with the same fields (but there are other fields as well.)

I have been struggling to get the code in previous thread I had posted with to work past the initial Quote table being copied to the order table - in other words no QuoteDetails records or QuoteDetailAcc records are getting inserted to the appropriate orders tables.

My original code is flawed because when you get to the accessories Loop part of the code my recordset is not correct and I do not know how to build it correctly.
But I have even removed that accessories Loop portion of code and cannot get the QuoteDetails part of the code to work.
I am posting the code here just to show what my attempt was but I am thinking there has to be another way to accomplish my goal.
any help would be greatly appreciated.
My code from the first attempt will be posted next.
 

slharman1

Member
Local time
Today, 00:03
Joined
Mar 8, 2021
Messages
467
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
 

slharman1

Member
Local time
Today, 00:03
Joined
Mar 8, 2021
Messages
467
When I run this code I do not get an error but I also do not get any QuoteDetails records or QuoteAcc records copied to their respective orders tables.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:03
Joined
May 7, 2009
Messages
19,169
you have 2 on-going thread with same topic.
be patient and just use 1 thread.
 

slharman1

Member
Local time
Today, 00:03
Joined
Mar 8, 2021
Messages
467
you have 2 on-going thread with same topic.
be patient and just use 1 thread.
Sorry, I was trying to ask using a different method. I will respond to your post on my other thread in a minute.
 

Users who are viewing this thread

Top Bottom