If Exists Insert Else Update with SQL

[jonggg]

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 a sql statement to do this 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

 

[PHV]

I'd use a Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JBinQLD]

G'day fella,

Try:

Dim strSQL As String

if nz(dlookup("col1", "Table1", "col1='" & Me.txtcol1 & "'"),"") <> "" then

'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& ""

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 & "')

endif

If txtcol1 = "" Then
MsgBox "Please Enter col1 Number"
Else
'CurrentDb.Execute strSQL, dbFailOnError
DoCmd.RunSQL strSQL, dbFailOnError
End If
End Sub

Good luck,

JB

 

[jonggg]

JBinQLD,
Thats great, I just had to fix some sytax error of my own and now its working well. thanks a mil.

-jon

 

[JBinQLD]

Good luck with the project!

JB