"Variable not defined"; VBA and Intellisense using outdated table schema

[BarksWorseThan]

I can refer to some, but not all, of the fields in the RecordSource of a Form in the VBA code-behind. I have been careful to name the controls which provide data display/entry for the fields differently from the fields, and to name any variables differently from both fields and controls.

If I reference certain fields in an expression, I get a compile-time error "Variable not defined". Also, the same field names that throw this error do not appear in the Intellisense drop-down if I type, for example, "Me." to start an expression.

Looking through the fieldnames listed in the Intellisense drop-down, I note some entries there that have either been removed from the table or renamed; similarly, I note the absence in the list of some recently-added fields.

Finally, in the Form Design interface, the Control Source drop-downs for all controls show the correct, current list of field names for the RecordSource table.

It appears as though the VBA IDE is working from some stale, older version of the table schema -- it reflects the fieldnames as they were when the form was first designed, and does not reflect more recent changes to those fields and their names.

A specific example: my table dat_Tracts used to have a field in it called TractSub, and Form_Tracts had a control on it named cboTractSub whose ControlSource was TractSub. I subsequently renamed the field to TractSubCode, renamed the control to cboTractSubCode, and reset that control's ControlSource to TractSubCode. The form and control work fine, but if I try to reference TractSubCode or Me.TractSubCode in the VBA, I get the error. Furthermore, I note that Intellisense still includes Me.TractSub in its list even though there is no field nor variable with that name!

Does anyone recognize these symptoms? Is there a way to refresh/update VBA's "understanding" of the fieldnames? Or is there some other explanation (and fix) for this problem?

Sorry to be long-winded but I want to be clear that this does not seem to be a simple case of confusing control and fieldnames or the like.

Many thanks in advance,

Chris

[NeoPa]

BarksWorseThan:
Sorry to be long-winded but I want to be clear that this does not seem to be a simple case of confusing control and fieldnames or the like.

How can you even consider apologising for such a perfectly explained question? I'm almost shocked (but certainly impressed).

I've never noticed any such symptoms myself. I'm wondering if this might be because of some of the habits I have for dealing with my projects, but frankly that's a stab in the dark. I'd certainly be interested in seeing this behaviour for myself and maybe experimenting to see if I can determine what, if anything, it relates to. I don't have 2007 or beyond available at this time, but if you're interested in attaching a copy I'll link you to some preliminary steps that will help avoid wasted effort (Attach Database (or other work)).

[BarksWorseThan]

Hi NeoPa,

It seems an honor, if a dubious one, to have stumped the Most Active/Most Answers member of this forum! I appreciate your offer to look over my work, and I may take you up on that but I am heading out of town tomorrow for most of this week and won't have time to prep it for you before I go.

Your comment about not having Access 2007/2010 gives me one idea to try: it turns out that I just upgraded to 2010 a few weeks ago; I wonder if opening it in 2003 (my previous version) would show the same symptoms or not? My guess is that it *will* have the same problem, but perhaps the exercise will give me some insight or clue. I don't recall the timing of the table mods (that are not showing up in VBA IDE) vis-a-vis my switch to 2010...

So, I'll be out of the loop on this for a stretch of days but will follow up on my return. Many thanks,

Chris

[NeoPa]

I was hoping/expecting that the act of compiling the project explicitly (One of the steps involved from the link) would actually cause the issue to disappear. It probably comes down to what causes Access to rebuild its table of available objects within the project. My guess would be the Compile step, but I don't know for sure. I suggest you try that step as one of your first. Many of the other steps are often worthwhile anyway though. Good tools for general usage.

I'll see how you've managed when you get back. I'm still happy to look at it for you if it gets that far.