How to store calculated fields ?

Hi all,

NB : For me, this question is not about conception and NOT storing calculated fields.

In my form, I ask the user to fulfill a few fields, then I calculate other fields from that. I am using this because I then use the Chart/Graphic view from my Table to display some informations.

For example, I want to be able to ask for a number, say '17' in a 'enter_number' field, then concatenate it with 'w' in my calculated fields with '="w"&[enter_number]' . I would like to store that field. This here would create a column that would represent the different weeks of a year. That column would be used as an axe on my chart.

Putting the formula in the 'ControlSource' property field doesn't update the DB (no link, I guess). Putting it in different 'Event' property fields don't solve my problem. Do I have to write any VBA 'Sub' or use a 'Request' to store that value ?

Please don't answer me that I shouldn't store calculated fields since my databse will be a small one with no consideration of wasted space

Thanks in advance

Nico

Nico,

Yes, you will need VBA code to store a calculated field.
Place a control on your form bound to the to-be-calculated
field. In the After Update event of the source field, use
something like:




-----Original Message-----
Hi all,

NB : For me, this question is not about conception and
NOT storing calculated fields.

In my form, I ask the user to fulfill a few fields, then
I calculate other fields from that. I am using this
because I then use the Chart/Graphic view from my Table to
display some informations.

For example, I want to be able to ask for a number,
say '17' in a 'enter_number' field, then concatenate it
with 'w' in my calculated fields with '="w"&
[enter_number]' . I would like to store that field. This
here would create a column that would represent the
different weeks of a year. That column would be used as an
axe on my chart.

Putting the formula in the 'ControlSource' property field
doesn't update the DB (no link, I guess). Putting it in
different 'Event' property fields don't solve my problem.
Do I have to write any VBA 'Sub' or use a 'Request' to
store that value ?

Please don't answer me that I shouldn't store calculated
fields since my databse will be a small one with no
consideration of wasted space

Thanks in advance

Nico
.

Nico,

Yes, you will need VBA code to store a calculated field.
Place a control on your form bound to the to-be-calculated
field. In the After Update event of the source field, use
something like:




-----Original Message-----
Hi all,

NB : For me, this question is not about conception and
NOT storing calculated fields.

In my form, I ask the user to fulfill a few fields, then
I calculate other fields from that. I am using this
because I then use the Chart/Graphic view from my Table to
display some informations.

For example, I want to be able to ask for a number,
say '17' in a 'enter_number' field, then concatenate it
with 'w' in my calculated fields with '="w"&
[enter_number]' . I would like to store that field. This
here would create a column that would represent the
different weeks of a year. That column would be used as an
axe on my chart.

Putting the formula in the 'ControlSource' property field
doesn't update the DB (no link, I guess). Putting it in
different 'Event' property fields don't solve my problem.
Do I have to write any VBA 'Sub' or use a 'Request' to
store that value ?

Please don't answer me that I shouldn't store calculated
fields since my databse will be a small one with no
consideration of wasted space

Thanks in advance

Nico
.

Sorry, Nico. I pressed that Tab key once too many.

The code should be something like:

[YourCalculatedControl] = "W" + [YourSourceControl]

You should be aware, however, that you can achieve what
you want with no programming (and its inevitable
debugging) in a query. Drag the desired fields to the
grid, and create a new calculated field by typing the name
enclosed by brackets, a colon, and the calculation,
enclosing literals in quotes and fieldnames in brackets,
for example:

[Week]: "W" + [WeekNumber]

You can then base your Chart/Graphic view on the query,
which to Access looks just like a table.

You should also be aware that disk space is the minor
objection of developers to storing calculated fields.
More importantly, even if these don't apply to your
current situation:

1) It's faster to calculate the value on the fly than
look it up from a table.
2) Storing it relies on your programming acumen to ensure
that the data cannot be changed outside your form where
you set the calculated fields through procedures. If the
table is edited directly, these procedures won't be run.
On the other hand, the query will produce the accurate
calculation every time.

HTH
Kevin Sprinkel


-----Original Message-----
Hi all,

NB : For me, this question is not about conception and
NOT storing calculated fields.

In my form, I ask the user to fulfill a few fields, then
I calculate other fields from that. I am using this
because I then use the Chart/Graphic view from my Table to
display some informations.

For example, I want to be able to ask for a number,
say '17' in a 'enter_number' field, then concatenate it
with 'w' in my calculated fields with '="w"&
[enter_number]' . I would like to store that field. This
here would create a column that would represent the
different weeks of a year. That column would be used as an
axe on my chart.

Putting the formula in the 'ControlSource' property field
doesn't update the DB (no link, I guess). Putting it in
different 'Event' property fields don't solve my problem.
Do I have to write any VBA 'Sub' or use a 'Request' to
store that value ?

Please don't answer me that I shouldn't store calculated
fields since my databse will be a small one with no
consideration of wasted space

Thanks in advance

Nico
.

Hi Kevin,

thanks for answering but the display of the end of your message is missing...

See above. I accidentally tabbed to the Send button and
hit it twice. I included the rest of the text above.


-----Original Message-----
Hi Kevin,

thanks for answering but the display of the end of your
message is missing...
.

Thanks Kevin,

well I guess the example I gave was way too simple

This time, I want to be able to calculate many fields from a few. For example, I want to be able to fulfill a whole MS Project DB from a few fields. I want to calculate them instead of having MS Project doing it. Well, I know MS Project isn't really complyant with 'Data Importation'

Many fields have to be fulfilled depending of the values of other. That's why I would use 'Formula' such as 'IIF' (not sure. In French, that would be 'VraiFaux', i.e. 'TrueFalse'. This formula makes a logical test, say compares 2 dates, then have 2 available results depending of the test). Do I have to load a 'Sub' in the 'AfterUpdate Event' of my source control ? What would the syntax / process look like ? I am a little familiar with VBA and Excel, but I am not sure about how to insert a calculated 'value' in a 'Control', using (or not) formulas.

By the way, I am thinking of protecting my datas from the users except from the interface. I mean I just want them to access the form, not allowing them to 'edit' the DB. 'DoCmd.Minimize' would only minimize the DB, not 'Hide' it. Is there a way ?

And eventually, is this possible to use 'Basic' Access as a multiuser interface ? I mean I would drop my application on a shared directory, not using any server application (such as MS Project Server nor one dedicated to Access). I would like to use a 'buffer' DB for preventing error from Updating my DB, and a boolean while writting for preventing from reading, assuming that the 'write' operation would take less than a seconde. Is that possible ?

Thanks for your help !

Nico

Beaucoup de questions! See below for specific=20
responses.

-----Original Message-----
Thanks Kevin,

well I guess the example I gave was way too simple

This time, I want to be able to calculate many fields=20
from a few. For example, I want to be able to fulfill a=20
whole MS Project DB from a few fields. I want to calculate=20
them instead of having MS Project doing it. Well, I know=20
MS Project isn't really complyant with 'Data=20
Importation'

Many fields have to be fulfilled depending of the values=20
of other. That's why I would use 'Formula' such as 'IIF'=20
(not sure. In French, that would be 'VraiFaux',=20
i.e. 'TrueFalse'. This formula makes a logical test, say=20
compares 2 dates, then have 2 available results depending=20
of the test). Do I have to load a 'Sub' in=20
the 'AfterUpdate Event' of my source control ? What would=20
the syntax / process look like ? I am a little familiar=20
with VBA and Excel, but I am not sure about how to insert=20
a calculated 'value' in a 'Control', using (or not)=20
formulas.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D
Firstly, let's tackle the idea of a form "control". =20
Reports also have controls, which have similar properties,=20
but for clarity let's restrict the discussion to form=20
controls. Basically, they are a placeholder for data and=20
include the following types: textbox, combo box, list=20
box, option group, checkbox, etc. Most can be either=20
Bound or Unbound (a label is by definition Unbound). If=20
they are Bound, then what is entered into the control is=20
automatically stored in the field to which it is bound. =20
So include bound controls for all of your calculated=20
fields on your form. The easiest way to do this is to=20
drag and drop them from the View, Field List box.

@IIF is a VBA function that evaluates a logical statement,=20
and returns one of two values, such as:

Gender =3D @IIF([GenderType]=3D"M", "Male", "Female")

Which assigns the result to a variable named Gender.

They can be nested, although I don't know how many=20
levels. Beyond a simple case, I prefer one of the other=20
logical VBA constructs, If.Then or Select Case..

If [GenderType] =3D "M" Then
Gender =3D "Male"
Else
Gender =3D "Female"
EndIf


Select Case [GenderType]
Case "M"
Gender =3D "Male"
Case "F"
Gender =3D "Female"
Case Else
Gender =3D "On ne sait pas!"
End Select

Since they depend on more than one field's value, use the=20
Form's AfterUpdate event to set your calculated fields. =20
If you wish to do error-checking on what's been entered,=20
use the BeforeUpdate event, which is called before writing=20
the changes to the table.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D

By the way, I am thinking of protecting my datas from the=20
users except from the interface. I mean I just want them=20
to access the form, not allowing them to 'edit' the=20
DB. 'DoCmd.Minimize' would only minimize the DB,=20
not 'Hide' it. Is there a way ?

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
This is a complex issue in which I'm not expert. I=20
suggest you post a message specific to it for the MVP's.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
And eventually, is this possible to use 'Basic' Access as=20
a multiuser interface ? I mean I would drop my application=20
on a shared directory, not using any server application=20
(such as MS Project Server nor one dedicated to Access). I=20
would like to use a 'buffer' DB for preventing error from=20
Updating my DB, and a boolean while writting for=20
preventing from reading, assuming that the 'write'=20
operation would take less than a seconde. Is that=20
possible ?

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
Access is by nature a multiuser application. For multiple=20
users to use it, they each must have a valid copy of=20
Access installed, or you must provide a run-time version=20
of your application. The latter can only be done if you=20
own the Developer's version, and will require additional=20
error-checking programming and toolbar building on your=20
part. A run-time error not trapped by the programmer will=20
cause an ungraceful exit (avec deux pieds gauches) from=20
the application, and all functionality you wish the user=20
to have must be built from scratch on custom toolbars.

a "buffer DB"-I've always done all error-checking at=20
the control and/or form level, "forcing" the user to enter=20
valid data. Again, this is a complex issue; I suggest you=20
post a question specific to it.

Good luck with your project. A v=D4tre service.=20

Kevin Sprinkel
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
Thanks for your help !

Nico
.

Merci beaucoup

Me again
One last question...I promise !

I don't really get which property controls the value of the bound field I want to be calculated. After the update of the 'Source Control', I should run a 'Sub' that would change the value of the calculated field. If its name is 'calculatedField' bound to a 'Field1' in the table and set in the 'ControlSource' property, would something like ' [calculatedField]=IIF(logical test;value if true;value if wrong) ' or ' [calculatedField].Value=IIF(logical test;value if true;value if wrong) ' work ?

I guess this is my last question !
thanks for keeping helping newbie or not !