calculating fields in continuous form

[Rli]

Hi all,
I have a beginners question....
I created a continuous form based om a table with two fields (field1 and
field2)
In the form all records are shown as lines (continuous form)
Now i want to add a new field to each line (field3) calculating the value of
field1 multiplied with the value of field2.
So Field3 is not retrieved from the underlying table and the 'record source'
property of this field is empty.
In fact this is a simplified representation of my question, the calculation
is very complex.
Maybe it is simple to do, but all i tried resulted in giving me the same
awnser in field3 for all records (the awnser i get depends on the record i
click on).

How should i proceed to get the calculation done and shown for each record?

Does anyone understand what i mean?

 

[Allen Browne]

Create a query into this table.

Type the calculation into the Field row of the query, e.g.:
Field3: [Field1] * [Field2]

Use the query as the RecordSource for your form.

 

[Ofer]

In the controlSource of the third field write

=[Field1]*[Field2]

 

[Rli]

Thanks, but...
for this simple problem one can do that.
My form is in fact a subform and in my case the 'calculation' is very
complex with a lot of discission making etc depending on both
'master'-formfields and direct query values. I wrote a VBa routine for that
and it works fine except i don't know how to trigger the routine for each
record individually. When i use the On-current event of the form all fields
field3 of each record are filled with the same value (of the current record
in the subform).


Isn't there any way to trigger by record and show the awnser 'by record'?
:

Create a query into this table.

Type the calculation into the Field row of the query, e.g.:
Field3: [Field1] * [Field2]

Use the query as the RecordSource for your form.

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

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

Hi all,
I have a beginners question....
I created a continuous form based om a table with two fields (field1 and
field2)
In the form all records are shown as lines (continuous form)
Now i want to add a new field to each line (field3) calculating the value
of
field1 multiplied with the value of field2.
So Field3 is not retrieved from the underlying table and the 'record
source'
property of this field is empty.
In fact this is a simplified representation of my question, the
calculation
is very complex.
Maybe it is simple to do, but all i tried resulted in giving me the same
awnser in field3 for all records (the awnser i get depends on the record i
click on).

How should i proceed to get the calculation done and shown for each
record?

Does anyone understand what i mean?

 

[Allen Browne]

If you want Access to calculate it for each record individually, you cannot
use Form_Current.

You will need to create a query that draws records from enough tables to get
all the arguments to pass to your calculation function. Use outer joins
where appropriate.

If you cannot pass all the arguments to your function, then it will have to
look up the arguments based on the values you do pass it. Typically it will
need to OpenRecordset() or DLookup() the values it needs. These are not
quick operations, and since they are called by every row of your continuous
form, things could get slow.

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

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

Thanks, but...
for this simple problem one can do that.
My form is in fact a subform and in my case the 'calculation' is very
complex with a lot of discission making etc depending on both
'master'-formfields and direct query values. I wrote a VBa routine for
that
and it works fine except i don't know how to trigger the routine for each
record individually. When i use the On-current event of the form all
fields
field3 of each record are filled with the same value (of the current
record
in the subform).


Isn't there any way to trigger by record and show the awnser 'by record'?
:

Create a query into this table.

Type the calculation into the Field row of the query, e.g.:
Field3: [Field1] * [Field2]

Use the query as the RecordSource for your form.

Hi all,
I have a beginners question....
I created a continuous form based om a table with two fields (field1
and
field2)
In the form all records are shown as lines (continuous form)
Now i want to add a new field to each line (field3) calculating the
value
of
field1 multiplied with the value of field2.
So Field3 is not retrieved from the underlying table and the 'record
source'
property of this field is empty.
In fact this is a simplified representation of my question, the
calculation
is very complex.
Maybe it is simple to do, but all i tried resulted in giving me the
same
awnser in field3 for all records (the awnser i get depends on the
record i
click on).

How should i proceed to get the calculation done and shown for each
record?

Does anyone understand what i mean?

 

[Rli]

Hello , thanks for replying.
When i do what you suggest it gives me the error 'you cannot assign a value
to this object'.
Any ideas? the form and field is edittable etc....

In the controlSource of the third field write

=[Field1]*[Field2]

--
I hope that helped
Good luck

Hi all,
I have a beginners question....
I created a continuous form based om a table with two fields (field1 and
field2)
In the form all records are shown as lines (continuous form)
Now i want to add a new field to each line (field3) calculating the value of
field1 multiplied with the value of field2.
So Field3 is not retrieved from the underlying table and the 'record source'
property of this field is empty.
In fact this is a simplified representation of my question, the calculation
is very complex.
Maybe it is simple to do, but all i tried resulted in giving me the same
awnser in field3 for all records (the awnser i get depends on the record i
click on).

How should i proceed to get the calculation done and shown for each record?

Does anyone understand what i mean?

 

[Rli]

Thanks Allen for responding,
I have read and reread you suggestions...but actually, i don't understand it.
What do you mean when you write 'If you cannot pass all the arguments to
your function, then it will have to look up the arguments based on the values
you do pass it. Typically it will need to OpenRecordset() or DLookup() the
values it needs.'

You first say the only way is to create a (very very complicated) query and
base the form upon it. Okay, that part i understand, but where does the
Openrecordset or Dlookup come into this???? One can call a VBA routine in a
sql-query? How do you do that?
Can you show me an example of such a query?

 

[Allen Browne]

You say you have written a function that performs the calcuation.

The ideal is to create a query that gives you all the arguments you need to
pass to your function from the query. If the function is named CalcFee(),
and it needs to know the ProductTypeID form the Product table, the TaxRate
from the Product table, the ClientRate from the Client table and so on, you
would include all these tables in the query, and type something like this
into the Field row of your query:
CalcFee(Product.ProductTypeID, Product.TaxRate, Client.ClientRate)

If you can't do it that way, you could pass into the function just the
OrderID from the record, and write the function to lookup all the other
values in the other tables associated with this entry. So the Control Source
of your text box would contain:
=CalcFee([OrderID])
and the function would OpenRecordset() or DLookup() to get the ProductTypeID
and TaxRate from the Product table, and the ClientRate from the Client
table, and so on.

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

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

Thanks Allen for responding,
I have read and reread you suggestions...but actually, i don't understand
it.
What do you mean when you write 'If you cannot pass all the arguments to
your function, then it will have to look up the arguments based on the
values
you do pass it. Typically it will need to OpenRecordset() or DLookup() the
values it needs.'

You first say the only way is to create a (very very complicated) query
and
base the form upon it. Okay, that part i understand, but where does the
Openrecordset or Dlookup come into this???? One can call a VBA routine in
a
sql-query? How do you do that?
Can you show me an example of such a query?

 

[Rli]

So, if i understand it right one can use the query:

SELECT [field1],[field2],field3:calcfee([field1],[field2)
FROM [atable]
WHERE something;

and then base a continuous form with these 3 fields showing per record??
The calcfee() function should be public function in a module ? Up to now it
was a on-current click event.

That would be the perfect awnser. Thank you very much for this idea

You say you have written a function that performs the calcuation.

The ideal is to create a query that gives you all the arguments you need to
pass to your function from the query. If the function is named CalcFee(),
and it needs to know the ProductTypeID form the Product table, the TaxRate
from the Product table, the ClientRate from the Client table and so on, you
would include all these tables in the query, and type something like this
into the Field row of your query:
CalcFee(Product.ProductTypeID, Product.TaxRate, Client.ClientRate)

If you can't do it that way, you could pass into the function just the
OrderID from the record, and write the function to lookup all the other
values in the other tables associated with this entry. So the Control Source
of your text box would contain:
=CalcFee([OrderID])
and the function would OpenRecordset() or DLookup() to get the ProductTypeID
and TaxRate from the Product table, and the ClientRate from the Client
table, and so on.

 

[Allen Browne]

Yes, that's the idea.

The syntax would be:
SELECT [field1],[field2], calcfee([field1],[field2) AS field3
FROM [atable]
WHERE ...

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

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

So, if i understand it right one can use the query:

SELECT [field1],[field2],field3:calcfee([field1],[field2)
FROM [atable]
WHERE something;

and then base a continuous form with these 3 fields showing per record??
The calcfee() function should be public function in a module ? Up to now
it
was a on-current click event.

That would be the perfect awnser. Thank you very much for this idea

You say you have written a function that performs the calcuation.

The ideal is to create a query that gives you all the arguments you need
to
pass to your function from the query. If the function is named CalcFee(),
and it needs to know the ProductTypeID form the Product table, the
TaxRate
from the Product table, the ClientRate from the Client table and so on,
you
would include all these tables in the query, and type something like this
into the Field row of your query:
CalcFee(Product.ProductTypeID, Product.TaxRate, Client.ClientRate)

If you can't do it that way, you could pass into the function just the
OrderID from the record, and write the function to lookup all the other
values in the other tables associated with this entry. So the Control
Source
of your text box would contain:
=CalcFee([OrderID])
and the function would OpenRecordset() or DLookup() to get the
ProductTypeID
and TaxRate from the Product table, and the ClientRate from the Client
table, and so on.

Thanks Allen for responding,
I have read and reread you suggestions...but actually, i don't
understand
it.
What do you mean when you write 'If you cannot pass all the arguments
to
your function, then it will have to look up the arguments based on the
values
you do pass it. Typically it will need to OpenRecordset() or DLookup()
the
values it needs.'

You first say the only way is to create a (very very complicated) query
and
base the form upon it. Okay, that part i understand, but where does the
Openrecordset or Dlookup come into this???? One can call a VBA routine
in
a
sql-query? How do you do that?
Can you show me an example of such a query?


:

If you want Access to calculate it for each record individually, you
cannot
use Form_Current.

You will need to create a query that draws records from enough tables
to
get
all the arguments to pass to your calculation function. Use outer
joins
where appropriate.

If you cannot pass all the arguments to your function, then it will
have
to
look up the arguments based on the values you do pass it. Typically it
will
need to OpenRecordset() or DLookup() the values it needs. These are
not
quick operations, and since they are called by every row of your
continuous
form, things could get slow.