sorry - just one quick qn

  • Thread starter Thread starter Andy Levy
  • Start date Start date
A

Andy Levy

My form has two IDs, a main ID and a secondary ID

The main ID is an autonumber - so i can be sure that the number will always
be unique. Even if the last entered record is deleted - its ID number will
not be repeated ever.

However - I need to make the secondary ID unique as well - that is never
repeated. I can set its type to Integer, and set it to No Duplicates , but
if i delete a particular record in the database i could make a new record
and assign it the old ID accidentally.

My only idea was to create a table called usedSecondaryID and have it check
against that table each time i assign a secondary ID value. If the value has
been used before - then through up a msgbox. What do you think ?

Thanks again

Andy
 
(comments inline)

Andy Levy said:
My form has two IDs, a main ID and a secondary ID

The main ID is an autonumber - so i can be sure that the number will
always be unique. Even if the last entered record is deleted - its ID
number will not be repeated ever.
Click to expand...

I don't know if I'd want to rely on the Jet database engine never to
reuse an autonumber if there's no current record with that number. If
nothing else, you can create an append query that force a new record to
be created using an ID value that was previously assigned, so long as
there's no record now with that number. If you really must ensure that
no ID is ever reused, I think you'd better never delete records -- just
mark them as deleted or inactive instead.
However - I need to make the secondary ID unique as well - that is
never repeated. I can set its type to Integer, and set it to No
Duplicates , but if i delete a particular record in the database i
could make a new record and assign it the old ID accidentally.
Click to expand...

I don't understand about the secondary ID. Your table will have two
separate, generated, unique indexes? You're not talking about a
secondary ID that is only unique within the primary ID, are you?
My only idea was to create a table called usedSecondaryID and have it
check against that table each time i assign a secondary ID value. If
the value has been used before - then through up a msgbox. What do
you think ?
Click to expand...

Is the secondary ID supposed to be, effectively, an autonumber; that
is, generated by the application? If so, you can use a one-record
"NextNumber" table to hold the next available value, and the process of
creating a new record in your "main" table can open a recordset on the
NextNumber table (locking it), get the next-number value and assign it
to the secondary ID field, then increment the next-number value and
store it back in the NextNumber table, closing the recordset and
releasing the table.
 
Back
Top