primary key fields

[p]

Hi! I was hoping I could find some suggestions for a
problem that I was having. I have a unique ID field in my
database but I want to be able to leave it blank for a
while and then enter a value into it later. Basically I
have a list of people in my database that are authorized
to do a specific task and therefore they already have an
existing id number. Then I have a list of people that do
not have an ID number and they are not going to receive
one right away until they have completed other
requirements, but I want to be able to put their personal
information into the database. So the only way to
distinguish the active people from the none active ones is
by whether they have ID numbers or not. This did not work
in Access because you cannot leave a primary key field
empty. The ID field that I created is a text field. Is
there anyway that I can have Access fill in the nonactive
ID's with a temporary number that can be changed later?
Any other suggestions on what I can do with this would be
great!!! Thanks!

 

[Steve Schapel]

P,

Assigning a temporary ID number yourself to the non-active people
would seem to be the simplest answer. You could prefix the temporary
ID with a certain number or letter, for example X001, X002 etc. When
these people become complete their requirements and become active, you
just change the ID number to their new permanent ID. If there are
related tables, define a Relationship enforcing Referential Integrity
with Cascade Updates enabled, to take care of the possibility of the
ID being changed. In the meantime, you can always distinguish active
from non-active based on [ID] Not Like "X*"

- Steve Schapel, Microsoft Access MVP

 

[GPO]

You are mixing your concepts.

The primary key serves a specific purpose, namely to
uniquely identify a record in a table. If you want to make
it serve other purposes as well you could (and indeed
have) run into problems.

I'd be inclined to allocate EVERYBODY a Unique ID in the
database, but flag those individuals who have not met the
requirements you describe with a separate yes/no field
instead. If the flag is Yes, they are authorised to do the
task. If No, they are not authorised. That way you even
have the flexibility of "de-authorising" someone who has
already been given an ID.

GPO

 

[p]

Thank you for your responses! The idea of generating the
temporary ID sounds good to me. My question now is could
I make a table where access generates several temporary
ID's and then just pick numbers from that table to put
into the ID field, then each time a number is picked I
can delete it from my table. It would help to have a
source where those numbers are chosen because I may have
a large number of people in my database and there may be
more than one person entering information into it.
Thanks!

-----Original Message-----
P,

Assigning a temporary ID number yourself to the non- active people
would seem to be the simplest answer. You could prefix the temporary
ID with a certain number or letter, for example X001, X002 etc. When
these people become complete their requirements and become active, you
just change the ID number to their new permanent ID. If there are
related tables, define a Relationship enforcing Referential Integrity
with Cascade Updates enabled, to take care of the possibility of the
ID being changed. In the meantime, you can always distinguish active
from non-active based on [ID] Not Like "X*"

- Steve Schapel, Microsoft Access MVP

Hi! I was hoping I could find some suggestions for a
problem that I was having. I have a unique ID field in my
database but I want to be able to leave it blank for a
while and then enter a value into it later. Basically I
have a list of people in my database that are authorized
to do a specific task and therefore they already have an
existing id number. Then I have a list of people that do
not have an ID number and they are not going to receive
one right away until they have completed other
requirements, but I want to be able to put their personal
information into the database. So the only way to
distinguish the active people from the none active ones is
by whether they have ID numbers or not. This did not work
in Access because you cannot leave a primary key field
empty. The ID field that I created is a text field. Is
there anyway that I can have Access fill in the nonactive
ID's with a temporary number that can be changed later?
Any other suggestions on what I can do with this would be
great!!! Thanks!

.

 

[Steve Schapel]

P,

One way to do this, different from your suggestion, but I imagine
serve the purpose... On the Before Insert event of the form where the
inactive people are getting entered, or on the Click of a command
button, or some other appropriate event, code something like this
(assuming the example I mentioned before, with the letter prefix)

Me.ID = "X" & DMax("Mid([ID],2)","YourTableName") + 1
.... or, depending on circumstances, you may need this...
Me.ID = "X" & DMax("Mid([ID],2)","YourTableName","[ID] Like 'X*'") + 1

- Steve Schapel, Microsoft Access MVP

Thank you for your responses! The idea of generating the
temporary ID sounds good to me. My question now is could
I make a table where access generates several temporary
ID's and then just pick numbers from that table to put
into the ID field, then each time a number is picked I
can delete it from my table. It would help to have a
source where those numbers are chosen because I may have
a large number of people in my database and there may be
more than one person entering information into it.
Thanks!

-----Original Message-----
P,

Assigning a temporary ID number yourself to the non- active people
would seem to be the simplest answer. You could prefix the temporary
ID with a certain number or letter, for example X001, X002 etc. When
these people become complete their requirements and become active, you
just change the ID number to their new permanent ID. If there are
related tables, define a Relationship enforcing Referential Integrity
with Cascade Updates enabled, to take care of the possibility of the
ID being changed. In the meantime, you can always distinguish active
from non-active based on [ID] Not Like "X*"

- Steve Schapel, Microsoft Access MVP

Hi! I was hoping I could find some suggestions for a
problem that I was having. I have a unique ID field in my
database but I want to be able to leave it blank for a
while and then enter a value into it later. Basically I
have a list of people in my database that are authorized
to do a specific task and therefore they already have an
existing id number. Then I have a list of people that do
not have an ID number and they are not going to receive
one right away until they have completed other
requirements, but I want to be able to put their personal
information into the database. So the only way to
distinguish the active people from the none active ones is
by whether they have ID numbers or not. This did not work
in Access because you cannot leave a primary key field
empty. The ID field that I created is a text field. Is
there anyway that I can have Access fill in the nonactive
ID's with a temporary number that can be changed later?
Any other suggestions on what I can do with this would be
great!!! Thanks!

.