storing calculated values

[Sanjo]

I understand the conventional wisdom of not storing calculated values on the table, but I have a need to do do so. On my Input Screen I have a generated Certificate # derived from 5 single value fields keyed onto the screen. This certificate # is unique to those 5 single-value fields which should not be repeated. I want to store it on my Table as the Primary key with "no duplicates" so that if a keyer keys the same 5 single-digit values , thus creating the same Certificate #, he will get a MsgBox back telling him he cannot add a duplicate entry.

attached is my table and Input Screen.

Can someone give me the steps to achieve this?

thanks in advance.

 

[plog]

Nope, sorry, you're situation isn't an exception. You shouldn't store this data in one field. You can create a composite primary key, which is a primary key based on multiple fields (http://office.microsoft.com/en-us/access-help/create-or-modify-a-primary-key-HA010341487.aspx).

As for your form, even if it isn't set up to have a composite primary key, you can still achieve what you want using VBA to see if the 5 fields have existing matches in your table. Then if a match exists throw the message, if not allow the insert.

You haven't found an exception.

 

[Sanjo]

Thanks for your help. I don't know VBA, but would like for the input form to be able to do what you suggest. In the meantime, by make a primary key on multiple fields on the Table still throws out the "Runtime error 2105. You can't go to the specific record" message. It prevents the keyer from duplicating records even if it isn't the cleanest way to do it. I will have to find some help on creating the appropriate VBA code for the Input Screen.

thanks for your time and help.

 

[gemma-the-husky]

if your certificate number is generated once, and will never change - then go ahead and store it

it might save a bit of time not having to compute it when you need it - even if strictly it is superfluous.

the problem with calculated fields is often that they drift out of step with the underlying data.