By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,852 Members | 2,116 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,852 IT Pros & Developers. It's quick & easy.

Update or Insert Table from txtbox

P: 1
Hello,
I've been working on taking data from a form and inserting it to a table if the record doesn't exist and updating it if the record does exists. All the data is in textboxes and a comboboxes. I have button that when clicked runs an sql statement in visual basic. When I click the button error '3129' appears stating that "Invalid SQL statement; Expected INSERT, DELETE, UPDATE ..." Please help.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command93_Click()
  2.  
  3. Dim strSQL As String
  4.  
  5. strSQL = "IF EXISTS ( SELECT col1FROM Table1 WHERE col1= " & Me.txtcol1& ") BEGIN"
  6.  
  7. 'Update Statment
  8. strSQL = strSQL & " Update Table1 SET col2 = '" & Me.txtcol2 & "' "
  9. strSQL = strSQL & "col3 = '" & Me.txtcol3 & "', "
  10. strSQL = strSQL & "col4 = '" & Me.cbocol4 & "', "
  11. strSQL = strSQL & "col5 = '" & Me.txtcol5 & "', "
  12. strSQL = strSQL & "col6 = '" & Me.txtcol6 & "', "
  13. strSQL = strSQL & "col7 = '" & Me.txtcol7 & "' "
  14. strSQL = strSQL & "WHERE col1= " & Me.txtcol1& ""
  15.  
  16. strSQL = strSQL & " Else "
  17. 'Insert Statement
  18. strSQL = strSQL & "INSERT INTO Table1 ( col1, col2, col3, col4, col5, col6, col7) "
  19. strSQL = strSQL & "Values ('" & Me.txtcol1 & "', "
  20. strSQL = strSQL & "'" & Me.txtcol2 & "', "
  21. strSQL = strSQL & "'" & Me.txtcol3& "', "
  22. strSQL = strSQL & "'" & Me.cbocol4 & "', "
  23. strSQL = strSQL & "'" & Me.txtcol5 & "', "
  24. strSQL = strSQL & "'" & Me.txtcol6 & "', "
  25. strSQL = strSQL & "'" & Me.txtcol7 & "') END"
  26.  
  27. If txtcol1 = "" Then
  28. MsgBox "Please Enter col1 Number"
  29. Else
  30. 'CurrentDb.Execute strSQL, dbFailOnError
  31. DoCmd.RunSQL strSQL, dbFailOnError
  32. End If
  33. End Sub
Sep 4 '08 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,470
There is no "IF EXISTS" construct in Jet SQL.

I suggest you do the checking in VBA then create the SQL accordingly.
Sep 5 '08 #2

NeoPa
Expert Mod 15k+
P: 31,470
Let me know if you need any help with that.

Concepts or coding :)

Welcome to Bytes!
Sep 5 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.