Form Information to Report

[umpire_43]

Hi,

I'm hoping someone can help me with this.

First of all, i created a form with multiply text boxes with complex
calculations in each text box. When you move from one record to another
record, each text box will change due to the calculation.

Now what i need to do is to create a separate report for each record. I
started creating a report by copying and pasting the text boxes from the
form to report in design mode. If at anytime the calculation changes, i don't
want to change the calculation on the form and also on the report. (to much
of chance of forgetting to change both places)

Is there a way i can sort of link the calculation from the text box (form)
to the report? (Only correct the formula on the form and don't need to
correct the calculation text box on the report...ONLY ONCE)

The calculation will change with each record on the report when it is printed.

Could you tell how to link the information from the form to report? It is
possible.

Thanks for your help.

Keith

 

[Allen Browne]

The best approach might be to create a query, and put the complex
calculations into the fields of the query. You can then use this query as
the source for both your form and report.

In a report, you can read a text box from a form, but setting the Control
Source of a text box on the report like this:
=[Forms].[Form1].[Text0]
However, that will show the same value in every record of the report, so it
is probably no good to you.

 

[umpire_43]

Hi Allen,

thanks for the quick reply!!!!

while....i guess i'll have to go back to the drawing board on this. Some of
the calculation in the text boxes are very complex. I get an noification
that the field is to long when i put the information into a query. The
calculation have a multi nested if statements.

Do you have any other recommendation, that might help me in achieving the
reports for each record with different calculations?

thanks again
Keith

The best approach might be to create a query, and put the complex
calculations into the fields of the query. You can then use this query as
the source for both your form and report.

In a report, you can read a text box from a form, but setting the Control
Source of a text box on the report like this:
=[Forms].[Form1].[Text0]
However, that will show the same value in every record of the report, so it
is probably no good to you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I'm hoping someone can help me with this.

First of all, i created a form with multiply text boxes with complex
calculations in each text box. When you move from one record to another
record, each text box will change due to the calculation.

Now what i need to do is to create a separate report for each record. I
started creating a report by copying and pasting the text boxes from the
form to report in design mode. If at anytime the calculation changes, i
don't
want to change the calculation on the form and also on the report. (to
much
of chance of forgetting to change both places)

Is there a way i can sort of link the calculation from the text box (form)
to the report? (Only correct the formula on the form and don't need to
correct the calculation text box on the report...ONLY ONCE)

The calculation will change with each record on the report when it is
printed.

Could you tell how to link the information from the form to report? It is
possible.

Thanks for your help.

Keith

 

[Allen Browne]

If it's too complex for a query, it's probably too complex for a text box
too.

Other possibilities:
- Custom VBA function to perform the complex calculations

- Write the results of a calcuation to a temp table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

thanks for the quick reply!!!!

while....i guess i'll have to go back to the drawing board on this. Some
of
the calculation in the text boxes are very complex. I get an noification
that the field is to long when i put the information into a query. The
calculation have a multi nested if statements.

Do you have any other recommendation, that might help me in achieving the
reports for each record with different calculations?

thanks again
Keith

The best approach might be to create a query, and put the complex
calculations into the fields of the query. You can then use this query as
the source for both your form and report.

In a report, you can read a text box from a form, but setting the Control
Source of a text box on the report like this:
=[Forms].[Form1].[Text0]
However, that will show the same value in every record of the report, so
it
is probably no good to you.

Hi,

I'm hoping someone can help me with this.

First of all, i created a form with multiply text boxes with complex
calculations in each text box. When you move from one record to another
record, each text box will change due to the calculation.

Now what i need to do is to create a separate report for each record.
I
started creating a report by copying and pasting the text boxes from
the
form to report in design mode. If at anytime the calculation changes, i
don't
want to change the calculation on the form and also on the report. (to
much
of chance of forgetting to change both places)

Is there a way i can sort of link the calculation from the text box
(form)
to the report? (Only correct the formula on the form and don't need to
correct the calculation text box on the report...ONLY ONCE)

The calculation will change with each record on the report when it is
printed.

Could you tell how to link the information from the form to report? It
is
possible.

 

[umpire_43]

Hi Alleen,

I think i'm brain dead here!!!

Could you tell me what procedure/process i would use so both the form and
report can read the same calculation? I don't want to create two vba
process. Could you get me start on this?

Thanks again for your help.
Keith

If it's too complex for a query, it's probably too complex for a text box
too.

Other possibilities:
- Custom VBA function to perform the complex calculations

- Write the results of a calcuation to a temp table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

thanks for the quick reply!!!!

while....i guess i'll have to go back to the drawing board on this. Some
of
the calculation in the text boxes are very complex. I get an noification
that the field is to long when i put the information into a query. The
calculation have a multi nested if statements.

Do you have any other recommendation, that might help me in achieving the
reports for each record with different calculations?

thanks again
Keith

The best approach might be to create a query, and put the complex
calculations into the fields of the query. You can then use this query as
the source for both your form and report.

In a report, you can read a text box from a form, but setting the Control
Source of a text box on the report like this:
=[Forms].[Form1].[Text0]
However, that will show the same value in every record of the report, so
it
is probably no good to you.

Hi,

I'm hoping someone can help me with this.

First of all, i created a form with multiply text boxes with complex
calculations in each text box. When you move from one record to another
record, each text box will change due to the calculation.

Now what i need to do is to create a separate report for each record.
I
started creating a report by copying and pasting the text boxes from
the
form to report in design mode. If at anytime the calculation changes, i
don't
want to change the calculation on the form and also on the report. (to
much
of chance of forgetting to change both places)

Is there a way i can sort of link the calculation from the text box
(form)
to the report? (Only correct the formula on the form and don't need to
correct the calculation text box on the report...ONLY ONCE)

The calculation will change with each record on the report when it is
printed.

Could you tell how to link the information from the form to report? It
is
possible.

 

[Allen Browne]

Are you familiar with VBA code?

If you have a calculation that is too complex to perform in a query, you can
write a VBA function to perform the calculation for you. You then call this
function in a query, e.g.:
SELECT *, MyFunc([ID])
FROM Table1;

If that's too slow, you turn the query into an Append query (Append on Query
menu), and add the records to a table. You then use this table as the source
for your form, and for your report. Since they both read the calculated
values in the table, you can get instantaneous results.

(This is a temporary table only: not the main table where your non-dependent
fields are actually kept.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Alleen,

I think i'm brain dead here!!!

Could you tell me what procedure/process i would use so both the form and
report can read the same calculation? I don't want to create two vba
process. Could you get me start on this?

Thanks again for your help.
Keith

If it's too complex for a query, it's probably too complex for a text box
too.

Other possibilities:
- Custom VBA function to perform the complex calculations

- Write the results of a calcuation to a temp table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

thanks for the quick reply!!!!

while....i guess i'll have to go back to the drawing board on this.
Some
of
the calculation in the text boxes are very complex. I get an
noification
that the field is to long when i put the information into a query. The
calculation have a multi nested if statements.

Do you have any other recommendation, that might help me in achieving
the
reports for each record with different calculations?

thanks again
Keith

:

The best approach might be to create a query, and put the complex
calculations into the fields of the query. You can then use this query
as
the source for both your form and report.

In a report, you can read a text box from a form, but setting the
Control
Source of a text box on the report like this:
=[Forms].[Form1].[Text0]
However, that will show the same value in every record of the report,
so
it
is probably no good to you.

Hi,

I'm hoping someone can help me with this.

First of all, i created a form with multiply text boxes with complex
calculations in each text box. When you move from one record to
another
record, each text box will change due to the calculation.

Now what i need to do is to create a separate report for each
record.
I
started creating a report by copying and pasting the text boxes
from
the
form to report in design mode. If at anytime the calculation
changes, i
don't
want to change the calculation on the form and also on the report.
(to
much
of chance of forgetting to change both places)

Is there a way i can sort of link the calculation from the text box
(form)
to the report? (Only correct the formula on the form and don't need
to
correct the calculation text box on the report...ONLY ONCE)

The calculation will change with each record on the report when it
is
printed.

Could you tell how to link the information from the form to report?
It
is
possible.

 

[umpire_43]

Yeh i am familiar with vba code but its been a while since a done some major
coding

I just can't remember where to create the vba code....module or class then
after that how to reference the function to report and form.

am i off base here?

Thanks keith

Are you familiar with VBA code?

If you have a calculation that is too complex to perform in a query, you can
write a VBA function to perform the calculation for you. You then call this
function in a query, e.g.:
SELECT *, MyFunc([ID])
FROM Table1;

If that's too slow, you turn the query into an Append query (Append on Query
menu), and add the records to a table. You then use this table as the source
for your form, and for your report. Since they both read the calculated
values in the table, you can get instantaneous results.

(This is a temporary table only: not the main table where your non-dependent
fields are actually kept.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Alleen,

I think i'm brain dead here!!!

Could you tell me what procedure/process i would use so both the form and
report can read the same calculation? I don't want to create two vba
process. Could you get me start on this?

Thanks again for your help.
Keith

If it's too complex for a query, it's probably too complex for a text box
too.

Other possibilities:
- Custom VBA function to perform the complex calculations

- Write the results of a calcuation to a temp table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

thanks for the quick reply!!!!

while....i guess i'll have to go back to the drawing board on this.
Some
of
the calculation in the text boxes are very complex. I get an
noification
that the field is to long when i put the information into a query. The
calculation have a multi nested if statements.

Do you have any other recommendation, that might help me in achieving
the
reports for each record with different calculations?

thanks again
Keith

:

The best approach might be to create a query, and put the complex
calculations into the fields of the query. You can then use this query
as
the source for both your form and report.

In a report, you can read a text box from a form, but setting the
Control
Source of a text box on the report like this:
=[Forms].[Form1].[Text0]
However, that will show the same value in every record of the report,
so
it
is probably no good to you.

Hi,

I'm hoping someone can help me with this.

First of all, i created a form with multiply text boxes with complex
calculations in each text box. When you move from one record to
another
record, each text box will change due to the calculation.

Now what i need to do is to create a separate report for each
record.
I
started creating a report by copying and pasting the text boxes
from
the
form to report in design mode. If at anytime the calculation
changes, i
don't
want to change the calculation on the form and also on the report.
(to
much
of chance of forgetting to change both places)

Is there a way i can sort of link the calculation from the text box
(form)
to the report? (Only correct the formula on the form and don't need
to
correct the calculation text box on the report...ONLY ONCE)

The calculation will change with each record on the report when it
is
printed.

Could you tell how to link the information from the form to report?
It
is
possible.

 

[Allen Browne]

Yes: create a new standard module.
Ctrl+G opens the Immediate Window.
From there choose Module on the Insert menu.

Whether that's the best way forward will depend on what you are doing. If
you can achieve it in a query without writing your own function, that might
be better still.