Access Changes Upper to Lower Case in VBA

Question

Hello:

I am puzzled why the first letter of a data element in Access, which is defined as upper case in the table and the form, is changed to lower case in VBA.  Even if I enter upper case in the VBA code, as soon as I move to another location, it returns to lower case.

The data element is upper case in the table, in the form, but for some reason changes to lower case.

For example, here is some simple code:

Private Sub B12_500Code_AfterUpdate()
If IsNull(Me.b12_500Code) Or Not IsNumeric(Me.b12_500Code) Then
    MsgBox ("The Value Entered Was Not Numeric - Defaulted To Zero" & vbCrLf & "Please Re-enter")
    Me.b12_500Code = 0
End If
End Sub

Notice the lower case b in Me.b12_500Code.  This is the only place I can find a lower case.  How can I reset it to refresh it's memory to use upper case for the "B"?

Thanks,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 1

Notice the lower case b in Me.b12_500Code.

Hi RichLocus,

The code is case-insensitive, so don't worry, all works the same.

Probably you have declared a variable    b12_500Code   with lower case b somewhere in your code. When you change that variable to upper case b, that "token" is adjusted all through your code.

You can also declare the variable    B12_500Code    in the current AfterUpdate Sub, move to an other line, and remove that declaration again.

Imb.

Answer 2

I've experience this type of behavior as well, no clue as to why it happens, but it doesn't impact functionality.

 

Similarly, I've seen my code Declared as

    DAO.Database

changes to

    dao.Database

for no reason.

 

Something with the VBA editor, God only knows what though.

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 3

Something with the VBA editor, God only knows what though.

Hi Daniel,

It is a consequence of how VBA parses the code.

Imb.

Added: Just make a new declaration with the right capitals in it, move to the next line, and then delete the declaration again.

Imb.

Answer 4

Redoing the declaration do not resolve the issue.  I've tried that long ago.  The Editor is tempermental and sometimes behave oddly, it's simply part of life.  This has been discussed before and no has ever been able to find the root cause or solution.

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 5

I've experience this type of behavior as well, no clue as to why it happens, but it doesn't impact functionality.

Hi Daniel,

Do the next experiment.

Go to some Sub, and type:   Dim xxx as doa.

This is immediately converted to:   DOA.   and you can select  Database,  Result:  Dim xxx As DOA.Database

On the next line you type:    Dim doa

When you now go to the next line. All your DOA's are converted to doa's.

Then delete the line   Dim doa   and save the Sub, and leave the application. All doa's stay doa's

To restore  DOA, type Dim DOA, go to the next line and delete the line Dim DOA.

Imb

Edit:  Please read DOA AS DAO!

Answer 6

DOA???? Presumably DAO.

Answer 7

DOA???? Presumably DAO.

Thank you, isladogs,

Yes, that is right.

Slip of the pen. Sometimes running a half marathon leaves your brains in a different state.

Imb.

Answer 8

I'm impressed but I didn't know any half marathon events were 'running' at the moment.

Answer 9

I'm impressed but I didn't know any half marathon events were 'running' at the moment.

Hi isladogs,

There are no "global" events, that is right. But I like to do that every sunday morning in my own environment.

It is good for body and soul, and a good test to check that you're not yet affected by the Corona-virus.

Imb.

Answer 10

Open Search and Replace and select Current Project and enter:

Find what: b12_500Code

Replace with: B12_500Code

Select Debug, Compile and save the project.

---

Gustav Brock

Answer 11

Daniel:

I tried all the suggestions here, and none of them worked.  I guess "That's Life" as Frank Sinatra used to sing.

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 12

I tried all the suggestions here, and none of them worked.  I guess "That's Life" as Frank Sinatra used to sing.

Hi RichLocus,

Did you really write this line in the module:

        Dim B12_500Code

and go to a next line, for VBE to process the line?

As soon as you do this all lower case    b12_500Code    will be converted to uppper case    B12_500Code

Then delete the line again, and save the module.

Imb.

Answer 13

IMB:

I"m away from my office but will try it again tonight. Perhaps I didn't follow your directions exactly.

Thanks,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 14

IMB:

Yes... your process worked like a charm!

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 15

Redoing the declaration do not resolve the issue.  I've tried that long ago.  The Editor is tempermental and sometimes behave oddly, it's simply part of life.  This has been discussed before and no has ever been able to find the root cause or solution.

Hi Daniel,

Did you really do the test that I suggested?

In my opinion the case-behaviour of the Editor is completely predictable. The odd behaviour is probably caused by unconscious typing errors of the user.

Imb.

Answer 16

...In my opinion the case-behaviour of the Editor is completely predictable. The odd behaviour is probably caused by unconscious typing errors of the user...

Hi, I'm more with Daniel. It is not predictable. One should always try the old Dim it trick. Over many years it has worked many times and many times not. We've often discussed it and never found a coherent logic.

---

Karl
Access DevCon Vienna
Access-Entwickler-Konferenz

Answer 17

Over many years it has worked many times and many times not. We've often discussed it and never found a coherent logic.

Hi Karl,

Until now I did not found examples where the Dim declaration did not work.

Do you happen to have some references to such cases?

Many thanks.

Imb.

Answer 18

I've seen this also. Use Edit/Replace/Current Project dao.Database to DAO.Database and that seems to correct the issue (until it happens the next time)

---

Dave Thompson

Answer 19

(until it happens the next time)

Hi Dave,

Just add a line with:     Dim dao       and it happens again!!!

Just add a line with:     Dim DAO       to repair it.

Imb

Answer 20

Imb, Good to know. Next time the gremlin shows up, I'll use your technique.

---

Dave Thompson

Answer 21

Next time the gremlin shows up, I'll use your technique.

Hi Dave,

No gremlins, just unconsciously made typing errors, that are recognized by VBE as (internal) tokens.

At least, that is my interpretation, and I have not yet seen any examples that this is not the case.

Imb.

Answer 22

Until now I did not found examples where the Dim declaration did not work.

Do you happen to have some references to such cases?

Hi Imb.,

of course you are fully entitled to your opinion. However, I have to prepare readers to not fully rely on the Dim or F&R methods to always cure it. I've had many occasions when they didn't help.

The latest case was yesterday at a client site where we couldn't cure "close" in his code. "Dim Close" didn't help and just throws an error because of the misused key word anyway.

---

Karl
Access DevCon Vienna
Access-Entwickler-Konferenz

Answer 23

The latest case was yesterday at a client site where we couldn't cure "close" in his code. "Dim Close" didn't help and just throws an error because of the misused key word anyway.

Hi Karl,

Thank you for your example,  I am glad with it.

I found now there are a few "words" that cannot be cured with a Dim statement, such as Close, Open, Input.

The problem with these words is that, until now, I did not succeed to induce the lower case either, whatever I tried.

Not believing in miracles, my next question is: how is it ever possible that the code contains "close" and not "Close"?

I will continue with my experiments.

Imb.

Answer 24

Imb, other than DAO which I treat as an exception, I prefer to type keywords and declared variables in lowercase. Then when I move off the line, the editor corrects the case confirming that I did not make a typo.

If I don't see the keyword snap into place, I know I made a mistake. Thus recrdset will not correct, but recordset will change to Recordset, etc.

I know assist is available, but it's about even between correcting typos or tabbing off the wrong value form the pull down assist.

---

Dave Thompson