6 digit random number, no duplicates.

[orange]

@Fostertrident,

Did you review Vlad's suggestion in post #6? Thoughts?

[Fostertrident]

Thanks for this, I didn't realise you could generate random autonumbers either. Thanks for your advice and I hope the lunch was still warm.

[Fostertrident]

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.

Thanks for this I will have a look and study this tonight.