Link to home
Start Free TrialLog in
Avatar of wmilliga
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?
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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.
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 & """)"
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