asked on

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.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luke Chung

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.

PatHartman

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.

Eddie Antar

ASKER

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

PatHartman

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.