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.
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.
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