Solved

MS Access vba compile error when referencing field names

Posted on 2014-07-24
11
508 Views
Last Modified: 2014-09-23
Hi

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?
0
Comment
Question by:kenabbott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40216442
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)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40216463
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:

me.[MyField]

but you can also use:

me!MyField
0
 

Author Comment

by:kenabbott
ID: 40216682
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
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 40216739
Hi,

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.

Cheers,

Christian
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40216753
ken,

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.
0
 

Author Comment

by:kenabbott
ID: 40216795
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.
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
ID: 40216823
Hi,

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.

Cheers,

Christian
0
 

Author Comment

by:kenabbott
ID: 40217195
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.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 40217844
About what? I've spoken about several methods above...:-)
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40225027
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.
0
 
LVL 57
ID: 40225199
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:

  Forms("frmName")("FieldOrControlName")

  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:

Me.SomeFieldName

 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.

Jim.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question