Update or Insert Table from txtbox

[jongerb]

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.

Private Sub Command93_Click()
 
Dim strSQL As String
 
strSQL = "IF EXISTS ( SELECT col1FROM Table1 WHERE col1= " & Me.txtcol1& ") BEGIN"
 
'Update Statment
strSQL = strSQL & " Update Table1 SET col2 = '" & Me.txtcol2 & "' "
strSQL = strSQL & "col3 = '" & Me.txtcol3 & "', "
strSQL = strSQL & "col4 = '" & Me.cbocol4 & "', "
strSQL = strSQL & "col5 = '" & Me.txtcol5 & "', "
strSQL = strSQL & "col6 = '" & Me.txtcol6 & "', "
strSQL = strSQL & "col7 = '" & Me.txtcol7 & "' "
strSQL = strSQL & "WHERE col1= " & Me.txtcol1& ""
 
strSQL = strSQL & " Else "
'Insert Statement
strSQL = strSQL & "INSERT INTO Table1 ( col1, col2, col3, col4, col5, col6, col7) "
strSQL = strSQL & "Values ('" & Me.txtcol1 & "', "
strSQL = strSQL & "'" & Me.txtcol2 & "', "
strSQL = strSQL & "'" & Me.txtcol3& "', "
strSQL = strSQL & "'" & Me.cbocol4 & "', "
strSQL = strSQL & "'" & Me.txtcol5 & "', "
strSQL = strSQL & "'" & Me.txtcol6 & "', "
strSQL = strSQL & "'" & Me.txtcol7 & "') END"
 
If txtcol1 = "" Then
MsgBox "Please Enter col1 Number"
Else
'CurrentDb.Execute strSQL, dbFailOnError
DoCmd.RunSQL strSQL, dbFailOnError
End If
End Sub

[NeoPa]

There is no "IF EXISTS" construct in Jet SQL.

I suggest you do the checking in VBA then create the SQL accordingly.

[NeoPa]

Let me know if you need any help with that.

Concepts or coding :)

Welcome to Bytes!