Variable has a random capital ever since I typed it in wrong in a table. How to fix?

[wackywoo105]

This is an odd one. I set a table up and created a form from it. I then wrote some vba code. A while later I noticed I had named a field in the database SUrname instead of Surname.

I've rewritten it as Surname in table, field controls etc, but in vba code it still corrects to SUrname. I then deleted it from everywhere and still on typing it auto changes to SUrname.

Is there a way to fix this? I have done it before and after a lot of mucking about it did fix, but by then I didn't know what I had done that fixed it.

 

[CJ_London]

Probably decompile then recompile then compact/repair

 

[MajP]

When you write in VBA most of the time
Me.Surname is referencing a control. It only references a field if no control with that name exists. When added the control the first time it was named SUrname by default but there is no "link". So if you then changed the name the control is still SUrname. You need to edit the control

 

[wackywoo105]

I've deleted it from all tables. I've deleted it from all forms. I've deleted it from all vba. I've compacted and repaired. I closed and reopened. When I type Surname it changes to SUrname.

What could I be missing? I wish I could get on with my life but I feel like Walter White chasing a fly. The annoying thing is I have fixed this issue before but can't seem to this time.

Also when you say decompile and recompile I have never compiled in the first place.

 

[jdraw]

Post your database. Someone will locate the culprit.

 

[MajP]

FYI. VBA has no idea what is in your tables or queries so changing the name in your tables and compact repair will not have any impact. However a a compile will affect this. If there are no problems in the code a compile happen automatically. If there is a problem it will not recompile. You have to manually select compile and then fix all the problems first. My guess is you need to force a compile. I have seen this before.

 

[CJ_London]

I've deleted it from all tables. I've deleted it from all forms. I've deleted it from all vba. I've compacted and repaired. I closed and reopened

But you haven’t decompiled then recompiled

How does one decompile and recompile a database application?

I have an Access database application and I would like to know the proper way of decompiling and recompiling it.

stackoverflow.com

 

[MajP]

@wackywoo105. Nothing wrong with decompiling first, but that takes a little extra work. In the past when I have seen this it normally is hung on compiling because there is an error/s in the code. Try compiling first. If that does not do the trick then read the article on decompiling. It is worth going through the decompile drill either way because it is a good skill set to have.

 

[MajP]

Also here is an interesting fix.

Solved - Why .Value changes to .value

In one of my databases .Value changes to .value as soon as I exit the line. I searched the whole project to see if a variable named value is declared anywhere. I couldn't find any. In a copy of database, I started deleting one object at a time (form-report-module) to find out the source of this...

www.access-programmers.co.uk

 

[Cronk]

Have you checked for any occurrence of

Dim SUrname

 

[AccessBlaster]

I would use find in the project / code window to look for any instance of SUrname just to rule it out.

 

[wackywoo105]

Thanks everyone. I was going to try and post the database but there is a lot of sensitive info I was trying to remove before doing so.

Anyway I renamed the field in tables. Deleted it from all forms. Found and replaced all occurrences with the code. Along with lots of compact and repair (may not do anything but why not). I then declared the variable in a module and slowly added it back elsewhere.

The first time I did this this I thought it had worked. I went to bed happy and then the next morning was getting SUrname again :/. I had another go and this time it has stuck.

This is a database I started building around 10+ years ago and have added lots to it over time. I have no background in coding so the early code is a mess and has gradually improved over time. This is why you will see some daft questions from me as I never learned basic concepts before ploughing on.

 

[MajP]

Again, most of what you described will do nothing. A full decompile and recompile is more likely a solution.

 

[Josef P.]

@wackywoo105: have you enabled 'Trac name AutoCorrect info'?

/edit: tested now with naming of a form control (TextBox)
This works for me (with disabled AutoCorrect info):

1. SaveAsText
2. Delete form
3. Compact Db
4. LoadFromText

Decompile did not bring any change for me.
Does Decompile possibly only affect the VBA code and not the control definitions of a form?

With that, it also worked: https://team-moeller.de/?Add-Ins:TM-RebuildDatabase

 

[Minty]

Another possible solution.
At the top of a module (below Option Explicit) put

Dim Surname as Variant

Compile the code and save.
Compact and repair
Delete the line, recompile, compact and repair again.

 

[Josef P.]

@Minty: tested and works with my test app ... this is the simplest method.

/edit: looked up, did not work.
The declaration of the variable (in a standard module) was changed to the wrong notation in my case).

 

[Minty]

@Josef P.

Normally that indicates it is declared somewhere else as well then.
That method has always worked for me in the past when the editor appears to have cached a strangely capitalised version of a variable or function name.

It must be declared outside of a named function or sub, so that it is globally available.

 

[Josef P.]

I usually did it similar to what you described.
In the attached example, however, this does not work.

This also works in the attached example:

1. create a new name: SUrname => SurnameX
2. Compact
3. rename again: SurnameX => Surname

 

[Mike Krailo]

I've always made the corrections to the tables and controls and just imported everything into a new database and fixed this sort of annoyance that way. It cleans up all the behind the scenes tables the ms access uses.

 

[Galaxiom]

(Half joking) Maybe Surname is a weird word.

I have a query in SSMS where SURNAME is a column in a Temporary table. No matter what I do, SSMS is absolutely convinced it is an invalid column name when I refer to it in the main main query. It is perfectly happy with all the other columns in the temp table.

The query runs just fine.