Primary Key

[RexesOperator]

Can a PK be a calculated field?

I want to be able create a PK that consists of three strings being added together. The first two would be collected from cascading combo boxes. The third field is a simple text field.

I would then run DLookup to make sure the final string is unique.

I have reasons for not using an autonumber field (there are compatibility issues with existing tables from a much older dbase database).

 

[pbaldy]

Rather than add a calculated 4th field, you could simply make the other 3 fields keys (called a compound key).

 

[RexesOperator]

The problem is the last text field is need as part of the ID. So is this doable? I realize it probably violates a number of rules, but this is a unique situation.

 

[pbaldy]

Yes, it can be done.

 

[The_Doc_Man]

First, use DCOUNT (not DLOOKUP) to assure uniqueness. Using Asimov's theory of numbers, there are only three numbers of interest: 0, 1, and many. If you have DCOUNT returning 0, your index could be unique. If 1, it IS unique (and in use). If anything else (many), then it is not unique.

Second, a PK can be a concatenated field or a compound key but in the strictest sense of your question, it cannot be a computed field - for the simple reason that you cannot put a formula in a table. Queries can have formulas but don't happen to have keys of their own. (They inherit keys from the component tables.) But that's a techie quibble.

Now, how you derive the intended value of a key is your business. Having a mixture of drop-downs and direct-entry text is as legal as any other method as long as you check what you did before you try to store it. Or trap the error you would get if you tried without checking.

If the resultant key is truly a candidate key (i.e. meets uniqueness tests and relevance tests), then no, this doesn't violate normalization.

 

[RoyVidar]

It's ages since I did something like that, but I don't think it's impossible to use "multi-field PKs" with combos or lists, neither very difficult. Isn't it just setting the bound column to 0, then do some smallish code in the forms on current event and the combo/list after update event?

 

[RoyVidar]

I have tried, several times (though it is a while ago), and I did consider my previous reply as the report you're asking for. I think I gave all relevant information there, but let me repeat them, and try to be a bit more precise.

If you don't have a single unique field in the rowsource, then there is inbuilt functionality to create such, which I described in my previous reply. Setting the Bound Column property of the combo to 0, makes the Value property return the ListIndex - which is unique (there is no need to do any concatenation scheme). Check it out in the help file.

When suggesting to set the Bound Column to 0, I'm not suggesting to store the ListIndex, I should perhaps have been clearer on that, but the combo or list, need to be unbound, and syncing it with the form recordsource, will need to be performed, for instance in the forms on current event.

In addition, one would also need to assign values to the foreign key field programatically, in the after update event of the combo or list.

This is what i refer to as "some smallish code".

So, unless you're getting those strange results in the attached, then my view is that this is not impossible (as you suggested), it is in my view not even difficult. Though it is quite simple to use combos and listboxes with one field pks, I don't really see this here as more difficult. It's just "some smallish coding".

 

[RoyVidar]

Thank you!

 

[The_Doc_Man]

Here, I am simultaneously a pragmatist and a purist.

I'm with Pat in that when you have no single candidate key, you will have easier work ahead by using an autonumber PK.

But... I'm also with those whose business naturally generates something that would be a legit candidate key as PK. Adding an autonumber PK is, at that point, clutter.

Where is the dividing line? Size of the key. An autonumber is a LONG integer. If your compound PK is longer than, say, two LONGs, you are already starting to consume index space like it was going out of style. You'd do better with shorter keys. It is a geometric type of progression as to how much worse it gets as you add bytes to the size of the PK.