Assigning a text field caption from a function

[jer99 via AccessMonster.com]

I have set up a table called Report_Driver that contains:
ReportName
ReportField
FieldValue
FieldSource
FieldType (can be "Literal" or "Field")

I have set up a few text fields in the report.
My intent is call a function from the report that will exist in a module. It
will do something like this:

open the Report_Driver for a specific reportname
Go through the records
If the field exists then <<<<<-
----**************
if the type is literal, place the value in the caption of the
text box
if not, then do a query, make a literal from the result to place
in a text box
endif

How do I find if the field exists?

and how do I reference it to set the caption from the module function?

It doesn't like:
reports(sreportname)!(sfieldname).caption = RsTemp!FieldValue

(where I populated sreportname with the report name and sfieldname with the
field name from the table)


For some reason I'm just not seeing it.

 

[Marshall Barton]

I have set up a table called Report_Driver that contains:
ReportName
ReportField
FieldValue
FieldSource
FieldType (can be "Literal" or "Field")

I have set up a few text fields in the report.
My intent is call a function from the report that will exist in a module. It
will do something like this:

open the Report_Driver for a specific reportname
Go through the records
If the field exists then <<<<<-
----**************
if the type is literal, place the value in the caption of the
text box
if not, then do a query, make a literal from the result to place
in a text box
endif

How do I find if the field exists?

and how do I reference it to set the caption from the module function?

It doesn't like:
reports(sreportname)!(sfieldname).caption = RsTemp!FieldValue

(where I populated sreportname with the report name and sfieldname with the
field name from the table)

For something this tricky, you need to be very precise in
your description. You continually use the word "field" (a
column in a table/query), which is very difficult to deal
with in a report. If, on the other hand, you really mean a
control (text box, label, etc) in the report, then you can
check if your ReportField is in the report's Controls
collection:

Dim db As Database
Dim rs As DAO.Recordset
Dim ctl As Control

Set db = CurrentDb()
Set rs = OpenRecordset(? ? ?
Do Until rs.EOF
For Each ctl In Me.Controls
If ctl.Name = rs!ReportField Then
If rs!FieldType = "Literal" Then
ctl.Caption = rs!FieldValue
Else
ctl.Value = Dlookup(? ? ?
End If
End If
Next ctl
rs.MoveNext
Loop
Where you put that code depends on the details of what you
are trying to accomplish.