Primary key problem

  • Thread starter Thread starter mel
  • Start date Start date
M

mel

HI! I have this problem and I was wondering if there were
any suggestions for it. Basically, I have a primary key
field (unique identifier) in my database and I don't want
to set it to autonumber because some of the persons that
will be entered already have an existing ID that I can
type in as I enter their information in the database.
However, there are others who don't have an existing ID
and I want the system to generate that number for them. Is
there any way I can do this in access. So far the only
thing that I have thought of was creating a table that
contains like 2000 numbers where the user can open the
table and choose the number and then delete it. This does
not seem very practical though in the long run because the
numbers will eventually run out. Any suggestions are truly
appreciated. Thank you!!!!!
 
Why not just use a autonumber field for the relations?

The users never have to see, know about, or use, or look at , or even be
aware that you have a autonumber behind the scenes. In fact, they should
never see it.

You can have a field for the optional id as you wish...but that got nothing,
or very little to do with using a autonumber field for setting up relations
between tables. Your users will never worry, know, care or even be bothered
with the fact that you used a autonumber.

I mean, when word loads a document into memory...do you care about the
actual number of the memory segment used for the document load? (no, you
don't care..nor do your users). The idea applies to the primary key if it is
a autonumber. Your users SHOULD NEVER see this number.

Now, if you want to have some id field that is optional as you desire, then
that is just fine. You can even set the index to not allow duplicates also.
But, once again, that optional id should have NOTHING to do with the primary
key, nor should it be the primary key. Just use the automatic system BEHIND
the scenes..and make the id field optional.

You can certainly place a button on the form, or have some option that
generates the next highest number in the system. However, if those id
numbers are being assigned by another system, then you can't guess, or even
use the next highest number as that number might already be assigned.

How are these id numbers assigned now?

If you want the computer to take the next highest number in the computer,
then a button to "make new id" could be:

me!badgeId = dmax("badgeid","tblCustomers") + 1
me.refresh

So, simply take the highest number in the badgeID and add one to it.
However, this issue and problem is not one of a primary key.
 
Thank you for your response! I was specifically
instructed to develop the database with that ID as the
primary key where all of the relationships between the
tables are based on that ID. I did try to come up with a
text box that generated random numbers for the user to
find a number and enter that as the ID, however, I always
got the same sequence of numbers. I had the following in
the text box in an unbound form where the user can open
the form and look at the number:

Text22=int(rnd()*100000)

and this generated a five digit number. To avoid getting
the same sequence every time I opened the database I did
the following in the forms OnOpen event:

Dim Text22
Randomize
Text22=int(rnd()*100000)

This DID NOT do the trick, I still got the same sequence.
Could you tell me what I am doing wrong or if you have any
other suggestions.

Thanks :)
 
Thank you for your response! I am trying to generate a
five digit number as the ID, that's the only criteria. I
am instructed to use this specifically as the unique
identifier relating all of the tables. Any ideas are
highly appreciated.

Thank you :)
 
Use

Randomize Timer

to get a new seed for the random function each time.

I still think there are better ways than the constraints you are working
under.
--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Thanks for responding. I tried what you said and it still
did not work. Is there any way to fix this or another way
around it to generate unique random numbers?

Thank you!!!
 
Hmm, I just tried this on a test form, and it worked, generating a
different sequence each time I opened the form, and/or restarted Access.

Private Sub cmdComm_Click()

Randomize Timer

Me.txtMsg = Int(10000 * Rnd())



Exit_cmdComm_Click:
Exit Sub

Err_cmdComm_Click:
MsgBox Err.Description
Resume Exit_cmdComm_Click

End Sub

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Back
Top