Need help with a SQL Append Query & Access VBA



Can someone help me to modify this append query?

----------------------------------------------------------------------

Private Sub cmdPublish_Click()
Dim SQL As String
SQL = "INSERT INTO boats ( StockNo, [Year], Make, Model ) VALUES ('STK', '1999', 'Parker', '2320')"
DoCmd.RunSQL SQL
End Sub

----------------------------------------------------------------------

OK- my ODBC connection to MySQL works. Now I want to put real data in.

Let's say I had either a control on a form or a field in a table. For example:

tblBoats
   StockNo (text)
   Year (int)
   Make (text)
   Model (text)

Or simply controls on a form containing values, for ex:

txtControlStockNo
txtControlYear
txtControlMake
txtControlModel


Can someone provide me with an example of the correct syntax within VBA to modify my append query?
LVL 2
wmilligaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
try like:

Private Sub cmdPublish_Click()
Dim SQL As String

strStockNo = replace$("" & txtControlStockNo.value,"'","''")
strYear = txtControlYear.value
strMake = replace$("" & txtControlMake.value,"'","''")
strModel = replace$("" & txtControlModel.value,"'","''")

SQL = "INSERT INTO boats ( StockNo, [Year], Make, Model ) VALUES (" & strStockNo & ", " & strYear & ", '" & strMake & "', '" & strModel & "')"
DoCmd.RunSQL SQL

End Sub
0
Ryan ChongCommented:
oops, the statement should be as:

SQL = "INSERT INTO tblBoats ( StockNo, [Year], Make, Model ) VALUES (" & strStockNo & ", " & strYear & ", '" & strMake & "', '" & strModel & "')"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Arthur_WoodCommented:
ryancs - shouldn't it be:

SQL = "INSERT INTO tblBoats ( StockNo, [Year], Make, Model ) VALUES ('" & strStockNo & "', " & strYear & ", '" & strMake & "', '" & strModel & "')"


StockNo is declared as Text, so the '...' are needed around strStockNo.


AW
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MikeAHoganCommented:
I'm not sure if you want to go through the trouble of setting everything to variables before adding into your query, if you dont, just use this syntax
                           
Private Sub cmdPublish_Click()
Dim SQL As String                            
SQL = "INSERT INTO tblBoats ( StockNo, [Year], Make, Model ) VALUES ('" & me!
txtControlStockNo & "', " & me!txtControlYear & ", '" & me!txtControlMake & "', '" & me!txtControlModel & "')"
DoCmd.RunSQL SQL
End Sub


By the way, you should maybe look at changing the field name "Year" to something a little different like intYear or Yr  
Year() is a vba fucntion and could cause problems down the road.
0
MikeAHoganCommented:
Or if you're concerned about users entering ' in the text fields change the quatations to this

SQL = "INSERT INTO tblBoats ( StockNo, [Year], Make, Model ) VALUES (""" & me!
txtControlStockNo & """, " & me!txtControlYear & ", """ & me!txtControlMake & """, """ & me!txtControlModel & """)"
0
Ryan ChongCommented:
Arthur_Wood , you're right, my mistake there! ;-)
0
Arthur_WoodCommented:
just wanted to avoid a follow up question from wmilliga when the SQL threw an error.   I have great confidence in you, ryancs.

AW
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.