×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL error (expected end of statement)

SQL error (expected end of statement)

SQL error (expected end of statement)

(OP)
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")
  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.

RE: SQL error (expected end of statement)

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


Trisha
padinka@yahoo.com

RE: SQL error (expected end of statement)

(OP)
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?

RE: SQL error (expected end of statement)

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

"What a wonderfull world" - Louis armstrong

RE: SQL error (expected end of statement)

(OP)
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!

RE: SQL error (expected end of statement)

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

RE: SQL error (expected end of statement)

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

RE: SQL error (expected end of statement)

(OP)
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.

RE: SQL error (expected end of statement)

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

RE: SQL error (expected end of statement)

(OP)
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 :) )

RE: SQL error (expected end of statement)

(OP)
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.

RE: SQL error (expected end of statement)

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close