SQL error (expected end of statement)

[cruford]

Here is the code I have for this form I'm working on. In the strSQL I get a compile error that says "Expected: End of statement". The INTO word is highlighted, if I remove everything after INSERT I don't get an error but that does me no good.


Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_Update

Dim strNotes As String
Dim strSQL As String
strSQL = INSERT INTO [Patient Notes] ([customer_id], [Notes], [Timestamp], [Initials]) VALUES ([me]![customer_id], strNotes, now(), currentuser)
strNotes = InputBox("Please enter what changes you made", "Update Record&quot
DoCmd.RunSQL strSQL

Exit_Form_Update:
Exit Sub

Err_Form_Update:
MsgBox Err.Description
Resume Exit_Form_Update

End Sub


On this main form (frmPatients)is a subform (Patient Notes Subform). When the record is updated I want the program to insert a new record in the "Patient Notes" table with whatever they put in the input box, timestamp is automatic, and initials using the currentuser() function, and customer ID populating from the customer id currently on the form. Any help would be greatly appriciated. I am using Access 2000.

 

[padinka]

INSERT INTO [Patient Notes] ( customer_id, Notes, [Timestamp], Initials )
SELECT [me]![customer_id] , strNotes , Now() , currentuser;
Trisha
padinka@yahoo.com

 

[cruford]

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?

 

[chrissie1]

strSQL = "INSERT [Patient Notes] ([customer_id], [Notes], [Timestamp], [Initials]) VALUES (" & [me]![customer_id] & ",'" & strNotes & "'," & now() & "," & '" & currentuser& "')"
"What a wonderfull world" - Louis armstrong

 

[cruford]

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!

 

[chrissie1]

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

 

[chrissie1]

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

 

[cruford]

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.

 

[chrissie1]

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

 

[cruford]

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 )

 

[cruford]

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.

 

[Jorgandr]

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.