Default Value of table fields

[Guest]

Hi, All
Is it possible to assign Default Value of table fields by a function of any
other field(s) of the same table in the field properties of table design
screen (Without using of any forms, query and so)? If yes. How?
Thanks, A.NIAKI

 

[Duane Hookom]

You can't do this in the field properties. You might be able to set this as
a validation rule in the table properties.

 

[Tim Ferguson]

Is it possible to assign Default Value of table fields by a function
of any other field(s) of the same table in the field properties of
table design screen (Without using of any forms, query and so)? If
yes.

You have asked similar questions within a couple of minutes, but the
change from DefaultValue to ValidationRule is quite a big one!

Just to explain:

A ValidationRule is an absolute rule that _has_ to be true before a
record can be stored in the database... this is regardless of how the
field is being updated (new record or update, table sheet or query or VBA
procedure, etc). If it says "MyField < Date()" then there is no way that
a value from the future can find its way into the record. Ever. A
ValidationRule can refer to other fields in the same record ("Colour IS
NULL OR PaintValue>0") but you have to enter these rules into the Table
Properties, not the field properties. A ValidationRule can even refer to
values in other tables, but you have to go round the houses to enter one
like that.

A DefaultValue gets looked up at the instant of the creation of the
record, before any other value has been filled in. It cannot refer to
another value in the same record, simply because there are no other
values in the record at that time. Once the empty record has been created
with its DefaultValues, only then are those values overwritten by the
values that the user wants.


I suppose it's possible to muck about with a DefaultValue but you'd
probably find it easier just to provide a real value instead.

A ValidationRule is a critical component of the data integrity model and
therefore it should be designed once and then left alone. In any case,
ideally the front end will be validating user data before they get to the
db engine, so users really shouldn't get to see a ValidationRule error.
Ideally! <g>


Does that help to answer the questions?

Tim F

 

[Guest]

Thanks for your reply and explanation, but let me say:
1-I believe a ValidationRule of each field actions at the same time of
updating data of the field. But a ValidationRule of a table will actions at
the time of record updating. May you try it?
2-I didn’t get my answer about ValidationRule, my second question was about
using of user function for a ValidationRule . attention please; How can I use
a user_function(suppose “MyFunction()†check the argument and retune
True/False) to check the data of a field (Ex.: MyField) at data entry time,
like this Expression: “ My Funcheion[“MyFieldâ€] in table design. Is it
possible?

Tanks in advance.
A.NIAKI

 

[Tim Ferguson]

Thanks for your reply and explanation, but let me say:

1-I believe a ValidationRule of each field actions at the same time of
updating data of the field. But a ValidationRule of a table will
actions at the time of record updating.

Sorry: I don't see the difference. If one field is updated, the record is
updated. I really don't know whether field-level rules are evaluated for
fields that are not updated but I wouldn't know how to test it and don't
really care anyway.

2-I didn’t get my answer about ValidationRule, my second question
was about using of user function for a ValidationRule . attention
please;

Not allowed. ValidationRules work at the level of the db engine, and
therefore know nothing about VBA, forms, macros or any other part of the
Access GUI. There are several built-in functions available to the db
engine, and it's often possible to build a suitable expression using just
them.

If you are able to post details, we may be able to help.

B Wishes


Tim F