Semi-Random Numbers

[drichter12]

I have a need to set up a field to generate a unique random number within a pre-defined range and wonder if this is a simple task or not. Here is the situation:

We have to generate a unique random "access code" which falls between 10000 and 99999 for each new record created. These are access codes for long distance calling and are assigned to employees as they are added. The number would remain with the employee until the employee is terminated or their code is revoked but the number cannot be re-used for accounting purposes.

Any ideas or suggestions would be appreciated.

Thanks,
Dale

Dale

 

[PHV]

Why should the "access code" be random ?
You couldn't use an AutoNumber with 10000 as starting value ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SgtJarrow]

Here's what you need to accomplish this....

A table named something like tblUsedCodes. It has one field in it called AccessCode, set to numeric, single type and this field is the primary key for the table.

Then the below code is used to create the "unique" codes...

Public Function GetRandomAccessCode() As Single

    Dim rs As ADODB.Recordset
    Dim sngCode As Single
    Dim blnCodeOK As Boolean
    
    Set rs = New ADODB.Recordset
    
    With rs
        blnCodeOK = False
        .Open "tblUsedCodes", CurrentProject.Connection, adOpenKeyset, adLockPessimistic, adCmdTable ' Opens table
        Do
            If Not .BOF Then .MoveFirst ' Moves to first record for search
            Randomize
            sngCode = Int((9 * Rnd) + 1) * 10000 ' Generates the first digit as int can only get 32000ish
            Randomize
            sngCode = sngCode + Int((9999 * Rnd) + 1) ' Generates the rest of the number
            .Find "[AccessCode] = '" & sngCode & "'" ' Searches for the number
            If .EOF Then ' If the number is not found in the table
                blnCodeOK = True ' Tells the system this number is ok
                .AddNew
                .Fields("AccessCode") = sngCode ' Add the number to the table so it is not used again
                .Update
            End If
        Loop Until blnCodeOK = True ' Create a new number if the current one was found
        .Close ' Close the table
    End With
    
    GetRandomAccessCode = sngCode ' Return the number

End Function

This could, and really should, be modified NOT to use this "table" of Used Codes. It should really be looking at the existing table you have where the employee's code is going to be stored. This is to prevent any "errors" as well as helps in data integrity, bloating, and other reasons. I presented this to you this way for ease and hopefully you can figure out how to adapt it to yuor needs. If not, please let us know.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer