VBE keeps undoing my capitalization changes

[HalloweenWeed]

Hello. I have been redoing some of my old fieldnames in Access, there is several of them I want to fix capitalization in. I have fixed them in the forms, tables, and queries. But when I go into the visual basic editor and attempt to fix the capitalization, the editor keeps undoing it, even though it matches the forms, tables, and queries.

I have even tried putting in an underscore, saving, exiting Access, go back and see the underscores, but then when I try to do the final fixes it goes back to the original capitalization anyway! Is there any way to stop this? How is the vbe finding these obsolete field references, and is there any way to refresh them, barring a database rebuild from scratch (yet again)?

 

[theDBguy]

Hi. Just a thought... Have you tried decompiling your project first?

 

[HalloweenWeed]

Yes I just did (and learned how here: https://www.devhut.net/2012/04/16/ms-access-decompile-a-database/). I learned some more about vbe and Access. It lost about 500KB. Upon finishing, it still does the same thing. But here's the kicker: now my db FE is compacting again when I close it despite "compact on close" not checked - so I'm going to rebuild it from scratch for the second time. I will do the changes then.

 

[theDBguy]

Yes I just did (and learned how here: https://www.devhut.net/2012/04/16/ms-access-decompile-a-database/). I learned some more about vbe and Access. It lost about 500KB. Upon finishing, it still does the same thing. But here's the kicker: now my db FE is compacting again when I close it despite "compact on close" not checked - so I'm going to rebuild it from scratch for the second time. I will do the changes then.

Okay. Good luck!

 

[Isaac]

a few things that have helped me when I experience that exact behavior (corrupt-ness, and it insisting on compact & repair when I close it)
1) don't do development on a network copy...copy the file down to your local before working on it, especially if you are on a VPN/wifi.
2) compile & save prior to running code
3) don't add or remove breakpoints (or try to edit code), during code execution or while in break mode.

 

[Deleted member 147267]

You could post a db copy where we might find a cause, which could save you a lot of rework.

 

[HalloweenWeed]

You could post a db copy where we might find a cause, which could save you a lot of rework.

No it's a split-database on an institutional network with a BE that contains 4000 records of personal information, confidential. Thanks for the offer though.

 

[Isaac]

But the VBA can only capitalize based on declared variables, sub/function names, and form controls, basically, right? That's really what we're talking about? Not data/tables.

 

[Deleted member 147267]

No it's a split-database on an institutional network with a BE that contains 4000 records of personal information, confidential.

I would think that for this, no data is needed, but no problem. BTW, your comment about fixing (matching?) what you've done in table/query design is interesting. I would expect the code project to pick up the revised names after a decompile. Did you also compile the code project after a decompile of the db? And we're talking field names here, and there are no aliases defined in the properties of the fields?

 

[HalloweenWeed]

But the VBA can only capitalize based on declared variables, sub/function names, and form controls, basically, right? That's really what we're talking about? Not data/tables.

Yes that's what we are talking about, form controls. The problem is (a very minor one), I changed them in the forms but vbe won't allow me to change them to match now. Capitalization change only - for programmer readability. I try to do a "replace" "replace all" but the editor instantly changes them back, even when I try one at a time manually. Weird?

 

[HalloweenWeed]

I would think that for this, no data is needed, but no problem. BTW, your comment about fixing (matching?) what you've done in table/query design is interesting. I would expect the code project to pick up the revised names after a decompile. Did you also compile the code project after a decompile of the db? And we're talking field names here, and there are no aliases defined in the properties of the fields?

Yes I did. I followed those decompile instructions to the letter. The "compact on close" issue puts a spin on it though, the compact completes so quickly that I don't know if I hit <Esc> in time to stop it or not.

 

[Isaac]

The problem is (a very minor one), I changed them in the forms but vbe won't allow me to change them to match now. Capitalization change only - for programmer readability. I try to do a "replace" "replace all" but the editor instantly changes them back, even when I try one at a time manually. Weird?

Precisely what I am honing in on, since I've never seen VBA recognize table names, unless something other than what I am thinking of is being referred to.

As to being weird.

I just created a brand new database with a textbox on a form. Text1. I then created some button code to populate Text1 when button is clicked.
I compiled, saved, and closed form. I then re-opened form and changed Text1 to TEXT1. I compiled. saved, etc., and yet the VBA project still refers to Text1 and will not change it.

So if I were you I'd consider accepting this and not re-building your database. (attached). Looks like it may be entirely normal.

If it were a variable named you'd changed ... then I'd worry. VBE does pick that up.

 

[HalloweenWeed]

Precisely what I am honing in on, since I've never seen VBA recognize table names, unless something other than what I am thinking of is being referred to.

As to being weird.

I just created a brand new database with a textbox on a form. Text1. I then created some button code to populate Text1 when button is clicked.
I compiled, saved, and closed form. I then re-opened form and changed Text1 to TEXT1. I compiled. saved, etc., and yet the VBA project still refers to Text1 and will not change it.

So if I were you I'd consider accepting this and not re-building your database. (attached). Looks like it may be entirely normal.

If it were a variable named you'd changed ... then I'd worry. VBE does pick that up.

Yes, that's what I am talking about. You'd think there should be a place to look at VB references (to controls) or to tell it to update the spelling.
But it's mainly the conpact on close issue (that I just noticed since I had this vbe spelling issue) that forces me to rebuild from scratch, like you said, for capitalization it's not worth the effort.
Working remotely, "compact on close" is a moderately serious issue, because it takes several minutes (when I said it was quick earlier that was from a local drive), and also if it happened when a user forced a close (like forced shut-down) I think it could leave the FE file corrupt as well.

 

[Isaac]

Yes, that's what I am talking about. You'd think there should be a place to look at VB references (to controls) or to tell it to update the spelling.

Agree, it would definitely be nice. Especially since we've become used to the VBE always catching changes to variable name capitalization.
But it appears that with form controls, what you experienced is normal behavior.

But it's mainly the conpact on close issue (that I just noticed since I had this vbe spelling issue) that forces me to rebuild from scratch

I sympathize. It just happened to me recently, until I realized I was working on a network copy of the FE (over VPN no less). I kept forgetting that day to copy it down to my local. That ended up making all the difference, although switching around screens in Design view continued to be SLOOOOW but I blame that on my VPN which I can't do much about. I guess if I was determined to solve that, I'd have to take the time to have both FE and BE on my local, but I'm too lazy to do at the moment.

 

[jdraw]

I have used the following to adjust the Case of the column names in a table.

tdf refers to the tableDef involved
intTemp is an integer to iterate over the tables Fields collection.

 'Change the case of field names in this table
                     ' to be all Uppercase
                     tdf.Fields(intTemp).name = UCase(tdf.Fields(intTemp).name)

Also, if relevant, seems vbe uses the case when the variable is first dimmed in a module.
Good luck.

Update: Based on comments and testing.

When I create a table, and then create a bound form (Form wizard) on that table, the control names have the same case as the table field names. If I delete the form; then change the case of the field names(propercase) in the table with logic as shown above and create a new form (form wizard), the control names on that bound form are the same as the case (propercase in this example) as the field names in the table.

So my testing indicates that the case of the control names on a bound form are the same as the case of the field names that exist when the form is created.

'Added the next line(s) to alter the case of fieldnames in this table 1-Jul-20
                     'Change the case of field names in this table
                     ' to be all Uppercase
120                  tdf.Fields(intTemp).name = StrConv((tdf.Fields(intTemp).name), vbProperCase)

Original values:
ID
FNAME
LNAME
GRADDATE

revised values
Id
Fname
Lname
Graddate

 

[HalloweenWeed]

Also, if relevant, seems vbe uses the case when the variable is first dimmed in a module.
Good luck.

The references that I am trying to change were never Dim'ed in a module, they were always just form controls (which also happen to be same name as the fields from the queries/tables. (As you know) I don't think you can even Dim a control name in module for that form.

 

[sxschech]

I tried too and looks like however you first entered the name of the control is the way it stays. I created a text box with all upper case and then changed to lower case and it stays upper case. Seems to keep the case format of the way it was originally entered, however, if you change the name and the case, it can reflect the new case. Also, looks like it keeps a memory of what was previously entered so when I changed the name and renamed it back to the original name, it used the case of the original text. Eventually was able to get it to stick by changing the name to something else, saving the form and closing the file, reopening the file and then changing the name, but seems like to much effort to adjust the case of the name.

 

[Deleted member 147267]

they were always just form controls (which also happen to be same name as the fields from the queries/tables.

This habit I would advise against. You will eventually find that Access will not be able to distinguish between a control or field name in some cases. I'm beginning to lose site of the problem; I don't even see it really. Users who only look at form and report labels don't know or care if the field is capitalized or not so I don't understand why capitalization of a control or field name even matters to them. If it matters to you so that you can tell which is which in code, then re-read the first sentence of this reply.

I also did a test - capitalized the name of a command button and msgbox Me.command1.Name returned the capitalization - which would be the name as a property value. I don't have Name Autocorrect enabled in this db. Intellisense offers the lower case string whether I compile the code or not. Perhaps if you want Access to not treat Case verbatim, you will have to set the property of the db to something other than Option Compare. Again, if this is all because of field and object names being the same, I honestly think you are wasting time on something you shouldn't be doing anyway.

 

[sxschech]

Agree with Micron to name objects on the form to not be same as the field names from table or query and also since users won't see, it shouldn't matter as much if the vba shows upper or lower. Wanted to mention though that in my test, it was a new db, turned off auto correct and only created an unbound form (did not create any tables or queries).

 

[HalloweenWeed]

Thanks for you input guys, but the real truth of the matter is in a few week's I will have time to completely rebuild my FE from scratch, it's just that I could've used that time to do some more learning.
As for the control name vs field names issue, do you think this may be causing my "compact on close" issue, or slowing down remote Access operations? Because if it isn't: this is working so I'm not going to spend another 2-3 months fixing something that doesn't matter. Besides, I'm just following the method of the original db creator (who is long-gone now). Will keep it in mind for the future though.