# How to create auto generated random numbers (1 Viewer)

#### soap

##### Registered User.
Any macro or vba codes to generate random numbers for students pin?

#### theDBguy

##### I’m here to help
Staff member
Hi,

Have you tried using the Rnd() function?

#### The_Doc_Man

##### Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
In general, you need to do two things.

First, assure that you use the RANDOMIZE function before you generate your random number. If you don't, the randomness isn't quite so random and in fact becomes predictable.

Second, understand what RND() does. You can look it up with your favorite browser by asking for "MS ACCESS RND() FUNCTION" and you'll get all the hits you ever need.

When using RND, you are asking for a random number generated by a method that results in you getting back a SINGLE number (i.e. floating-point or real as opposed to an integer). To get a 4-digit PIN, you might need this kind of expression:

Code:
``NextPIN = FIX( RND() * 10000 )``
This would give you a 4-digit PIN, but that means that you could have 0000 and 9999 as two possible results. There are many ways to handle this case but it would depend on what you wanted to EXCLUDE. If you don't mind having 0000 as a PIN, then it is as simple as I showed you. If you wanted something else, please explain.

#### soap

##### Registered User.
In general, you need to do two things.

First, assure that you use the RANDOMIZE function before you generate your random number. If you don't, the randomness isn't quite so random and in fact becomes predictable.

Second, understand what RND() does. You can look it up with your favorite browser by asking for "MS ACCESS RND() FUNCTION" and you'll get all the hits you ever need.

When using RND, you are asking for a random number generated by a method that results in you getting back a SINGLE number (i.e. floating-point or real as opposed to an integer). To get a 4-digit PIN, you might need this kind of expression:

Code:
``NextPIN = FIX( RND() * 10000 )``
This would give you a 4-digit PIN, but that means that you could have 0000 and 9999 as two possible results. There are many ways to handle this case but it would depend on what you wanted to EXCLUDE. If you don't mind having 0000 as a PIN, then it is as simple as I showed you. If you wanted something else, please explain.
Please how do i make it alphnumeric?

#### MickJav

##### It wasn't me
I use a table to hold the last ID used like code below this is a text version but it wouldn't be hard to use numbers

Put call in the default of a forms text box as below
GenCodeID(1)
the table holds a number of records hence the # in the call

Function GenCodeID(IntNum As Integer) As String
Dim IntProdNum As Long
Dim strProdLet As String
Dim intRandom As Integer
Dim rst As Recordset
Dim StrSQL As String
Dim recCode As Recordset
Dim Db As Database
Dim strSet As String

StrSQL = "SELECT * FROM StblSystemID WHERE ID =" & IntNum

Set Db = CurrentDb()
Set recCode = Db.OpenRecordset(StrSQL, dbOpenDynaset)

strProdLet = recCode("Letters")
IntProdNum = recCode("LastNumber")
Randomize
intRandom = Int((99 * Rnd) + 1) 'this uses 99 but you could use 1000000
GenCodeID = strProdLet & IntProdNum & "-" & intRandom

recCode.Edit
recCode("LastNumber") = IntProdNum + 1
recCode.Update
recCode.Close
Db.Close
End Function

Last edited: