<< Is it better to grab the next number from the permanent table and reserve it or generate the key in the temporary table and then check to see if it used? Or is there another way that I hadn’t thought of (most likely J). >>
You basically have three choices: 1. Pull the next number (with NEXT()) off the common numbering scheme as you create the temporary records. This will give you a "hole" in the numbering system every time someone cancels an addition (but you have that already when someone cancels now). It also means that you will not be able to use your autonumbered column as a proxy for order-of-entry since user A may get an ID but sit there entering a record while user B gets the next number and commits her record to the main table before user A finishes. 2. Use temporary autonumbers in your temporary tables and then renumber the records when committing them back to the main table(s). This is easy if you're only talking about a one-record structure, but if the autonumber flows through some PK/FK relations it's a bit trickier. Basically, you APPEND or INSERT the "main" record into the real table then do SELECT IDCol INTO vIDVal FROM RealTable WHERE COUNT = INSERT to find out the just-created id number. You update this into the remaining temporary tables and APPEND or INSERT those back into their real tables. 3. Use some other numbering scheme that isn't a simple autonumber. For instance, you might pull one autonumber value off a sequence some place to represent each users session, then combine that number with another autonumber value to create a unique record value in the temporary tables that would not need to be changed when added back to the main tables. -- Larry
