I am updating a database from A2000 to A2007 in which there is a report where the format Font / Colour / Position / background of controls on the report change dependant on the record the report is printing.

The database currently achieves this through creating a recordset in the Detail on Format event, this seems a trifle inefficient, as the data can be read from the reports underlying query.

Is it possible to read the fields in the reports query without having to have (non visible) controls for each of these fields on the report?, and if so what is the code format needed to read the fields directly

Thanks in anticipation.

Add an On Open event to your report, and insert the code below. Worked when tested in Ac2003:

Hello John

Thank you for your reply

But I am not sure I either understand it or, or how to use it or my request was not very clear.

Your code gives a list of field names from the underlying query, but what I need is the values in those fields
for each record.

I have replaced the original code in the detail On Format event with (part)This means there are 9 hidden controls on the form just to extract the formatting information for one visible
control and needs to be repeated for other visible controls.

I am trying to use the above code (or equivalent) without the need to have the corresponding fields as hidden
controls on the report. I believe you can refer to fields from a Form recordsource without needing a corresponding
control, but I cannot get equivalent code to work on the report.

Thanks

Ah, well, no. You must have them on the report in order to make the change in the Detail section for each record. Or, you might could have all the formatting in one table with a Primary Key, in your data table referencing the appropriate primary key for each record, and then read-in the values in the Detail section - that is, if the formatting is the same for the visible controls of each record that need formatting.

Hello John

Thanks for your advice.

I am afraid that all the records can potentially have different formatting from each other;
the report is for a site access pass that includes graphics (e.g. a disabled symbol), or coloured background and
various text references that need to be formatted for each record. I was using a recordset to find the format data for each record (rather than say a DLookup) but felt that it was very inefficient to open and close a
recordset on each and every record. I'd also considered using a persistent recordset opened in the reports On
Open event and stepping through with each On Format event, only as there is also the facility to print out
different numbers of copies for each pass I would have to ensure that the current record and the recordset
stay synchronised.

As the data required is readily available in the report's recordsource query (or can easily be added), I felt it
would be a more robust method to read the formatting data from the current record.

Which, as I am typing this, has given me the idea of creating a persistent recordset of the query, write this to the
report's recordsource (Set Me.Recordset = rstPerm.OpenRecordset) and set the persistent recordset to the
current record on the report, extracting the formatting data from recordset, this would avoid an
unnescessary call to the BE data file and the need for hidden controls on the report.

I'll try that (when I get a bit longer to give it some more thought)- if you can think of any problems please let me
know.

Thanks.