Compile Error Expected End Of Statement Access Vba
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads
Access Vba Compile Error Expected =
with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the vba compile error expected end sub Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
Compile Error Expected End Sub Excel Vba
Sign up compile error: expected end of statement up vote 0 down vote favorite A Microsoft Access 2010 database is giving me the following error message: Compile Error: Expected End Of Statement Here is the method that is throwing the error vba compile error expected array message: Private Sub Form_BeforeUpdate(Cancel As Integer) 'Provide the user with the option to save/undo 'changes made to the record in the form If MsgBox("Changes have been made to this record." _ & vbCrLf & vbCrLf & "Do you want to save these changes?" _ , vbYesNo, "Changes Made...") = vbYes Then DoCmd.Save Else DoCmd.RunCommand acCmdUndo End If Dim sSQL As String sSQL = "SELECT max(Clients.ClientNumber) AS maxClientNumber FROM Clients" Dim rs As DAO Recordset Set rs = CurrentDb.OpenRecordset(sSQL) MsgBox ("Max client number is: compile error expected expression vba " & rs.Fields(1)) End Sub The line of code that is throwing the error message is: Dim rs As DAO Recordset I am not sure if the problem has to do with the syntax of what is on the line preceding it. Can anyone show how to fix this problem? And explain what is going on? vba ms-access access-vba ms-access-2010 share|improve this question asked Oct 23 '13 at 1:08 CodeMed 1,0111895168 add a comment| 2 Answers 2 active oldest votes up vote 3 down vote accepted You are missing a full stop (period) between the DAO and the Recordset - it should be Dim rs As DAO.Recordset Beyond that, you will also have a runtime error on reading the field value, since a DAO Fields collection is indexed from 0, not 1. Hence, change the penultimate line to this: MsgBox ("Max client number is: " & rs.Fields(0)) Alternatively, reference the field by its name: MsgBox ("Max client number is: " & rs!maxClientNumber) share|improve this answer answered Oct 23 '13 at 1:23 Chris Rolliston 3,6501716 2 No, that wasn't my suggestion - read it again... Use either rs.Fields(0) or rs!maxClientNumber - your rs!Fields(0) is a muddle. –Chris Rolliston Oct 23 '13 at 1:37 If anyone is wondering that was a reply to a comment that's now been deleted, though the point still stands (you can't index by position using the bang operator)... –Chris Rolliston Oct 23 '13 at 11:01 That was not
resources Windows Server 2012 resources Programs MSDN subscriptions Overview Benefits Administrators Students Microsoft
Compile Error Expected End Of Statement Macro
Imagine Microsoft Student Partners ISV Startups TechRewards Events Community compile error expected end of statement visual basic Magazine Forums Blogs Channel 9 Documentation APIs and reference Dev centers Retired content Samples
Compile Error Expected End Of Statement Vba
We’re sorry. The content you requested has been removed. You’ll be auto redirected in 1 second. Visual Basic Reference Visual Basic Language http://stackoverflow.com/questions/19531039/compile-error-expected-end-of-statement Reference Error Messages Error Messages End of statement expected End of statement expected End of statement expected '#ElseIf' must be preceded by a matching '#If' or '#ElseIf' '#Region' and '#End Region' statements are not valid within method bodies/multiline lambdas '
your question and get tips & solutions from a community of 418,469 IT Pros & Developers. It's quick & easy. Expected: End of Statement P: 17 boliches64 Hi All I am getting https://bytes.com/topic/access/answers/849980-expected-end-statement the "Expected: End of Statement" message from my code: strSQL = strSQL & "DLookUp("[ID]", http://www.pcreview.co.uk/threads/insert-into-gets-compile-error-expected-end-of-statement.3765327/ "qryListCopiesToDelete", "[ID]" >1)))" "[ID]" gets highlighted as an error! If I remove the quotation marks "qryListCopiesToDelete" becomes the problem etc. When I remove all quotes from the above script the warnings go but the script fails to work! How do I get the DLookup to read the query? All queries were built in Query Design and work as compile error plain queries, but when I place the code into vb - NOTHING! Help please, I have spent ages on the web but to no avail. Oct 30 '08 #1 Post Reply Share this Question 11 Replies Expert Mod 2.5K+ P: 2,543 Stewart Ross Hi. Two problems with your statement: the first being that to include double quotes within a string you need to repeat the double quote character (it is after all a compile error expected delimiter in VBA, and the VBA interpreter is seeing the end of your string as occurring just before your reference to the ID in your DLookup). The second error, which I also saw in a similar post you made earlier on this topic, is that your comparison statement is incorrect - the > 1 part is outside of the comparison. Sorting these errors out, your StrSQL setup for the DLookup becomes: Expand|Select|Wrap|Line Numbers strSQL=strSQL&"DLookUp(""[ID]"",""qryListCopiesToDelete"",""[ID]>1"")))" which becomes Expand|Select|Wrap|Line Numbers DLookUp("[ID]","qryListCopiesToDelete","[ID]>1"))) when run. I am not convinced that this will do what you expect, however, as it is returning one ID from the set of all matching rows where the ID is > 1. I doubt very much that the one returned will in all cases be the one you need, but that is for you to sort out. -Stewart Oct 30 '08 #2 reply Expert Mod 15k+ P: 29,922 NeoPa In any case, quotes within SQL should be single-quotes rather than double-quotes (technically speaking - see Quotes (') and Double-Quotes (") - Where and When to use them). This would avoid the problem altogether (although not fix your logic - See Stewart's post). Oct 30 '08 #3 reply P: 17 boliches64 Thanks Stewart (and NeoPa), the error message has gone away!! BUT as you predicted the sc
Guest I'm just trying to add a new record in a table consisting of two fields System_ID and SerialNumber. My SQL statement is as follows: DoCmd.RunSQL "INSERT INTO tblSystems(System_ID, SerialNumber) VALUES(" & strIDnum & ", " & strSNnum& ")" This gives me an Expected end of statement compile error. I've tried it several other ways and get the same error. ToppHatt, Mar 10, 2009 #1 Advertisements Tom van Stiphout Guest On Tue, 10 Mar 2009 13:59:01 -0700, ToppHatt <> wrote: You are short on spaces. For example you need one between tblSystems and (. One way to get this straight is to set a breakpoint at this line, get the whole sql statement, paste it into a new query in sql view, and switch to design view. The parser will complain if there is a syntax error. -Tom. Microsoft Access MVP >I'm just trying to add a new record in a table consisting of two fields >System_ID and SerialNumber. My SQL statement is as follows: > >DoCmd.RunSQL "INSERT INTO tblSystems(System_ID, SerialNumber) VALUES(" & >strIDnum & ", " & strSNnum& ")" > >This gives me an Expected end of statement compile error. I've tried it >several other ways and get the same error. Tom van Stiphout, Mar 11, 2009 #2 Advertisements Tony Chacon Guest Your code, now modified to run syntactically correct in Access SQL ToppHat, this may be a bit late for your issue, but it may help others in search of the same problem. I modified your code to include the following string elements and this worked when I tested it. Dim strSql As String strSql = "INSERT INTO tblSystems([System_ID], [SerialNumber]) " _ & " VALUES('" & strIDnum & "'," & "'" & strSNnum& & "');" DoCmd.RunSQL strSql MsgBox ("New record added") ToppHat wrote: INSERT INTO gets compile error: Expected end of statement. 10-Mar-09 I'm just trying to add a new record in a table consisting of two fields System_ID and SerialNumber. My SQL statement is as follows DoCmd.RunSQL "INSERT INTO tblSystems(System_ID, SerialNumber) VALUES(" & strIDnum & ", " & strSNnum& ") This gives me an Expected end of statement compile error. I've tried it several other ways and get the same error. Prev