Default table field value to depend on another field value

[slames]

I have a table with a list of subscriptions, if their subscription is of class "free", then I want the default value of status to equal active, otherwise I want the default of this field to be inactive. This is what I've put in the default field of the design of table but it causes errors,

IIf(([tblSubscription].[scClass])="2","1","3&quot

can anyone help?, Thanks
Steph

 

[WildHare]

Unfortunately, Access does not allow cross-table field validation rules in schema design. The best you can do here is try to determine WHICH class of subscription is/will be most common, and set the default status to that code, and then through your form(s) or otherwise, change it when appropriate.

However, you might not even need to STORE a "status" field, if it can be directly derived from your "class" field - just compute it with an expression in a query when you need it.

In other words, why bother storing TWO things, that essentially have a one-to-one relationship:

Class Status
----- ------
Free Active
NotFree inActive


It would all depend on HOW MANY statuses a NON-free class of subscription could have.

You could also handle this with a look-up table as well.

Jim
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm