setting derived values on table

[JesOakley]

yes, I know this is a bad idea, but the table is an old one and the fields can't be changed, so...

I have a x-ref table that has two text fields (A and B) and a primary key derived value (C) that is the other two added together.

I have ploughed through oodles of help files, masses of tektips posts and tried everything I can think of, but there doesn't appear to be any way at table level of defaulting C to be A & B. I can't even see how to validate C to make sure it is A & B. No amount of brackets or quote marks seem to help.

Please bear in mind I am trying to do this against the table, without the use of forms or vba etc, etc.

Can anyone help me?

Thanks in advance.

 

[PHV]

Use a table validation rule:
C=A & B

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JesOakley]

Hmmn, got 'cannot use multiple columns in a column-level CHECK constraint' when I tried [C]=[A]& in C's validation field.

 

[PHV]

Please, reread carefully my suggestion (the bold stuff).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JesOakley]

I appreciate your patience here, because obviously I'm being incredibly dense, but what is a table validation rule if it's not going into the table design, clicking on the field, and then entering your suggestion into the validation rule? Is there some other way that I'm missing?

 

[dhookom]

There are Field validation rules and Table validation rules.

If you really need to store the computed value, you can use code in your data entry forms to set the value of the computed field. I expect you could use the Before Update event to stuff the calculated event into your derived field.

Duane
Hook'D on Access
MS Access MVP

 

[fneily]

You could, in design view, just change the primary key from the "C" column and have a multi-field primary key with A and B. I don't understand the rationale behind the developer's reasoning. If he needed a concatenation, then do so in a query.

 

[JesOakley]

My intention was to try and do this without queries, without forms, just purely against the table so as to minimise the work involved. As I said up front if I had developed this from scratch then I wouldn't have done it this way either!

However, when all is said and done, I'm surprised that there seems to be no way in datesheet view of auto-populating a field based on previously entered values. Unless of course, you know different...

 

[JesOakley]

Aha! I have found out where you set table-level as opposed to field-level validation! I never thought to go into properties when the table is open. You live and you learn. I can now validate, which isn't quite as good as deriving the field, but it's better than nowt. Thanx folks.