6 digit random number, no duplicates.

[Fostertrident]

Hi

I have a database which has imported IDnumbers from an old system, these ID numbers are 6 digits and were created randomly. I'm afraid this system of numbering has to remain, therefore I have about 300 records with these randomly assigned numbers that contain no duplicates. Therefore I can't do autonumber. I need to create a form, that allows me to add new records, giving a 6 digit number that hasn't yet been used.


Does anyone have any ideas?

Many thanks

[Micron]

You can start autonumber field at any value AFAIK, so that still won't help? That is, your highest current value is 456789, you could start at 456800?
Regardless of the solution, what happens when you reach 7 digits?

[Fostertrident]

I'm afraid the range of the codes that have already been generated are in a range where going up won't give me much room to manoeuver, but 999,699 records will be more than enough for a long long time. I realise that the solution I need is only needed due to poor planning, but this is the situation I'm in.

I was thinking that I need to first generate a random number between 100000 and 999,999, then dlookup whether that exists in my table, if it does it generates another until the dlookup tells me it isn't in the table, then I need to add that generated number to a field on my form. But I'm afraid I'm a bit at a loss to do any of these parts

[Micron]

Rather than repeat this test thousands of times, what about generating these numbers once, append each to a new table, then just use those values? Each time you use one, you delete from the table. The more you use, the faster it gets - not that you'd really notice the difference I suppose.

[Fostertrident]

I'm not sure if I get this solution. Sorry.

I also definitely need to learn how to loop something until I get the desired result as that is a huge gap in my Access learning.

[Gicu]

Why do you say that you have to keep the existing system? Whatever method you will implement it will involve some sort of checking the existing numbers so not that effective.

The best would be to use an autonumber field and update your existing data. To preserve any primary-foreign key relationship (which would be the only reason you would want to keep the existing numbers) you simply copy the main table (the one where the existing IDs are the PK) and keep the new copy empty, add a new field to it (name it OldID or siimilar) and change the ID field to autonumber. Now run an append query to populate it with the exisitng data making sure you append the existing IDs to the OldID field, leaving the autonumber PK to self -populate. Finaly create update queries joining the new main table to the ones with foreign-keys on the OldID-FK and update the FKs with the new auto-number PK.

Cheers,
Vlad

[Micron]

I'm saying if you do it as required, each time you need a number, you have to perform this test against every table value. The longer the list gets, the slower it goes. If you generate the numbers in one operation and append them to a table, I realize the test has to be performed the same number of times to get the maximum number of values, but you'd do it once. If that takes 2 minutes, it's done. Then each time you need a number, you pull one from this new table and delete it when the process is complete. With this generator, if you ever decide you need a new batch of numbers (e.g. in a different range) you'd have a tool that you can re-use.
Back to my lunch that's getting cold.

[Micron]

I saw nothing that suggests the existing numbers were a PK?
BTW, I believe you can set a table field to be autonumber, random and unique. Regardless, I think a better understanding of the tables and their relationships would help.

[kd2017]

... I was thinking that I need to first generate a random number between 100000 and 999,999, then dlookup whether that exists in my table, if it does it generates another until the dlookup tells me it isn't in the table, then I need to add that generated number to a field on my form. But I'm afraid I'm a bit at a loss to do any of these parts

...I also definitely need to learn how to loop something until I get the desired result as that is a huge gap in my Access learning.

Here is an example of how you might implement your idea. BUT this is LAST resort, the larger your table grows the slower this will get. You should really listen to these guys, they've been there done that.

I like micron's idea to meet your current requirements better than this code I've written.

Public Function GetRandomIdNumber() As Variant
On Error GoTo ErrHandler_GetRandomIdNumber
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qry As String
    Dim IdNumber As Long
    Dim rslt As Variant
    Dim counter As Long
    Dim min_num As Long
    Dim max_num As Long
    
    rslt = Null
    min_num = 100000
    max_num = 999999
    
    Set db = CurrentDb
    
    For counter = min_num To max_num
        IdNumber = Int((max_num - min_num + 1) * Rnd + min_num) 'generate random number between min and max
        
        qry = "SELECT IdNumber FROM [Table Name Here] WHERE IdNumber = " & IdNumber
        Set rs = db.OpenRecordset(qry, dbOpenSnapshot)
        If rs.BOF And rs.EOF Then
            'query didn't find an existing record so let's use this one
            rslt = IdNumber
            Exit For
        End If
    Next counter
    
    rs.Close

ExitHandler_GetRandomIdNumber:
    Set db = Nothing
    Set rs = Nothing
    
    GetRandomIdNumber = rslt
    Exit Function
    
ErrHandler_GetRandomIdNumber:
    MsgBox Err.Description, , "GetRandomIdNumber() Error #" & Err.Number
    Resume ExitHandler_GetRandomIdNumber
End Function

[Minty]

Definitely with Micron on this one.
Use an autonumber and set it to random.

As long as the existing references really are numbers and not text (e.g 000254 ) Then I can't see what the issue is.
You will get a random number as a unique reference. If you need to pad it out to at least 6 digits you can set a display format to include the leading zero's.

[kd2017]

The only problem with that is OP specified rand between 100,000 and 999,999 but random autonumber will spit out between -2,147,483,648 and 2,147,483,647

[Minty]

But did it have to be that in the new system or is that just "nice" to have, because the bosses lucky number is 6 and we MUST have 6 numbers because we always had 6 numbers ??

[Micron]

Could be as simple as

Function GetRandom() As Long
Dim lngNumber As Long

lngNumber = Int((999999 - 100000 + 1) * Rnd + 1)
If DCount("yourField", "yourTable", "yourField = " & lngNumber) = 0 Then
   GetRandom = lngNumber
End If

End Function

but only if you want to run this as a single use number generator. To populate a table, it needs a loop and a sql execution.

[accesstos]

Even if, the choice of the separate table of random IDs, sounds like the best option, I think that it complicates the procedures. Doesn't hurts so much the creation of some new records or just one when you need it in the main table using code.

So, my suggestion is the code below:

Option Compare Database
Option Explicit

Const lngcMaxID As Long = 999999
Const lngcMinID As Long = 100000

Function RandomNum(lngMax As Long, Optional lngMin As Long) As Long
    'Returns a random number between lngMin and lngMax
    Randomize
    RandomNum = Int(Rnd() * (lngMax - lngMin + 1)) + lngMin
End Function

Function SaveNewRecord() As Long
    'Creates a new record in Table1 with a random six-digit ID
    Dim lngID As Long
    Dim db As DAO.Database

    Set db = CurrentDb
    Do
        lngID = RandomNum(lngcMaxID, lngcMinID)
        db.Execute "INSERT INTO Table1 (ID) VALUES (" & lngID & ")"
    Loop Until db.RecordsAffected
    Set db = Nothing
    SaveNewRecord = lngID
End Function

Sub AddRecords(Optional ByVal intCount As Integer = 100)
    Dim i As Integer

    For i = 1 To intCount
        SaveNewRecord
    Next i
End Sub

There is an implementation of suggested code in attachment.

[moke123]

I have about 300 records with these randomly assigned numbers that contain no duplicates. Therefore I can't do autonumber.

Thats not a lot of records. I would be inclined to try and correct the poorly planned design while the record count is low.
What is the significance of the random number?