I am trying to reference various fields in the Detail_Format subroutine that are part of the current record. These fields are NOT defined as controls on the report. I am receiving the following error message - "Run-time error '2465'. 'Database' can't find the field 'fieldname' referred to in your expression."

So for a table 'tbl' with the fields 'FName', 'LName' and 'Suffix' I have tried to reference the 'FName' field as -
FName, [FName]
Me.FName, Me!FName, Me![FName], Me.[FName]
tbl.FName, tbl!FName, tbl![FName], tbl.[FName]

Although it doesn't make sense to me to use the Me. prefix, when I do I can see FName.

Also, if I define a control on the report that references FName then I can "see" it in the subroutine.

Any help would be much appreciated!

I am not sure why you just don't use "Me.FName" to reference the field. Me refers to the report, and FName refers to the control or field, and Me.FName returns the data currently in the control. What I do is to put the fields on the report, and then just set their visible property to No. Then I don't have any problems referencing them, and they are not shown to the user.

Alternatively, seeing as it looks like you are trying to concatenate text fields into something like "Smith, John, Jr." why don't you do this in an underlying query by adding a field like this to a query that pulls all your data from tbl:

FullName:([LName] & ", " & [FName] & ", " & [Suffix])

The results in the query will be "Smith, John, Jr." and you can just drop this field onto your report - after you make the query the record source for the report.

HTH


mmcdonal

Hi mmcdonal,

Thanks for responding. I have over 20 fields that I need to reference and concatenate differently based on values in those fields. The example I gave was just that - an example. In the past I have used "in"visible controls as you mentioned; but have always questioned why I've had to do it. Do you know why I can't "see" the fields? As I said - maybe not so clearly - when I type in Me. in VBA the listbox that comes up shows all the fields in the recordsource - even though they are NOT controls on the report - as well as other properties of Me.

Another track I took was to see if there is a RecordSetClone - like for forms - for reports. There's not that I could find.

So for this task I defined my "in"visible control and set the controlsource to a concatenated string of all the values I need to reference in VBA.

But thanks for your input.