Heya.

I am developing a new system and I am have come across a situation that always has me questioning myself. So, instead of just flipping a coin I decided to ask what YOU generally do.

If I have a table with the following fields:

ID (autonumber)
OrderNumber (text)
Buyer (number - foreign key)
field3
field4
field5

Now, I use the ID autonumber to reference all recods as it is faster and easier, but the true primary key is actually the OrderNumber/Buyer Comination.

In my table definition, I could:

1. Make ID the primary key and then use logic in my forms to ensure that Order Number and Buyer exist and are a unique combination.

OR

2. Use the OrderNumber/Buyer comination as the primary key (which means all table level checks will be done so no chance of me mssing a check).

The problem with method 1 is that I could fail to check everything if I have multiple forms.

The problem with method 2 is I get the "The field x cannot contain a null value because the required property is set to true..." when just navigating from the required field to another field on the form. This doesn't make much sense to me as I would think this check only needs to be done when committing the record, but it apparently feels the need to yell at the user while he/she is still just editing the record data.

So... What way do you guys do this?

Thanks,
Travis

There are lots of posts on this subject about using a "natural" (i.e. Order Number) key rather than generated key (i.e. Autonumber). The general consensus here is to use the generated key. You can make OrderNumber required and unique if you wish.

HTH

THanks for the reply!

I may not have specified enough focus where I should have. The problem I am having is that my "key" is made up of 2 fields. Thus, if I use an auto-key I would have to do extra processing to check to make sure a new record does not have a combination similar to one that already exists.

In my example one buyer may have his own set of purchase order numbers while another buyer may share some of the purchase order numbers. Thus, to differentiate between the same purchase order numbers I must specify who the buyer was (and therefore the key is the buyer/po combination)

Would you still use the generated key in this situation? There is no way I can tell to specify a grouping of 2 fields as needing to be required and a unique combination other than to make them the key.

Thanks again, and any help is muuuch appreciated. I just can't wrap my brain around which way is better

You can set a multi-field index that makes sure the combination of 2 fields is unique.

Great. Thanks!

A quick google of multi-field index got me everything I needed. Wasn't aware I could do that.

I don't know what I would do without you guys!

Hopefully you'll never have to find out