>Hi all,
>
>Ok, a *really* basic question, but I'm curious what you are all doing out there with this one.
>
>What type of index do you use with your key fields in tables? My books suggest Primary or Candidate, and I've also tinkered with Unique. I have encountered problems in the past when adding new records with 'key field index uniqueness violated' or some such message and thought it might be caused by the brief moments between when a record is newly created (append blank) and the line of code runs that assigns a new key value to it and plops it into the appropriate field.
>
>Am I making any sense? Just curious :).
>
>Mandy

Hi Mandy,
Yes, you're making a lot of sense. DON'T use the Unique index. What that does is prepare an index that shows only 1 record for each value of the field(s) in the index. This can be VERY difficult to work with. Primary and Candidate each insist on having the value of the index field(s) distinct/unique (I'm hesitating to even use the WORD unique here), and this is what you want.

Try using INSERT instead of APPEND BLANK. If you already have your key field value, and include it in the INSERT command, then you never have the 'brief moments' you mentioned above. In fact, the append blank scenario most frequently bombs when you have a record with a blank value already in the table because of a past problem.

We put the code for the Unique IDs into the DEFAULT value of the key fields, so there's no problem about the information being added correctly even when working with the tables from the command box.

We opted to never use Candidate indexes because of the difficulty of making absolutely certain of not having duplicates. Instead, we run a validation routine using a SQL cursor on the table which we've found is solid for our system where data is mostly added programmatically from outside sources, not by user entry.

HTH
Barbara