Contact US
Thanks. We have received your request and will respond promptly.
Come Join Us!
- 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!
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden.
SQL error (expected end of statement)
- Forum
- Search
- FAQs
- Links
- MVPs
SQL error (expected end of statement)
SQL error (expected end of statement)
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
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)
RE: SQL error (expected end of statement)
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)
"What a wonderfull world" — Louis armstrong
RE: SQL error (expected end of statement)
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)
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)
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:
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
but we are getting warm
"What a wonderfull world" — Louis armstrong
RE: SQL error (expected end of statement)
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)
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
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)
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
Thread: [RESOLVED] Expected end of statement
Hyperactive Member 
Join Date Jan 2001 Posts 430
[RESOLVED] Expected end of statement
[1025] Microsoft VBScript compilation error: Expected end of statement at line:7, char:12 near: Dim objConn As ADODB.Connection
- View Profile
- View Forum Posts
Super Moderator 
Join Date Jul 2002 Location Bristol, UK Posts 41,888
Re: Expected end of statement
VBScript does not support data types as such (and wouldn’t support that one without a reference anyway).. so remove all data types from your declarations. For example, that line should be just: Dim objConn
Note also that the Enum’s (like adCmdText etc) are not defined unless you have a reference to ADO (and I’m fairly sure you can’t have references), so you will need to define all of those too.
One more point, there is no reason to use Function’s as opposed to Sub’s, as you aren’t returning a value from either of them.
Expected end of statement что за ошибка
This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.
Answered by:
Question
Hi i recently have played around with Visual Basic, but every time i attempt to start debugging, i get an error saying " End of statement expected, line 2 column 161" just so you know, it’s an attempt at a Auto Clicker.
here is the code if it helps:
Public Class Form1
Private Declare Sub mouse_event Lib "user32" (ByVal dwflags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cbuttons As Long, ByVal dwExtraInfo As Long) Private Const mouseclickup = 4 Private Const mouseclickdown = 2 Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vkey As Long) As Integer
VBA: Compile error: Expected end of statement
I can't proceed and get the compile error, the "no data" seems to be the problem, but I need it for the if-statement.
Any idea how to solve that?
Excel Facts
Peter_SSs
MrExcel MVP, Moderator
- 365
- Windows
- Jun 18, 2008
- #2
marco84
New Member
- Jun 18, 2008
- #3
Thanks a lot
marco
Peter_SSs
MrExcel MVP, Moderator
- 365
- Windows
- Jun 18, 2008
- #4
Thanks a lot
marco
By the way, Welcome to the MrExcel board!
VBA Newbe
New Member
- Mar 5, 2009
- #5
I'm new to MrExcel and I have a similar challenge!
I have written the following code and receive a 'Compile error: Expected end of statement' message too.