>>>-- snip --
>>Sorry I didn't reply sooner. I thought for a while about your answer. My cmdadd uses an APPEND rather than an INSERT, because that's how the wizard did it. I seem to remember that there was a thread explaining why INSERT is better, but I can't remember what its reason was. My required fields have no default values, so I don't want to save a record with the wrong values filled in. The table that I was given has no primary key. I am getting them to add stuff so that unique keys can be generated, but for the moment it has to work without them. Are you saying you copy your primary key values to another table?
>
>First, I use INSERT rather than APPEND BLANK because I DO have unique IDs and this way there is never a moment when there is a record with an empty ID field. INSERT adds data to fields while it appends the record.
>
>Second, I use a table with a record for each of my data tables. Each record holds the table name, ID field name and matching index (usually the same name) and a field containing the last ID value used. When I need to insert a record, I lock the appropriate record in the ID table, increment the numeric field, check for duplicates and unlock the ID record. Then I'm ready to do an INSERT in the original table. All my IDs start with numbers turned into strings (pad the left characters with '0'), but some apps use a 3-char suffix to show where in multiple installations a record originated.
>
>HTH
>Barbara
I never liked those key fields consisting of arbitrary numbers whose only virtue is their uniqueness. Since everyone seems to use them, it must be the right way to do things, but I like to try to use the key field to solve my ordering problems for me as well. In the app I'm thinking about now, my colleagues want to order the data on an "item_no" field which is a complex series of letters, numbers, and a decimal point. They want the numbers in it to order like numbers. I wrote a function which loops through the key string and generates a key which will give them the correct order when used as an index. That way, there are no UDF's in any index expressions or query expressions to slow things down. I think I can get away with this, because my colleagues recognize the need to make the records unique and are willing to add some data to make them so, for their benefit and mine. (I realize that this is a luxury we don't all have.) After they have done this, I figured I would then make the index PRIMARY and use a trigger or error trapping or something to handle attempts to create duplicate records. Do you think I will get away with all this, or am I getting myself into trouble?