Showing a calculated field on a report

[Ali Edwards]

Hi all,

I've read many topics advising not to store a calculated field in a table whilst searching for the answer to this.
I have a field in a form called "ReportRef" which is calculated on the date that the record was created and record number. All I want to do is have this printed at the top of the report for each record. The report is based on the "Report" query so how do I get the "ReportRef" data into the query so that it will appear in the report?

If anyone can help I'd be really grateful.

Many thanks
Ali

 

[David R]

If you build the [ReportRef] field in the query, instead of directly on the form, you can refer to it easily in a report later.

Example:
Building on a form: Unbound text field, Control Source = [DateField] & "-" & [ReportNumber]
Building in a query: ReportRef: [DateField] & "-" & [ReportNumber], then your field on the form is actually bound to this new calculated field. Locking the field is a good idea to keep people from being tempted to change the data in it.

 

[Ali Edwards]

David - Thank you for your help.

I should have mentioned that the form is actually a subform (I don't know if this makes a difference). Also, Im reasonably new to Access!

Currently the main form [frmNewResults]is based on a query [qryNewQuery] and the subform [frmP1] is based on [tblP1]. Basically, the main form keeps the Customer address details and the subform shows test result data. There are many records in [frmP1] for each customer in [frmNewResults].

Having read your response, I created a query on [tblP1] and based [frmP1] on that query instead of the underlying table. However, I don't know how I can create a field in the query which will refer to the current record as viewed in the form.

In the existing setup I have an unbound field on the form [Report Index] and in the 'On Current' property for the form I have [Report Index] = [CurrentRecord]. This way, if a record is deleted, the following records [Report Index] field changes to the current record (so that the record showing always has a [Report Index] value equal to that shown in the record navigator). I then have another field [Report Ref] and the control source is: =[month] & [day] & [year] & "-" & [Report Index]", where the month, day and year fields are constructed using the datepart function and stored in the table. This all works very well in the form, displaying a value such as 280502-1 for the first record etc, but obviously I can't print this on a report because the value isn't available in the query.

I hope I have supplied more useful information here and that you (or somebody, anybody!) can help.

Many thanks

 

[Ali Edwards]

In case anybody is interested I have a method! Although a bit clumsy it will do:

When I click on the command button to print the report I have made the event procedure first fill an invisible bound text box with the Report Ref (me.ref=me.report_ref) followed by save record. The value is saved to the table and the report is printed with the value successfully. Of course, everytime the command button is clicked the value is overwritten with the current value, so if a record is deleted (and the record number is changed) the record number is always correct.

 

[David R]

Forms!FormName!ReportNumberControl should show you the report number from the record currently open on the form. Your method apparently works but it's a little unorthodox.