Data source field from form

[tikkat3]

In my report I am trying to get the results from a calculation field within a form.

The control source in the form is not an individual field, but a field added by me with the sum "=[fieldA]*25.98". i.e. in the form the field calculates a figure.

When trying to build the report I have made the control source for this particular field the appropriate field from the form through the expression builder. Its control source reads: "=[Forms]![Installation Problem Form]![Labour Cost]".

Should this not give me the results from the form? All I am getting is an Error. This does not make sense.

 

[boblarson]

Did you include the quotation marks? If so, they shouldn't be there.

 

[tikkat3]

No. The quotation marks were just there to separate the expression from the rest of the text. The rest was entered as the control source but brings up #Name?

The name of the form fields is correct. It is entered using the expression builder, double clicking on FORMS in the left column, finding the right form; then from the middle column, going down the list to find the correct field; double click the field which enters it into the main box; OK and it is entered as the control source.

What is going wrong?!

 

[Rich]

Is the form still open while the Report opens?

 

[tikkat3]

No. Report is the only thing open in Access.

 

[Michael J Ross]

You need to have the form open when the report runs, so the report can get the data from the form

 

[tikkat3]

Thanks Michael, I am now getting figures, but just 0 through all columns! The data from the form is not coming through.

 

[Michael J Ross]

Should work, can you post up your db?

 

[tikkat3]

I have partially resolved the problem. I had linked the wrong query to the report . The material costs (taken from report) are coming through ok, but anything from the form fields is showing the results of a single record - even if the record is not within the timescale of the report that is run.

 

[tikkat3]

I have partially resolved the problem. I had linked the wrong query to the report . The material costs (taken from report) are coming through ok, but anything from the form fields is showing the results of a single record - even if the record is not within the timescale of the report that is run.

I have worked out that the report shows the data for the record of the page of the form that is selected and gives this as the report results for all records.

If I am on record 10 in the form that figures for that record will show as the report results. If in record 46 those will be the report results!!!

 

[Michael J Ross]

What comes through from the form will be a single record.(the record that is visible on the form)

Can you not replicate the calculation in your report instead of doing it in the form and bringing it in, if you want to display multiple records in your report?

 

[tikkat3]

I have tried replicating the formula but am still getting nowhere fast.

The control source expression for the form (one of the fields in question) is:
=IIf([Vist / Raised Date]>#30/04/2007#,NZ([Project Eng Hrs]*58.74)+NZ([Install Hrs]*21.84)+NZ([Mat £]),NZ([Project Eng Hrs]*48.85)+NZ([Install Hrs]*19.49)+NZ([Mat £]))

In the report this is ([Installation non-conformances] is the table):
=IIf([Installation non-conformances]![Visit Date]>#30/04/2007#,NZ([Installation non-conformances]![Project Eng Hrs]*58.74)+NZ([Installation non-conformances]![Install Hrs]*21.84)+NZ([Installation non-conformances]![Mat £]),NZ([Installation non-conformances]![Project Eng Hrs]*48.85)+NZ([Installation non-conformances]![Install Hrs]*19.49)+NZ([Installation non-conformances]![Mat £]))

When I run the report I get the Enter Parameter box for Installation non-conformances, and an #Error in the results.

I am going balder by the minute!

 

[tikkat3]

All sorted! The correct expression is:

=IIf([Visit Date]>#30/04/2007#,NZ([Project Eng Hrs]*58.74)+NZ([Install Hrs]*21.84)+NZ([Mat £]),NZ([Project Eng Hrs]*48.85)+NZ([Install Hrs]*19.49)+NZ([Mat £]))

So simple and so frustrating Michael thank you for your guidance. It is what eventually got me there.

 

[Michael J Ross]

So simple and so frustrating )

It so often is, glad its sorted