Solved Change quote into an order

[slharman1]

Set the two string varibales using this: Chr(34) & varName & Chr(34) and Success!, Not really sure why though so where's the good in that.

Isaac, I can't tell you how much I appreciate your help with this! without guys like you newbs like me would struggle for a lot longer.
Thank again!

 

[slharman1]

Well if the result was showing you just the variable names instead of their values, that seems like the most fundamental problem. Remember when you are building up SQL using VBA, the result needs to be a literal string. That means concatenating strings to make a big string.

So it's never:

strSQL = "select * from table where id=VariableName"

It's always:

strSQL = "select * from table where id=" & VariableName

Anyway glad you got it working!

Isaac, I can't tell you how much I appreciate your help with this! without guys like you newbs like me would struggle for a lot longer.
Thank again!

 

[gemma-the-husky]

Chr(34) & varName & Chr(34)

chr(34) is a " symbol.

I always use this syntax, rather than entering double "" characters. I find it more readable, and it's easy to get it right. If you use "" characters directly, it's easier to get it wrong. Maybe you were only using one " character instead of two

given the above post, your string needs to look like this

where id = ABC

now because ABC is a string, ABC has to be contained in quote marks, so the string actually needs to look like this

where id = "ABC"

but because " is a string delimiter, you need 2 "" characters to get a 'real' " character so

where id = ""ABC"" or with a variable
where id = "" & varStrg & "" (I am not 100% sure whether these are correct to be honest)

but if you use chr(34) you only need one - so either this

where id = chr(34) & "ABC" & chr(34) or this
where id = chr(34) & varStrg & chr(34)

------
unfortunately, you cant declare a const
const quote = "" or even const quote="""

and then just use
where id = quote & varStrg & quote

so it's easier to just use chr(34)

 

[slharman1]

Chr(34) & varName & Chr(34)

chr(34) is a " symbol.

I always use this syntax, rather than entering double "" characters. I find it more readable, and it's easy to get it right. If you use "" characters directly, it's easier to get it wrong. Maybe you were only using one " character instead of two

given the above post, your string needs to look like this

where id = ABC

now because ABC is a string, ABC has to be contained in quote marks, so the string actually needs to look like this

where id = "ABC"

but because " is a string delimiter, you need 2 "" characters to get a 'real' " character so

where id = ""ABC"" or with a variable
where id = "" & varStrg & "" (I am not 100% sure whether these are correct to be honest)

but if you use chr(34) you only need one - so either this

where id = chr(34) & "ABC" & chr(34) or this
where id = chr(34) & varStrg & chr(34)

------
unfortunately, you cant declare a const
const quote = "" or even const quote="""

and then just use
where id = quote & varStrg & quote

so it's easier to just use chr(34)

Gemma thank you. The strings are variables so I just set the variables = chr(34) & chr(34) first and then just left the SQL statement as it was. Worked perfectly. Thanks again.

 

[slharman1]

I agree with Dave! I can never understand why people struggle to construct SQL Statements and in doing so, get the commas, speech marks and all that in the wrong places. It takes a lot of skill and practice to get right.

Most of the questions I see pop up on the forum regarding SQL Statements, where people can't get it right are due to syntax errors in these text strings. The problem is easily avoided by taking a slightly different approach in building the SQL string. As you can probably tell from my "nearly a rant" posting, it's one of my pet hates...

My method is very similar to Dave's, and I've done a video and blog about it on my website.

This is a clip from one of my videos where I discuss this in detail.

The YouTube is part of a Blog on my website here:-

INSERT INTO SQL Statement – Structured Guide - Nifty Access

Last updated: 2025-02-21 20:07:31 INSERT INTO SQL StatementThe INSERT INTO SQL Statement is known for its complexity. Many struggle with syntax errors that are often misleading. A structured approach makes handling SQL statements easier.Divide & Conquer the INSERT INTOBreaking down SQL...

www.niftyaccess.com

if you want further information.

Uncle Gizmo I actually followed your example in the 6 videos you lead me to earlier. That’s the way I will construct SQL statements from here on out. Thanks you have been a big help with me getting this accomplished.

 

[slharman1]

That's good to hear! At least I'm not wasting my time, well I am wasting time on the forum! Time to get back to work...

Can I use your comment on my testimonial page?

Testimonials - Nifty Access

Testimonials Tony Hine who operates under the handle "Uncle Gizmo" has received thanks and acknowledgement from many of the people he has helped. Tony really enjoys the process of sussing out the OP's problem and coming up with a suitable answer. I DON'T WANT TO BE HERE! - Click Here for Main...

www.niftyaccess.com

Absolutely! And thanks again!

 

[gemma-the-husky]

@Uncle Gizmo Very slick demo. I didn't realize for a moment that the strings were being manipulated to include the correct wrappers. I thought you wrapped it up without explaining that in enough detail perhaps. Anyway, I am sure I will look at some more Nifty suggestions.

 

[slharman1]

@Uncle Gizmo Very slick demo. I didn't realize for a moment that the strings were being manipulated to include the correct wrappers. I thought you wrapped it up without explaining that in enough detail perhaps. Anyway, I am sure I will look at some more Nifty suggestions.

He clearly and specifically spells it out in the video. It’s the only way a novice like me would’ve picked up on it. I had to watch the videos several times to comprehend what all is taking place. Problem is at my age I will forget most of it before I need it again.

 

[gemma-the-husky]

without wishing to change this thread - I generally use this for dates only

sqldate = "#" & format(ukdate,"long date") & "#"

I know everyone points out this does not work in all domains, but I just find it very easy. Long date is "8 April 2021", and therefore there is no ambiguity.

 

[slharman1]

One more problem:
I have added the other two tables via nested loops to my function, this is just part of the function to get values from 3 tables and insert them into 3 other tables,
The problem is in the values returned by the INSERT INTO statement, (I think)

  ' 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
                           
                            strItemNo = Chr(34) & strItemNo & Chr(34)
                            strModelNo = Chr(34) & strModelNo & 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

The code breaks at the last line: curDB.Execute strSQLQuoteDetailsInsert
Upon testing - the immediate window gives the following results.


?strSQLQuoteDetailsInsert
INSERT INTO tblOrderDetails(ItemNo, EstID, ModelNo, Description, Qty, Price, OrderID, AccessPrice) VALUES ("122", 6, "PSF30144-3R10-U6", "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, 2961.0539, 11, 224.0304)

I think it is the string below that is the result of the field strDescription from the strSQL statement that is causing the INSERT problem:

"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."
Any Ideas?

 

[slharman1]

So it would possibly be:-

' 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"

Nifty, Thanks!
So are you saying to only add this line of code where you show it?

 strDescription = Replace(strDescription, Chr(34), Chr(34) & Chr(34))

I am confused , doesn't this replac e

So it would possibly be:-

' 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"

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.

 

[slharman1]

So it would possibly be:-

' 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"

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:-

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.

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.

 

[slharman1]

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.

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?

 

[slharman1]

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.

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:

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.

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

 

[slharman1]

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.

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]

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 & "'"

 

[slharman1]

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 & "'"

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]

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.

 

[slharman1]

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.

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)

 

[slharman1]

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.

I am using both double quotes and single quotes in the text field that is the variable