How to reference current field name in control source?

[Eupher]

I have a number of reports that don't really function like classic reports. That is, much of the data is displayed in unbound text boxes laid out in a grid format, where the data is pulled directly from corresponding fields on a data entry form. I realize it's a kludge, but there are specific reasons for doing it that way. Anyway, the generic control source for each of these report fields looks something like this:

=IIf(IsNull(Forms!MB45_Form!NameRowXColY),"",Forms!MB45_Form!InstRowXColY)

where X is a digit from 1-9 and Y is a digit from 1-5. As you can imagine, it's quite tedious to hard-code those field names in every report that uses a similar format. I would rather have a control source that's a public function and therefore the same for each field, i.e.:

=GetFieldData(Me!Field.Name)

where the name of the control gets passed to the function, the function gets the right data from the corresponding form, and passes it back to the control source of the report field. But I don't know how to dynamically reference the name of the field to pass as the argument. Can someone help me with the syntax? Thanks!

Ken S.

 

[randysmid]

Hi,
This is all you really need as a control source:
=Forms!MB45_Form!NameRowXColY
HTH,
Randy Smith
California Teachers Association

 

[Eupher]

Randy, thanks for your response. I didn't make myself clear. The control source as it now exists is as you suggested (i.e. Forms!MB45_Form!NameRow1Col1, Forms!MB45_Form!NameRow1Col2, Forms!MB45_Form!NameRow1Col3, etc). The problem is, I need to build a bunch more reports with similar design (say, 30 reports), but with a different number of rows and columns on each report, a different source form, and with the number of controls on each form potentially exceeding 100. So the control source in each field would have to be manually edited to reflect the correct source form (i.e. Forms!MB40_Form!NameRow1Col1, Forms!BQ_Form!NameRow1Col1, etc.). That's an awful lotta zooming, cutting, pasting, and typing. Yuk! Hence my desire to come up with a function for the control source that will work for all the fields of the report.

Anyway, as an alternative, I came up with a little function that opens a specified report, searches all the textboxes for a control source value and replaces it with the correct value. A *huge* time saver. But still not as elegant as a single function for all the textbox fields.

Thanks again for the input.

Ken S.

 

[randysmid]

Hi,
You can create a query with all the fields in it, and use the DLookup function to retrieve the single value you want. HTH,
Randy Smith
California Teachers Association