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

Try:

CurrentDB.Execute "INSERT INTO [Test Table] ( [Field1], [Field2] ) VALUES('" & Forms![Test Form]![txtValue1] & "', #" & Date() & "#);"

Each version of Access has been less and less forgiving of sloppy coding. And, IMHO, what you had is sloppy coding. A SELECT clause usually needs a FROM clause. The Values clause is better used when you are adding a single record using values from a form and/or expressions.

As far as the people you referred to, when asking questions on a forum like this people need to have a thick skin. The written word only conveys about 30% of communication. Without the in person additions of facial expressions, tone of voice and body language the written word may seem harsher than intended. For example, I referred to sloppy coding. This is not meant to belittle or put down. I don't know if you wrote that code or someone else did. But there are tighter ways to code that (as I illustrated). There are lots of examples of code that worked fine in older versions but result in syntax errors in newer versions.

Answer 2

I apologize that I misjudged your intentions.  I actually do not feel belittled, I have been around long enough to know that no matter how much I learn I am still light-years away from knowing it all.  It is just that the way it was worded seemed a bit harsh and someone who is new might get discouraged from contributing or asking questions in the future.  I do not spend a lot of time in the forums but do personally work with people to assist them with the issues I have worked with in the past.  When I ask if they have asked questions in one of the Microsoft forums several of them responded that they have been made to feel stupid for asking or not knowing the lingo.

The Date function is causing an error and data is being lost.  I chose this thread because it described the same anomaly and though it did not have a clear answer the people who were involved would probably be notified and may have new information.

it seems to be an obscure problem and I felt I would have better luck this time tapping into the people who had seen or looked into it before.

I really do appreciate those of you who dedicate so much time to helping people in the forums, I am just a little quick to combat the stereotype of the condescending IT guy.

Answer 3

This project is one that has been dragged along from office version to office version since 2000.  I have been using Office 2010 for a while but the users all use Office 2013 and figured it could have been an issue with the references even though no one is missing a reference. 

After more testing the issue seems to be confined to when I run SQL  commands and not just the use of Date for example

This works fine.

Dim x As String

x = Date

This is changed when running on a users computer to remove the () from Date.

DoCmd.RunSQL "Insert Into [Test Table] ( [Field1], [Field2] ) Select Forms![Test Form]![txtValue1], Date();"

When this is run on the users computer it raises an error saying that it is missing ")" from "Date("

I started the Compile and I have some errors coming from code for unused forms and buttons so I will work through all of that and let you know.

I also plan on uninstalling and reinstalling some of the 3rd party applications that have references that are used.

Answer 4

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.

You didn't offend me. Reviving an old thread oftens means that people won't look at your question because they don't realize its a new or followup question. Plus, it can get confusing trying to a follow the answers to different questions in the same thread. 

I'm sorry if you felt belittled, but I see nothing belittling about my response. I was simply trying to give you advice for your future posting. 

My question to you is whether your use of the Date function returns the expected result (i.e. today's date) or not. If it does, then the issue is really moot. It doesn't really matter how it appears in VBA as long as it gets the right result.

Answer 5

I also read and appreciate Dirks response.  In my case neither explains my problem.

It's generally unwise to attach a new question to an old thread;  I'm sure Scott didn't mean to belittle you, just give you advice.

What is odd in your report is that you say Access doesn't remove the parentheses after Date when you write it in VBA on your own computer, though it does so on other computers.  I have never known Access *not* to remove the parentheses after "Date" in VBA code.  This is its normal and expected behavior.  The issue that started this thread was a bug following on from that behavior, not the behavior itself.

Do you by any chance have a broken reference in the database on your computer?  Does the entire VBA project compile properly when you click Debug -> Compile?