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.
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)
SELECT [me]![customer_id] , strNotes , Now() , currentuser;
Trisha
padinka@yahoo.com
RE: SQL error (expected end of statement)
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)
"What a wonderfull world" - Louis armstrong
RE: SQL error (expected end of statement)
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)
try this it should be better
"What a wonderfull world" - Louis armstrong
RE: SQL error (expected end of statement)
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)
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)
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)
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)
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)