Unique Number Creation

[ramdil]

Hi All
I have got a lots of help from this forum and again i am in need of yours help.
I will briefly explain the scenario.I have a customer table.It was created 12 years ago and its Primary key is Text value..And users was entering the customercode in their own.But now they are asking me to autogenerate it.Since its datatype is text, i cant use autonumber as i cant change the datatype.
So what i am trying to do is to generate a unique number after the user types the customername.Th e code is as follows

Dim tmpCustNo As String
tmpCustNo = CStr(Int((10000 0 * Rnd)))
Cust_No.SetFocu s
If IsNull(DLookup( "[Number]", "[tab_Customers]", "[Number] = """ & [tmpCustNo] & """")) Then
Cust_No = tmpCustNo
Else
Cust_No = tmpCustNo + 1
End If

.Now when i just thought a instance, i understand this logic will not work,The scenario is as follows.As i generates the random number, same number can repeat again, so the above case fails in that instance.So is there any way to generate the unique number.Thanks in advance

[Scott Price]

Why don't you use a Make-Table query to retrieve the existing records into a new table that has a proper autonumber ID field?

Seems a bit simpler than trying to make sure you don't re-create an existing value.

Regards,
Scott