random number generation

Discussion in 'Access VBA Modules' started by Kenny G, Jun 1, 2010.

  1. Kenny G

    Kenny G Guest

    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




    --
    Kenny G
     
    Kenny G, Jun 1, 2010
    #1
    1. Advertisements

  2. Kenny G wrote:

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

    --
    Marsh
    MVP [MS Access]
     
    Marshall Barton, Jun 1, 2010
    #2
    1. Advertisements

  3. 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" <> wrote in message
    news:...
    > 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
    >
    >
    >
    >
    > --
    > Kenny G
     
    Roger Carlson, Jun 1, 2010
    #3
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.