INSERT INTO [Patient Notes] ( customer_id, Notes, [Timestamp], Initials )
SELECT [me]![customer_id] , strNotes , Now() , currentuser;

Trisha
padinka@yahoo.com

Thanks for the quick reply I tried modifying the statement like your and still get this:

Compile error:
Expected: End Of Statement

Then it highlights the INTO word.  I am new to this is there something I need to turn on in Access to get it to recognize this keyword?

strSQL = "INSERT [Patient Notes] ([customer_id], [Notes], [Timestamp], [Initials]) VALUES (" & [me]![customer_id] & ",'" & strNotes & "'," & now() & "," & '" & currentuser& "')"

"What a wonderfull world" - Louis armstrong

Chrissie1,
I tried this statement and the single quote ' in front of the '"& currentuser& "')" kicks back an error:

Compile Error:
Expected: Expression

If I remove that there is no more error however, on the form I get a message when the beforeupdate event fires:

"Database can't find the field "|" referred to in your expression"

I went one step further and removed the quotes and &'s from the statement which will run then gives this error:

"Syntax error in INSERT INTO statement"

Thanks for any help you all can give!

strSQL = "INSERT [Patient Notes] ([customer_id], [Notes], [Timestamp], [Initials]) VALUES (" & [me]![customer_id] & ",'" & strNotes & "'," & now() & ",'" & currentuser& "')"

try this it should be better

"What a wonderfull world" - Louis armstrong

just a little comment

string or text should be between single quotes and values should not be between single quotes

"What a wonderfull world" - Louis armstrong

Today must not be my day.  I copied the code from your last post and now I get the Compile Error "Expected: end of statement" error again and it highlights the part in red here:

strSQL = "INSERT [Patient Notes] ([customer_id], [Notes], [Timestamp], [Initials]) VALUES (" & [me]![customer_id] & ",'" & strNotes & "'," & now() & ",'" & currentuser& "')"


Thanks for the tip on quotes I did not know that.

strSQL = "INSERT [Patient Notes] ([customer_id], [Notes], [Timestamp], [Initials]) VALUES (" & [me]![customer_id] & ",'" & strNotes & "'," & now() & ",'" & currentuser & "')"

are you sure all the function return values, and it is possible that the now thing must also be between two single quotes, can't really try it here at home

strSQL = "INSERT [Patient Notes] ([customer_id], [Notes], [Timestamp], [Initials]) VALUES (" & [me]![customer_id] & ",'" & strNotes & "','" & now() & "','" & currentuser& "')"

but we are getting warm

"What a wonderfull world" - Louis armstrong

Yes we are getting warm, I tried something a little different and maybe you know what I'm doing wrong.  Here is my new SQL statement that is almost working:

strSQL = "INSERT INTO [Patient Notes] VALUES ([me]![Customer_ID],strNotes,Now(),currentuser)"

Now when the beforeupdate fires I get a prompt asking me for the values of [me]![Customer_ID] and another prompt for strNotes.  If I fill in values when prompted for these the new record is added.  But I want them to be populated from the input box.  

The [me]![Customer_ID] should be pulling the customer Id from the form, strNotes should be what ever they type in the Input Box that pops up, now() should return the current date/time, and currentuser should pull back the current person logged into the database (i'm sure you knew those, i just wanted to clarify :) )

Well this is what I have come up with to get by for now.  The SQL statement would not pass the value form strNotes into the database so I just bypassed it all together, although its not pretty:


Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_Update
  
  Dim strSQL As String
  strSQL = "INSERT INTO [Patient Notes] VALUES (customer_id , 'Updated Account - ' & EnterChanges , Now(), currentuser)"
  DoCmd.RunSQL strSQL
  
Exit_Form_Update:
  Exit Sub
  
Err_Form_Update:
  MsgBox Err.Description
  Resume Exit_Form_Update
  
End Sub


The EnterChanges part gives the user a prompt to enter in what changes they made, then it inserts "Updated Account" before that and inserts in a new record with the customer id and time/date stamp.  Thanks for all your help, if you come up with a cleaner solution please post it.

Your strNotes line would have worked if you had placed it before your SQL statement. because the sql statement is being run before your are setting a value to strNotes it is not going update your record with that value.  In fact it will try to insert a null value for that field.