Can't access a field that's in the Record Source from the VBA code in my report.

This is driving me crazy. Does anyone know what the rules are for accessing a field from a report recordset that you do NOT place in the report design? Some times I have no problems, other times I get an End / Debug saying it can't find the field.

Example: I have a record source for a report that has a field called ActionPlanDate. It's in the data, but not on the report layout. I have a line of VBA code that states:
txtResponseDate1 = ActionPlanDate

I need to do it this way to not clutter the report with controls.

I'm getting the message... Microsoft Access can't find the field ActionPlanDate. It's seems to not be able to find any of the dataset fields that are not on the report layout. Even if I try Me.ActionPlanDate or Me.RecordSet.ActionPlanDate. None of this seems to work.

What's the rule?

Any help would be greatly appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Access thinks it is smarter than we are :)  One of the "helpful" things it does for us is to rewrite our RecordSource for a report (forms do not exhibit this behavior) to remove any and all columns that are not bound to fields on the report.  To beat Access at this game, put a hidden field in the detail section of the report.  Make it tiny and keep it from overlapping any other control.  I always set the background of hidden controls to bright yellow so they stand out for me in design view.

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
Luke ChungPresidentCommented:
Are you trying to get the value from the field into a VBA variable or assign the value to a text box?

A text box is assigned to the field using its controlsource property. You can assign this in VBA when the report opens if you don't set it in advance.
Therein lies the problem.  By the time you open the report, Access has already replaced your RecordSource with one of its own making and it only includes columns that are bound to controls.  People are fooled because when they try this the first time, it always works because the report is still using the RecordSource you provided.  Close the report and open it again.  The unbound fields will be MIA.
eantarAuthor Commented:
Thanks Pat. I wound up placing all those controls on the Report Design. I hid them and was then able to use vba code to assign the correct field values to unbound controls, depending on certain conditions. And you're right, you are fooled the first time you run it and Access has no problem with it.

Thanks for the help.
You're welcome.  This was very painful for me in the early days of Access.  I don't think i actually figured this out for the first 5 years I worked with Access.  It is a subtle problem and you only run into it if you don't need the report to show the field you are using in the calculation.  It is not a bug.  It is by design but no documentation anywhere even hints at what is going on behind the scenes.
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.