CurrentDB.Execute insert working just once

[krausr79]

I have a form with a combobox and a text box. I am running an insert query to add the data as a line. It adds the data alright the first time, but on any run after, it doesn't work. If I close and reopen the form, it will work once again. All code runs each time, but I don't get the insert

Private Sub btnAdd_Click()
'buffer values into variables to use exit function statements instead of end
Dim S As String
S = TheSystem
If S = "" Then Exit Sub
Dim N As String
N = TheNote


If N = "" Then Exit Sub
CurrentDb.Execute "insert into tblNotes values('" & S & "','" & Now() & "','" & N & "')"
Me.txtNote.Value = "Note added"
Me.Refresh
Dim WaitTill As Date
WaitTill = DateAdd("s", 1, Now())
Do Until Now() > WaitTill
DoEvents
Loop
Me.txtNote.Value = ""
End Sub

Private Function TheSystem() As String
If Nz(Me.cmbSystem, "") = "" Then
MsgBox ("Select a system")
TheSystem = ""
Exit Function
End If

TheSystem = Me.cmbSystem
End Function
Private Function TheNote() As String
If Trim(Nz(Me.txtNote, "")) = "" Then
MsgBox ("Enter note")
TheNote = ""
End If
If InStr(1, Me.txtNote, "'") Then
Dim YouSure As VbMsgBoxResult
YouSure = MsgBox("Having Apostrophes in notes will booger up the database. Replace all apostrophes' with accent marks`?", vbYesNo)
If YouSure <> vbYes Then End
Me.txtNote.Value = Replace(Me.txtNote.Value, "'", "`")
End If
TheNote = Me.txtNote
End Function

[Perceptus]

Having apostrophies in your data is messing it up because of how you are inserting data. If you a recordset Object with the Add new Method. you should be able to fix this. Have you tried something of this manner?

Dim RS as recordset
set rs = currentdb.openrecordset("select * from tblNotes")
rs.addnew
rs!fieldname = fieldvalue
rs!otherfieldname = otherfieldvalue
rs.update
set rs = nothing

[krausr79]

When I used the recordset, it gave me an error message I didn't recieve with the .execute. I had just left one of the fields as primary key when I didn't want to. How silly!

[pbaldy]

FYI, you could have gotten the error message with the Execute method, you just didn't ask for it. It would look like:

CurrentDb.Execute "insert into tblNotes values('" & S & "','" & Now() & "','" & N & "')", dbFailOnError

It's optional, since sometimes you don't want the warning.

[Perceptus]

inserting data as text into a sql command really kind of leaves you open for a sql injection attack i think. I try to avoid using them whenever possible.

You may also be able to do

CurrentDB.RecordsEffected after the execute statement. to see if the number was greater than 0.

btw, you need to declare a variable as Static in order for it to act as a buffer within a Procedure, otherwise you will want to have the buffer declared within the class/module