Storing to a 'calculated' column of an MS Access table

[readams]

I have a table which contains the following columns names/fields: (id, grade1, grade2, grade3, grade4, ages). Grade1 through Grade4 are of type Boolean, and ages is a String type. For a given row, the value of ages is a function of the values of grade1 through grade4, i.e., the value that is to be stored in ages is derived/calculated from the values in fields grade1 through grade4. Examples:

For a given row with the values of grade1 through grade4 as True, False, True, False, the field, ages, in that row should contain the string "grades 1, 3".

For a given row with the values of grade1 through grade4 as False, True, True, True, the field, ages, in that row should contain the string "grades 2-4".

I have written a Visual Basic user-defined function which correctly produces the resultant string based on the values of the grade1 through grade4 fields, but I do not know how to make the result be stored in the ages field of the table. Can anyone please help me?

(Right now, for testing purposes, I am outputting the string to a 'calculated control' on a form. The name of my function is "F1", and in the properties Control Source of the field on the form I have "=F1()". But I would like the result of my function to be stored in the ages column of the current row.)

Thank you.

Richard E. Adams
Email: RAdams@dhs.ca.gov

--------------------------
Richard:

Is it necessary to store the calculated field in the table when it can be produced on a report or form by using your function on the report or form? From a purist point of view, one should not store calculated data in a table containing the raw data from which it can be produced. It unnecessarily uses up storage. (Sorry, I get carried away sometimes.)

Vic
--------------------------

VicM,

Thank you for replying to my question. I agree with you that one should not store calculated data in a table containing the raw data from which it can be produced. However, the person I am working for wants this to be done for reasons I am not clear about - possibly so that the calculated value only needs to be computed once, when the grade1 through grade4 values are entered; the resultant value would be stored in the ages field of the table at that time.

Regardless of the reason, doing this does not seem conceptually difficult. But being new to working with MS Access and Visual Basic, I am finding no documentation that explains how one would accomplish this. Can one store a value to the ages field directly from a Visual Basic function, and if so, how?

The function should be executed/evaluated whenever someone is entering data to the table via the table's corresponding data entry form, upon their selecting a combination of the grade1 through grade4 check boxes, or when changing the grade1 through grade4 check boxes, i.e., modifying/updating the values in those fields of an existing record. Could you also possibly explain how to 'program' MS Access so that the function is triggered at the appropriate time(s)?

Thank you.

Richard E. Adams
Email: RAdams@dhs.ca.gov

 

[beetee]

There are numerous ways to write data to a field in a record, including:

1) use an update query.
2) use a form
3) write some visual basic code using recordsets

I don't know of a way to update a table directly. However, one trick I often use is to create a form that uses datasheet view. Then, in the 'beforeupdate' function of that form, I can create some code something like...

Me.Ages = MyVBFunction(Me.grade1, Me.grade2, ...)


If you need further clarification, please don't hesitate to write.