random number generation

  • Thread starter Thread starter Kenny G
  • Start date Start date
K

Kenny G

Using Access 2007

Below is the code I am using in a module and I have a query that calls the
module and appends the random number (string) to the table. My situation is
that when a number is generated all records get the same number. How can I
get each record to have a different DIDID (de-identified ID). What is wrong
with the code that it does not generate a new number for each record?

Thank you in advance for your assistance.

Public Function GenPrimaryValue() As String

Dim strStart As String
Dim strDate As String
Dim strEnd As String
Dim CrntDate As Date
Dim intChar As Integer
Dim upperbound As Long
Dim lowerbound As Long
Dim X As Long

Randomize (Timer())
intChar = RandomChoice(89, 65)
CrntDate = Now
upperbound = 65
lowerbound = 89
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)

strStart = Chr(intChar)

strDate = DatePart("YYYY", CrntDate)
strDate = strDate & Format(DatePart("m", CrntDate), "00")
strDate = strDate & Format(DatePart("d", CrntDate), "00")
strDate = strDate & Format(DatePart("n", CrntDate), "00")
strDate = strDate & Format(DatePart("s", CrntDate), "00")

strEnd = ""

For X = 1 To 5

Select Case RandomChoice(4, 0)
Case 1
intChar = RandomChoice(89, 65)

Case Else
intChar = RandomChoice(48, 51)

End Select

strEnd = strEnd & Chr(intChar)

upperbound = 0
lowerbound = 4
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)
Select Case intChar
Case 1
upperbound = 65
lowerbound = 89
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)
Case Else
upperbound = 51
lowerbound = 48
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)
End Select
strEnd = strEnd & Chr(intChar)
Next X

GenPrimaryValue = strStart & strDate & strEnd
PRONUM = GenPrimaryValue
End Function

Public Function RandomChoice(lowerbound As Integer, upperbound As Integer)

RandomChoice = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)

End Function

Public Function StoreValues()

Const intNumberOfRecords As Integer = 10
Dim rstRand As DAO.Recordset '[MyTable] table
Const strRandT As String = "tblPtListing" 'table name
Dim intRec As Integer 'Counts records added

Set rstRand = CurrentDb.OpenRecordset(strRandT, dbOpenTable)

'Add random MyID to the table
With rstRand
For intRec = 1 To intNumberOfRecords
.AddNew
!DIDID = GenPrimaryValue
.Update
Next intRec
.Close
End With 'rstRand

Set rstRand = Nothing



End Function
 
I'm not exactly sure what you're trying to do here, but I have several
samples that might be helpful:

DataScramble.mdb
http://www.rogersaccesslibrary.com/forum/topic357.html

CharacterScramble.mdb
http://www.rogersaccesslibrary.com/forum/topic386.html

RandomRecords.mdb
http://www.rogersaccesslibrary.com/forum/topic293.html


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Kenny G said:
Using Access 2007

Below is the code I am using in a module and I have a query that calls the
module and appends the random number (string) to the table. My situation
is
that when a number is generated all records get the same number. How can
I
get each record to have a different DIDID (de-identified ID). What is
wrong
with the code that it does not generate a new number for each record?

Thank you in advance for your assistance.

Public Function GenPrimaryValue() As String

Dim strStart As String
Dim strDate As String
Dim strEnd As String
Dim CrntDate As Date
Dim intChar As Integer
Dim upperbound As Long
Dim lowerbound As Long
Dim X As Long

Randomize (Timer())
intChar = RandomChoice(89, 65)
CrntDate = Now
upperbound = 65
lowerbound = 89
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)

strStart = Chr(intChar)

strDate = DatePart("YYYY", CrntDate)
strDate = strDate & Format(DatePart("m", CrntDate), "00")
strDate = strDate & Format(DatePart("d", CrntDate), "00")
strDate = strDate & Format(DatePart("n", CrntDate), "00")
strDate = strDate & Format(DatePart("s", CrntDate), "00")

strEnd = ""

For X = 1 To 5

Select Case RandomChoice(4, 0)
Case 1
intChar = RandomChoice(89, 65)

Case Else
intChar = RandomChoice(48, 51)

End Select

strEnd = strEnd & Chr(intChar)

upperbound = 0
lowerbound = 4
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)
Select Case intChar
Case 1
upperbound = 65
lowerbound = 89
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)
Case Else
upperbound = 51
lowerbound = 48
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)
End Select
strEnd = strEnd & Chr(intChar)
Next X

GenPrimaryValue = strStart & strDate & strEnd
PRONUM = GenPrimaryValue
End Function

Public Function RandomChoice(lowerbound As Integer, upperbound As Integer)

RandomChoice = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)

End Function

Public Function StoreValues()

Const intNumberOfRecords As Integer = 10
Dim rstRand As DAO.Recordset '[MyTable] table
Const strRandT As String = "tblPtListing" 'table name
Dim intRec As Integer 'Counts records added

Set rstRand = CurrentDb.OpenRecordset(strRandT, dbOpenTable)

'Add random MyID to the table
With rstRand
For intRec = 1 To intNumberOfRecords
.AddNew
!DIDID = GenPrimaryValue
.Update
Next intRec
.Close
End With 'rstRand

Set rstRand = Nothing



End Function
Click to expand...
 
Back
Top