wmilliga
asked on
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
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.
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 & """)"
SQL = "INSERT INTO tblBoats ( StockNo, [Year], Make, Model ) VALUES (""" & me!
txtControlStockNo & """, " & me!txtControlYear & ", """ & me!txtControlMake & """, """ & me!txtControlModel & """)"
Arthur_Wood , you're right, my mistake there! ;-)
just wanted to avoid a follow up question from wmilliga when the SQL threw an error. I have great confidence in you, ryancs.
AW
AW
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