Insert Into

[doran_doran]

insert into sql error

What is wrong with the following sql statement. Please see print screen for error.

My Goal: I am trying to grab user id, user name and today's date into a memo field call VisitorTrail. I would like this field to grow. So, data is added to old data in the field.

Also, How do I put extra 3 spaces between this value and "|" between them.

= = = = Code Begins Here = = = =
Private Sub Note_AfterUpdate()
Dim NoteBefore2 As String
Dim NoteAfter2 As String
Dim strSQL As String

NoteBefore2 = Nz(Me.Note.OldValue, "Null")
DoCmd.RunCommand acCmdSaveRecord
NoteAfter2 = Nz(Me.Note, "Null")

strSQL = "INSERT INTO tblNotes (visitorTrail)"
strSQL = strSQL & " Select " & Me!txtUserID & Me!txtDLookupAuthorName & Now() & Chr(10)
strSQL = strSQL & "FROM tblNotes"
strSQL = strSQL & "WHERE [NotesAutoId]=" & Me.txtNotesAutoID & ";"

If NoteBefore2 <> NoteAfter2 Then
Me!LastVisitorID = Me.txtUserID
Me!LastVisitorName = Me.txtDookupAuthorName
Me!LastVisitedDate = Now()

DoCmd.RunSQL strSQL

End If

End Sub

 

[grnegsndhm]

Field Names

You have a select and then a dynamic field name being pulled?

If you do not have field named "u54gp4pMoh..." in table notes then you should be selecting the value of the column?

If you want to pull in that value as a note then:
"insert into tbl (fld) values ('" & note & "')"

Hope this helps

 

[WayneRyan]

doran,

strSQL = "Update tblNotes " & _
         "Set    VisitorTrail = Visitor_Trail & vbCrLf & _
                                Me!txtUserID & " | " & _
                                Me!txtDLookupAuthorName & " | " & _
                                Now() & " " & _
         "WHERE [NotesAutoId]=" & Me.txtNotesAutoID & ";"

Wayne

 

[doran_doran]

Where Clause...

I tried following, It runs smoothly and data gets populated in a new record

strSQL = "INSERT INTO tblNotes ([VisitorTrail]) VALUES('" & Me!txtUserID & " " & txtDLookupAuthorName & " " & Now() & vbNewLine & "');"

How do I implement where clause

Thanks

= = = Wayne's Code = = =
strSQL = "Update tblNotes " & _
"Set VisitorTrail = Visitor_Trail & vbCrLf & _
Me!txtUserID & " | " & _
Me!txtDLookupAuthorName & " | " & _
Now() & " " & _
"WHERE [NotesAutoId]=" & Me.txtNotesAutoID & ";"

 

[doran_doran]

working version but lil glitch...

strSQL = "UPDATE tblNotes SET tblNotes.[VisitorTrail] = tblNotes.[VisitorTrail] & '" & Me!txtUserID & " " & txtDLookupAuthorName & " " & Now() & vbNewLine & "' WHERE tblNotes.[NotesAutoId]=" & Me.txtNotesAutoID & ";"

The code works fine.

But i get write conflict error when i exit the form so i can get back to my main form.

 

[doran_doran]

Finally it's done

Ok, I finally figured it out. I am so shocked that NO ONE helped me or even gave me the good direction.

=== working version of the code = = =

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim NoteBefore2 As String
Dim NoteAfter2 As String

NoteBefore2 = Nz(Me.Note.OldValue, "Null")
NoteAfter2 = Nz(Me.Note, "Null")

If NoteBefore2 <> NoteAfter2 Then
Me.VisitorTrail = Me.VisitorTrail & Me!txtUserID & " " & txtDLookupAuthorName & " " & Now() & vbNewLine
Me!LastVisitorID = Me.txtUserID
Me!LastVisitorName = Me.txtDLookupAuthorName
Me!LastVisitedDate = Now()
End If


End Sub

 

[WayneRyan]

d,

"I am so shocked that ..." you can't define your problem!
"I am so shocked that ..." you can't differentiate between INSERT/UPDATE.

Good luck in your future endeavors,
Wayne

 

[doran_doran]

Thanks...

Hi Wayne,

Actually, the Silicon Chips inside my head was switch to overload and I was unable to think. Can you believe that ? LOL LOL

Thanks for waking me up. I am shock myself. Later Buddy.. Thanks for all your help.

Moe