random number generation

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
 
M

Marshall Barton

Kenny 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?

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 think the problem has something to do with the Randomize
statement. You code is probably running faster than the
resolution of the Timer function so you are getting the same
seed value every time you call the GenPrimaryValue
procedure. That should be dealt with by using a static
variable to prevent using Randomize more than once:

Public Function GenPrimaryValue() As String
Static Initialized As Boolean
. . .
If Not Initialized Then
Randomize Timer()
Initialized = True
End If
. . .

Check the rest of your code more carefully. There are a
couple of places where you set lowerbound to a higher value
than lowerbound. Also, I can't fathom what the code to set
strDate is tryibg to accomplish, especially when it doesn't
include the hours and when the block of code could be done
more simply with a single line:
strDate = Format(CrntDate, "yyyymmddnnss")
or probably
strDate = Format(CrntDate, "yyyymmddhhnnss")

You also set intchar in some places and never use it before
setting it again.
 
R

Roger Carlson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top