Solved

MS Access vba compile error when referencing field names

Posted on 2014-07-24
11
487 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
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 26

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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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 34

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now