Solved Change quote into an order (1 Viewer)

Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:57
Joined
Jul 9, 2003
Messages
16,280
And Here:-

VBA-(SQL)-String Tutorial

Companion text to an extensive VBA-(SQL)-String Video-Tutorial for Beginners
codekabinett.com codekabinett.com

See:- Delimiters in Text Literals

Example:-
Code: 
customerName = Replace(customerName, "'", "''")
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:57
Joined
Jul 9, 2003
Messages
16,280
So it would possibly be:-

Code: 
 ' lngQuoteID is previously set in code
                    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 = rsQuoteDetailsList!ModelNo
                            strDescription = rsQuoteDetailsList!Description
                            lngQty = rsQuoteDetailsList!Qty
                            curPrice = rsQuoteDetailsList!Price
                            lngQuoteID = rsQuoteDetailsList!QuoteID
                            curAccessPrice = rsQuoteDetailsList!AccessPrice
                                'From:-
                                'https://codekabinett.com/rdumps.php?Lang=2&targetDoc=vba-sql-string-tutorial
                                
                                'customerName = Replace(customerName, "'", "''")
                                
                            strItemNo = Chr(34) & strItemNo & Chr(34)
                            strModelNo = Chr(34) & strModelNo & 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

You might be tempted to condense the two new lines in to one formula, but with it on two lines, it's obvious what you are doing. (Self documenting) If you did want to condense it into a single formula, you could create a separate function, your function name would provide the "Self Documentation"
 
Last edited:
S

slharman1

Member
Local time
Today, 17:57
Joined
Mar 8, 2021
Messages
476
Uncle Gizmo said:
So it would possibly be:-

Code: 
' lngQuoteID is previously set in code
                    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 = rsQuoteDetailsList!ModelNo
                            strDescription = rsQuoteDetailsList!Description
                            lngQty = rsQuoteDetailsList!Qty
                            curPrice = rsQuoteDetailsList!Price
                            lngQuoteID = rsQuoteDetailsList!QuoteID
                            curAccessPrice = rsQuoteDetailsList!AccessPrice
                                'From:-
                                'https://codekabinett.com/rdumps.php?Lang=2&targetDoc=vba-sql-string-tutorial
                             
                                'customerName = Replace(customerName, "'", "''")
                             
                            strItemNo = Chr(34) & strItemNo & Chr(34)
                            strModelNo = Chr(34) & strModelNo & 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

You might be tempted to condense the two new lines in to one formula, but with it on two lines, it's obvious what you are doing. (Self documenting) If you did want to condense it into a single formula, you could create a separate function, your function name would provide the "Self Documentation"
Click to expand...
Nifty, Thanks!
So are you saying to only add this line of code where you show it?
Code: 
 strDescription = Replace(strDescription, Chr(34), Chr(34) & Chr(34))
I am confused , doesn't this replac e
Uncle Gizmo said:
So it would possibly be:-

Code: 
' lngQuoteID is previously set in code
                    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 = rsQuoteDetailsList!ModelNo
                            strDescription = rsQuoteDetailsList!Description
                            lngQty = rsQuoteDetailsList!Qty
                            curPrice = rsQuoteDetailsList!Price
                            lngQuoteID = rsQuoteDetailsList!QuoteID
                            curAccessPrice = rsQuoteDetailsList!AccessPrice
                                'From:-
                                'https://codekabinett.com/rdumps.php?Lang=2&targetDoc=vba-sql-string-tutorial
                              
                                'customerName = Replace(customerName, "'", "''")
                              
                            strItemNo = Chr(34) & strItemNo & Chr(34)
                            strModelNo = Chr(34) & strModelNo & 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

You might be tempted to condense the two new lines in to one formula, but with it on two lines, it's obvious what you are doing. (Self documenting) If you did want to condense it into a single formula, you could create a separate function, your function name would provide the "Self Documentation"
Click to expand...
ah, uncle Giz, took me sleeping on it to realize we are just replacing with double quotes. I knew I needed to step away for a bit. 😊
 
Last edited:
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:57
Joined
Jul 9, 2003
Messages
16,280
slharman1 said:
ah, uncle Giz, toll me sleeping on it to realize we are just replacing with double quotes.
Click to expand...

The way I have answered the question is somewhat confusing. To Clarify:-

There are several ways of doing it, Philipp Stiefel shows one way:- customerName = Replace(customerName, "'", "''") this is for replacing single quotes. It appears that Philipp uses single quotes as his string delimiters, which is fine. You can use either single or double quotes, as long as you are consistent. You might ask why would you use one or the other?

If you used double quotes as string delimiters, and were entering people's names, names like this --- Mr O' Brien --- these names would be entered correctly, without any modification to the code.

If you knew that you were going to be entering a lot of data in inches like --- 20" --- then you could construct your SQL Statements using single quotes and when the text entered contains double quotes, it would be entered correctly without any modification to the code.

I suspect the major difference in the choice of using single or double depends on if you are writing SQL Statements for MS Access or MSSQL Server. I think (I don't know) the convention for SQL Server is to use single quotes and the convention for Microsoft Access is to use double quotes. I suspect you can use either single or double in either environment, but I'm not 100% sure about that. Maybe someone that is more familiar with MSSQL Server would chip in?

These are all valid replace statements.

Replace Single Quotes
1) customerName = Replace(customerName, "'", "''")
2) customerName = Replace(customerName, Chr(39), Chr(39) & Chr(39))
Chr(39) is the ASCII code for a Single Quote and because you are entering the ASCII code you do not need to delimit it with the speech marks (double quotes)

Replace Double Quotes
1) customerName = Replace(customerName, """, """")
2) customerName = Replace(customerName, Chr(34), Chr(34) & Chr(34))
Chr(34) is the ASCII code for a Double Quote and because you are entering the ASCII code you do not need to delimit it with the speech marks (double quotes)

Why do we have this problem with the quotes in SQL statements?

If the double quote is used as the SQL Statement delimiter as in the following sample SQL Statement:-

SQL: 
strSQLQuoteDetailsInsert = "INSERT INTO tblOrderDetails(ItemNo, EstID, ModelNo, Description) VALUES (" & strItemNo & ", " & lngEstID & ", " & strModelNo & ", " & strDescription & ")"

If the variable passed in "strDescription" contains a Double Quote then your SQL Statement would see that Double Quote as a text delimiter for the SQL Statement and not part of the passed through text.
 
S

slharman1

Member
Local time
Today, 17:57
Joined
Mar 8, 2021
Messages
476
Uncle Gizmo said:
So it would possibly be:-

Code: 
' lngQuoteID is previously set in code
                    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 = rsQuoteDetailsList!ModelNo
                            strDescription = rsQuoteDetailsList!Description
                            lngQty = rsQuoteDetailsList!Qty
                            curPrice = rsQuoteDetailsList!Price
                            lngQuoteID = rsQuoteDetailsList!QuoteID
                            curAccessPrice = rsQuoteDetailsList!AccessPrice
                                'From:-
                                'https://codekabinett.com/rdumps.php?Lang=2&targetDoc=vba-sql-string-tutorial
                              
                                'customerName = Replace(customerName, "'", "''")
                              
                            strItemNo = Chr(34) & strItemNo & Chr(34)
                            strModelNo = Chr(34) & strModelNo & 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

You might be tempted to condense the two new lines in to one formula, but with it on two lines, it's obvious what you are doing. (Self documenting) If you did want to condense it into a single formula, you could create a separate function, your function name would provide the "Self Documentation"
Click to expand...

Uncle Gizmo said:
The way I have answered the question is somewhat confusing. To Clarify:-

There are several ways of doing it, Philipp Stiefel shows one way:- customerName = Replace(customerName, "'", "''") this is for replacing single quotes. It appears that Philipp uses single quotes as his string delimiters, which is fine. You can use either single or double quotes, as long as you are consistent. You might ask why would you use one or the other?

If you used double quotes as string delimiters, and were entering people's names, names like this --- Mr O' Brien --- these names would be entered correctly, without any modification to the code.

If you knew that you were going to be entering a lot of data in inches like --- 20" --- then you could construct your SQL Statements using single quotes and when the text entered contains double quotes, it would be entered correctly without any modification to the code.

I suspect the major difference in the choice of using single or double depends on if you are writing SQL Statements for MS Access or MSSQL Server. I think (I don't know) the convention for SQL Server is to use single quotes and the convention for Microsoft Access is to use double quotes. I suspect you can use either single or double in either environment, but I'm not 100% sure about that. Maybe someone that is more familiar with MSSQL Server would chip in?

These are all valid replace statements.

Replace Single Quotes
1) customerName = Replace(customerName, "'", "''")
2) customerName = Replace(customerName, Chr(39), Chr(39) & Chr(39))
Chr(39) is the ASCII code for a Single Quote and because you are entering the ASCII code you do not need to delimit it with the speech marks (double quotes)

Replace Double Quotes
1) customerName = Replace(customerName, """, """")
2) customerName = Replace(customerName, Chr(34), Chr(34) & Chr(34))
Chr(34) is the ASCII code for a Double Quote and because you are entering the ASCII code you do not need to delimit it with the speech marks (double quotes)

Why do we have this problem with the quotes in SQL statements?

If the double quote is used as the SQL Statement delimiter as in the following sample SQL Statement:-

SQL: 
strSQLQuoteDetailsInsert = "INSERT INTO tblOrderDetails(ItemNo, EstID, ModelNo, Description) VALUES (" & strItemNo & ", " & lngEstID & ", " & strModelNo & ", " & strDescription & ")"

If the variable passed in "strDescription" contains a Double Quote then your SQL Statement would see that Double Quote as a text delimiter for the SQL Statement and not part of the passed through text.
Click to expand...
The problem is that I have Single quotes and double quote that are in the string, such as: 2'-6" long, 4'-0" wide.
What the hell do I do now?
I am starting to wonder if I can use a macro and just run some append queries or If I will run into the same issues.
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:57
Joined
Jul 9, 2003
Messages
16,280
slharman1 said:
The problem is that I have Single quotes and double quote that are in the string,
Click to expand...

Have you tried the code that I posted? I think it should work, because if you're using double quotes then single Quotes will pass through OK, and the Replace code will replace any double Quotes.
 
S

slharman1

Member
Local time
Today, 17:57
Joined
Mar 8, 2021
Messages
476
Uncle Gizmo said:
Have you tried the code that I posted? I think it should work, because if you're using double quotes then single Quotes will pass through OK, and the Replace code will replace any double Quotes.
Click to expand...
Yes I did, it changed the string that came through and put double quotes in the string. Although I am not 100% sure I understand your code. It looks to me it replaces everything I’m the string where a quote is to double quotes. Is that how it is supposed to act?
 
S

slharman1

Member
Local time
Today, 17:57
Joined
Mar 8, 2021
Messages
476
Uncle Gizmo said:
Have you tried the code that I posted? I think it should work, because if you're using double quotes then single Quotes will pass through OK, and the Replace code will replace any double Quotes.
Click to expand...
Here is what the immediate window give me on break:
?strSQLQuoteAccessInsert
INSERT INTO tblOrderAcc(OrderDetailID, ItemNo, EstID, ModelNo, Description, Qty, Price) VALUES (9, "1", 19, " ", ""Pot Sink with rolled rim, 30"""" wide x 12'-0"""" long, with (3) 30"""" long x 26"""" wide x 15"""" deep. (3) lever drains. Faucets by others."", 1, 224.0304)

For some reason the code gives me a inappropriate use of null for the ModelNo field even though it is not null.
Also, for the second and third records in the details section of the insert, I have changed the setting of the variable to this:
Code: 
curAccessPrice = Nz(rsQuoteDetailsList!AccessPrice, 0)
Do to the fact that some records will have null in the field for the records regularly.
It will happen for the Model Number in the details and accessories sections as well on some occasions.

Here is my current code, I cannot seem to get the details or the accessories records to come into the orders detail and order accessories tables.
I am getting frustrated.

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
    

        
        Set rsQuoteList = curDB.OpenRecordset(strSQL_QuoteRSL, 8)
        Dim lngQuoteID As Long
        
        lngQuoteID = rsQuoteList!QuoteID 'get QuoteID for details
        
        
        
          Do Until rsQuoteList.EOF
                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 = rsQuoteDetailsList!ModelNo
                            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
                            
                            
                            
                                
                                   '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 = rsQuoteAccessList!ModelNo
                                        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
 
Last edited:
S

slharman1

Member
Local time
Today, 17:57
Joined
Mar 8, 2021
Messages
476
Uncle Gizmo said:
Have you tried the code that I posted? I think it should work, because if you're using double quotes then single Quotes will pass through OK, and the Replace code will replace any double Quotes.
Click to expand...
Not sure if you saw my other two posts below, but I am making it through the code until the insert for the accessories, I can't understand why the details portion makes it through but not the accessories portion.

Even thogh it is still not writing the first record to the details table
 
Isaac

Isaac

Lifelong Learner
Local time
Today, 15:57
Joined
Mar 14, 2017
Messages
8,777
A lot has happened in this thread since I was away and so pardon me if someone already mentioned this, but I noticed there was some discussion about needing char(34), ", around text and just wanted to throw in this tip:

Access will also accept single quotes, which means you can reduce the complexity of the VBA significantly by simply leveraging this approach rather than using double quotes at all.

strSQL = "select * from table where textcolumn=" & chr(34) & SomeVariable & chr(34)

becomes

strSQL = select * from table where textcolumn='" & SomeVariable & "'"
 
S

slharman1

Member
Local time
Today, 17:57
Joined
Mar 8, 2021
Messages
476
Isaac said:
A lot has happened in this thread since I was away and so pardon me if someone already mentioned this, but I noticed there was some discussion about needing char(34), ", around text and just wanted to throw in this tip:

Access will also accept single quotes, which means you can reduce the complexity of the VBA significantly by simply leveraging this approach rather than using double quotes at all.

strSQL = "select * from table where textcolumn=" & chr(34) & SomeVariable & chr(34)

becomes

strSQL = select * from table where textcolumn='" & SomeVariable & "'"
Click to expand...
Isaac, I am so frustrated I am looking for a new solution to my problem.
Here is what I need to accomplish:

Table 1: Quotes - fields: PK=QuoteID, QuoteNumber, Desc
Table 2: QuoteDetails - fields: PK=QuoteDetailsID, FK-QuoteID, Desc, Qty, Price
Table 3: QuoteAccesories - fields: PK=QuoteAccID, FK=QuoteDetailsID, Desc, Qty, Price

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 all the previous posts 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.

This is the last step before I can begin using and refining this database and I am at my wits end.
 
Isaac

Isaac

Lifelong Learner
Local time
Today, 15:57
Joined
Mar 14, 2017
Messages
8,777
Personally, I would create a new thread that defines/frames the specific problem you're having now. You might get a fresh set of eyes focused on the narrow problem at hand. Remember to post the specific problem and the code you've used/using that's not producing desired results.
 
S

slharman1

Member
Local time
Today, 17:57
Joined
Mar 8, 2021
Messages
476
Isaac said:
Personally, I would create a new thread that defines/frames the specific problem you're having now. You might get a fresh set of eyes focused on the narrow problem at hand. Remember to post the specific problem and the code you've used/using that's not producing desired results.
Click to expand...
I will do that but my code is initially flawed because I do not understand how to create the proper recordset for the final accessories table since the quote id is no longer present in the accessories table due to normalization rules. (ie, the quoteID is not in the table for the accessories, only the quoteDetailsID)
 
S

slharman1

Member
Local time
Today, 17:57
Joined
Mar 8, 2021
Messages
476
Uncle Gizmo said:
Have you tried the code that I posted? I think it should work, because if you're using double quotes then single Quotes will pass through OK, and the Replace code will replace any double Quotes.
Click to expand...
I am using both double quotes and single quotes in the text field that is the variable
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:57
Joined
Jul 9, 2003
Messages
16,280
slharman1 said:
I am using both double quotes and single quotes in the text field that is the variable
Click to expand...

Are you saying it's working? or have you got a problem?

If a problem, then you can do some tests yourself.

Set up a temporary table with data in it, sample data that you can pass through using the insert into query. You'll only need 5 or 10 records. Keep changing the contents of the records until you get it working, and then add characters back in until it breaks. You should be able to identify what is causing the problem.
 
S

slharman1

Member
Local time
Today, 17:57
Joined
Mar 8, 2021
Messages
476
Uncle Gizmo said:
Are you saying it's working? or have you got a problem?

If a problem, then you can do some tests yourself.

Set up a temporary table with data in it, sample data that you can pass through using the insert into query. You'll only need 5 or 10 records. Keep changing the contents of the records until you get it working, and then add characters back in until it breaks. You should be able to identify what is causing the problem.
Click to expand...
It is working for the parent table but not for the child tables.
I am not sure my nested loops are correct.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:57
Joined
May 7, 2009
Messages
19,230
have not tested yet.
 

Attachments

  • code.txt
    10.7 KB · Views: 132
S

slharman1

Member
Local time
Today, 17:57
Joined
Mar 8, 2021
Messages
476
arnelgp said:
have not tested
Click to expand...

arnelgp said:
have not tested yet.
Click to expand...
I have actually solved the problem of getting the INSERT INTO not working.
Could you look at my new code and tell me where I am going wrong with the looping for this line: Do Until rsOrderDetailsList.EOF
I am getting data from another record in the child table.
I will take a look at your code and try to figure out what you are trying to show me.
Thank you
 

Attachments

  • AddNewOrder.txt
    9.6 KB · Views: 134
You must log in or register to reply here.

Similar threads

G
Solved Error #size! on calculated control based on subform value
Replies
2
Views
179
GaP42
G
A
Solved Copying data from Quote-line-items Subform to Sales-Order-line-items Table : Error 3022
Replies
6
Views
259
Pat Hartman
P
H
Solved Auto Create record in TblOrderDetails for the order when we create it in TblOrder
Replies
5
Views
501
jdraw
jdraw
D
Automatic form with calculations based on two tables
Replies
15
Views
247
arnelgp
arnelgp
amorosik
Difference in selection and ordering on RecordSource compared to using Filter and Order property
2
Replies
23
Views
559
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom