Karen,
As an alternative, have two autonumbered tables. Use a form
based on the 'temporary' one to make your initial entry. If the user
likes how everything looks, use an insert command or append the data to
the 'master' or 'permanent' table. That way any incomplete or
unacceptable entries are discarded from the second or 'temporary'
table. Only valid entries are added to the 'permanent' table. And your
primary key numbers remain in perfect order.
Of course this will not work if users are adding records from
the R:>.
If you have check numbers or other numbers which need each
number to be accounted for, you must restrict deletion of those numbers
as well. You can void an entry, but not delete the row.
Randy Peterson
[EMAIL PROTECTED] wrote:
>
> Has anyone done this? A client has decided they don't like
> the missing autonumbers. I have code to make sure a unique
> number is assigned -- that's not a problem. But I was
> wondering if there's a way to do it with a trigger, either
> a before or an after trigger.
>
> The problem is that the column is a primary key. That means
> it cannot be null. I was thinking of an 'after' trigger
> where the row is added, and then afterwards the row is
> updated with the correct number. But it couldn't be added
> with a null in the column because the PK would be violated.
> I could let it assign an autonumber, then do an update with
> the 'correct' number but I would run the risk that the update
> would be to a number higher than the autonumber and somewhere
> down the line they wouldn't be able to add a new record
> with the same autonumber (sorry, do you get that?)
>
> I'm trying to avoid putting code everywhere that they might
> be adding a row into this table, plus I know they put rows
> in from the r> prompt.
>
>
> Karen
