MS Access vba compile error when referencing field names


I have an Access db which throws up a compile error when it is asked to reference a field name in vba thus - eg me.MyField

However if I reference the control the field is linked to it works eg me.txtMyFieldTextBox.

The original version of the db is working fine but the version with the error is one where I have imported all objects form the original.

Any ideas?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

First of all you must understand that Me refers to the calling form, so if you try to access a table field that way you will encounter an error because the table field isn't a member of the form collection.

To access the table field you will have to use the DLookup function: DLookup(TableName, StringName)
Dale FyeCommented:
One of the reasons that most professional Access developers use a naming convention for their forms is so that in code, they will know whether they are referring to a control, or a field.  

Most use a prefix on the control which helps them determine the type of control in code, so that when they are debugging code, they do not have to continually refer to the form to determine the type of the control.

If you want to specifically refer to the field, I generally wrap that reference in brackets:


but you can also use:

kenabbottAuthor Commented:
I probably didn't explain this well enough and I'm fully aware of naming conventions etc and also how me. works.  The form is a bound form and the field in question is included in forms bound record source so Dlookup would not be relevant here.  Also I would normally use the bound controls name rather than the field name but this is a database I've inherited.

So the problem is simply that in db1 myVar = me.MyField works but in db2 the identical line of code (and form/table setup) gets the compile error.  My guess is that it maybe something to do with references although I'm not sure what.

Many thanks
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.


the compiler can check the reference to a database field only if you also have a RecordSource in the form. So for example, if you design the form using a RecordSource (maybe a query or table name) then Me.MyField will not throw a compile error. If you assign the RecordSource using VBA (i.e. in Form_Load) and delete the RecordSource setting in the form's properties then the compiler throws this error.

Sometimes it also happens that VBA uses the wrong reference if you have used equal names so in general you should always use a control name which differs from the bound field. I.e. if your field is "MyField" then the name of the control should not be "MyField", too, but instead for example "ctlMyField". In this case it is always clear for the compiler which name is assigned to which object and you'll never get a compile error. Of course, as mentioned above, the RecordSource must be known at design time as the compiler can only use what's available at design time and will not scan any VBA built SQL string to see if that is assigned at runtime and if that's the right value. It will also not check if your control is bound to a field named "MyField" to use that as reference.

The "!" syntax (recommended by Microsoft, but not by me, I always use ".") could solve the problem, you would not get a compile error (in all cases) because the compiler ignores anything after "!". That means, no confirmation by the compiler that you have used the right field name, because you could also write "Me!MickyMouse" and would not get an error, but surely get one at runtime.

So if you have not bound the form to a RecordSource because it's bound using VBA at runtime then you can always use the control to access a field with VBA as the controlname is always known at design time. If that's a field not visible in the form you can simply add a control (textbox or checkbox is enough) and make it invisible, bind that to the desired field and name the control with a prefix, then you can use that to manipulate the contents of the field with VBA without adding a visible field for the user. This method is for example a standard method used in Web forms. Look at a form in a web application in sourcecode mode and you'll mostly see one or more hidden controls which only the code uses.


Dale FyeCommented:

You never indicate, is this on a form, or report?

What version of Access?

With some of the later versions of Access (don't remember where this started but think it was 2007), you could not refer to a field that is in the form/reports recordsource if it was not assigned to a control on the form/report.  In that case, do like Christian suggests and add a hidden control to the form or report.
kenabbottAuthor Commented:
Many thanks for your replies.  I still don't think it quite gets to the bottom of it because the code works perfectly well (and has done for many months) in one db but the compile error appears in the other which is identical in every way and is running on the same pc using the same version of Access.  There must be some other underlying issue.  For now I will rewrite some of the code but if I do find a solution for the problem I will report it here.

VBA is far away of being bug-free. For example, the namespace of VBA get corrupted sometimes and it will not find a specific self-defined Enum anymore. If you have Enums in your code, try to add a space in the name of one of them (doesn't make a difference which enum or in which module), leave the row (will throw an error or the line will turn red), then remove the space and voilá, your namespace is corrected and everything compiles fine.
Also "compact & repair" sometimes helps, some people like to use decompile (you'll find a lot of instructions how to do that if you search for that), but I personally never had the need to decompile an Access file to repair something with the namespace. In case of a form, you can also use the undocumented "Application.SaveAsText" and "Application.LoadFromText" to save a form as pure ASCII file, then delete the form from the database and load it back, will exactly be the same like before but you do not have any "binary trash" in the form object coming from a corrupted namespace or any other trash saved with the form, then you can compile it again.
Sometimes only reimporting the objects into a blank new database file can repair some issues.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kenabbottAuthor Commented:
Tell me about it!  I have a feeling that this may be bug/corruption related because there is a lack of logic in what is going on.
About what? I've spoken about several methods above...:-)
I agree.  This is most likely corruption so start with the already recommended methods - compact/decompile/copy into empty db to rid the code of whatever is causing the poor behavior.

I have found more strange errors like this in A2013 than in any other version.

To clarify a couple of earlier points -
1.) The bang forces late binding and so that is why you won't get an error referencing Me!somefield when you do get an error using Me.somefield.  You may or may not get the error at runtime depending on whether the field actually exists in the RecordSource and whether or not you execute the instruction.  So, I also always use the dot whenever I can.  I strongly prefer compile errors to runtime errors.  They are so much easier to fix and they have the added advantage of not making you look stupid in front of your clients.

2.) The reason you have to create hidden bound controls for reports is because Access is so much smarter than we are that it completely rewrites our query for the report and it drops ALL columns that are not specifically bound to a control.  It doesn't seem to take into consideration that you might have used them in code or a macro.  It eliminates them if it doesn't find a bound control.  This behavior has existed as long as I can remember - at least back to A97.  This particular error caused me a substantial amount of pain in my early days with Access until I figured out what it was doing.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Just to be clear, bang is a look-up command.   What it tells VBA to do is get the object from the default collection of the parent object.

 The best syntax to use on is:


  That's what bang and dot syntax gets translated to anyway.   In forms, this works for both fields and controls because with a bound form, a property is added to the class for the field, unless there is a control with the same name.   So you need to be careful because if you have this:


 and a control with the same name, then you are referring to the control not the field and the control may or may not be bound to that field.

 Even though I know it slows things down, I've always stuck with the old habit of bang and dot just because it easier to understand what's going on.  A bang separates an object from it's parent collection and dot means a property or method follows.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.