Access VBA - Autocorrect changing code

Question

I thought I was going crazy but after testing it today I noticed that the Access VBA autocorrect seems to be changing my code.

I've found that when I put a function such as Date() in code with multiple lines and then move to a new line, some funky autocorrect changes my Date() to Date and moves and occasionally drops the last line of the remaining code.

Is there a way to prevent this from happening? I've had several lines of code that didn't work correctly after the autocorrect changed it.

Example:

    strSQL = "INSERT INTO tblCalls ( StuID, CallType, DateAdded, [USER], Comments ) " & _

                        "SELECT  StuID, CallType, '" & Date() & "' , [School], " & _

                        "'Start: " & Format(datStart, "MM/DD/YYYY") & "' " & _

             "FROM [tblPendTemp] WHERE ImportStatus IS NULL "

is changed automatically to

    strSQL = "INSERT INTO tblCalls ( StuID, CallType, DateAdded, [USER], Comments ) " & _

                        "SELECT  StuID, CallType, '" & Date & "' , [School], " & "'Start: " & _

                        Format(datStart, "MM/DD/YYYY") & "' " & "FROM [tblPendTemp] WHERE ImportStatus IS NULL " _

Answer 1

Unfortunately this is yet another bug that has been around for a while (since Office XP at least) that Microsoft refuses to fix. Even though it alters the user's code, Microsoft doesn't consider it important enough to spend any time to try and correct. I would be very surprised if this bug isn't in Access 2013 - which I have and hate more than 2010.

You can go into Tools -> Options and uncheck the Auto Syntax Check to stop autocorrect from working altogether but then you'd lose all the functionality of it.

I try to remember not to use the parenthesis and check my code after I use a function in a multi-line line of code and watch my code when moving to a new line. Be careful - if you add code without adding another new line, the next line of code will be appended to the end of the last line with the _ .

Answer 2

Scott,

I appreciate your responding to the post and the suggestion to check that I am not using Date as an object name.  I also read and appreciate Dirks response.  In my case neither explains my problem.  It is nice to have forums where we can discuss issues we find.  It is not a requirement for anyone to take their time to help others out, but it sure does help a lot of people.

I am sorry if I have offended you by posting on an older thread or if you think my question is silly, but I don't think that it is necessary to belittle someone.

Answer 3

First, please don't piggyback your question on another thread, especially a 2 year old one. 

Second, as Dirk pointed out this is a known behavior. Since the Date function doesn't require any parameters, Access will often eliminate the parentheses. This is normally not a problem if you are careful with your naming, i.e. not using Date as an object name.

Answer 4

This has just started happening to me, but it is now everywhere Date() is used.  But it is only on some of my users computers.  I write the code on my computer and it is fine.  when I move the app to a users computer, () is removed.  when I try to type date() into the vba on their computer, it autocorrects immediately.

Answer 5

Fascinating!  In 15 years of working with Access, I never noticed this bug.  Sure, I've always known that Access would remove the parentheses from references to the built-in Date() function (actually, it's defined as a property, which may partly explain it), but I never noticed it mangling the rest of the code.

I just tested in both Access 2010 and Access 2003, and it definitely does that to code like what you posted.  I wonder if the Access product team know it.