Form Calculations

[Guest]

I am trying to to make a field that uses a value from another form (A) in a
calcuation for a field in form B. I've made the control source the field in
Form A but now the calculation in Form B is only done if Form A is open.

I guess my problem is that the original field in Form A isn't bound/stored
in a table because it too is a calculation.

Is there a way the store the value of a calculation in a table?

 

[Guest]

I am trying to to make a field that uses a value from another form (A) in a
calcuation for a field in form B. I've made the control source the field in
Form A but now the calculation in Form B is only done if Form A is open.

I guess my problem is that the original field in Form A isn't bound/stored
in a table because it too is a calculation.

Is there a way the store the value of a calculation in a table?

 

[Guest]

Yes, but I would not suggest that method.
Here is a way you can do it. Below is a sample function that uses a static
variable. Put it in a standard module so it is visible to the entire
application.

Public Function TestOnly(Optional ByVal varValue As Variant) As Variant
Static varReturn As Variant

If Not IsMissing(varValue) Then
varReturn = varValue
End If
TestOnly = varReturn
End Function

If you are not familiar with static variables, they differe from normal
varialbes in that they retain their value between calls while normal
variables loose their value when the procedure is exited.

The above function uses an optional argument. If an argument is received,
it is stored in the static variable, replacing the existing value of the
varialbe. If no argument is received, it does not change the value of the
static variable. In ehtier case, it returns the current value of the static
varialbe.

So to use it, you would use the After Update event of the control on FormA
to put the value in that FormB will use:

TestOnly(Me.MyControlName)

Then make the function the control source for the control on FormB
=TestOnly()

Notice that FormA sets the value and FormB retrieves it.

 

[Guest]

Klatuu's advice is superior - but to answer your specific question - yes -
you could use the MakeTable query method to put that value in a table. I
will confess to using this approach......

 

[Guest]

Rereading your post, I notice that the control on FormA is a calculated
field. Therefore, it is not likely you will be able to use it's After Udate
event to load the function varialbe. You will have to find a place somewhere
in your form that once you know what the value is, you can load the function.
If there is no other logical place, then you may try the unload event of the
form.

Pardon the earlier oversight.

 

[Guest]

Thank you both for the advice. I think the first method is a little over my
hear. How would I do a MakeTable query?

 

[Guest]

It really isn't over your head and it is a good learning experience.
Here is all you need to do:

Copy the code I posted into a standard module. A standard module is a
module you select from your database window under Modules. If you don't have
any, create a new one. Just don't name it the same name as the function.
Paste the function into it and save it.

Open FormA in design mode. Open the Properties dialog, select form and
select the Events tab.
Find On Unload. Click it. Select Code Builder. The VB editor will open.
Type this in the editor:

TestOnly(Me.MyControlName) Use the name of the control that has the result
of the calculation.

Open FormB in design mode. open the Properties dialog, select form and
select the Data tab.
Enter this in the text box for Control Source
=TestOnly()

That's all there is to it.

 

[Guest]

Can I still use this? Even with the first value a result of a calculation as
well?

 

[Guest]

Also, I do actually need the results of the calculations to be stored in my
table along with the rest of the information so that I can easily include it
in my reports. Any other way I can do it?

 

[Guest]

One thing you should not do is store calculated values in tables. You should
do the calulation when you need to present it to the user on a form or a
report, or in a query.

In this case, you can do the calculation in the report itself. If you have
the other information in the table or query your report is buit on.

 

[Guest]

Could I place the info in a query and then use a code (ex: Discounted price:
CCur([xzasd].gift*discount)? How do I know what to use, CCur or something
else?

 

[Guest]

Queries do not store any data. Only tables store data. Queries only
retrieve or modify data in tables. Ccur is fine if you are dealing with
money, which seems to be the case. I don't know which Form the formula is
for. Is it FormA or FormB?

There are two ways to do this. One would be leave FormA open while you are
using FormB and base the formula in FormB on the control in FormA. The other
is to use the technique I posted earlier.

I would not use a table, particularly a Make Table. I never use Make Table
queries at all, for a number of reasons. The two main reasons I would not
use a table is for performance reasons. It takes a lot longer to write the
data to a table in one form and read it from another than it does to do it in
memory. The other is that if there is any kind of error, the data could end
up being missing or incorrect.

Let's give it a try. I will be happy to help you through it. I really
would like you to do it properly, and it will be a good learning experience
for you.

Could I place the info in a query and then use a code (ex: Discounted price:
CCur([xzasd].gift*discount)? How do I know what to use, CCur or something
else?

One thing you should not do is store calculated values in tables. You should
do the calulation when you need to present it to the user on a form or a
report, or in a query.

In this case, you can do the calculation in the report itself. If you have
the other information in the table or query your report is buit on.