21 thoughts on “Random Numbers Repeating”

1. Thinking in terms of formal database theory, yes, there’s obviously a better way: create these records in a temporary but autokeyed database table rather than in memory. Moral: use built-in features rather than reinvent inferior substitutes.

   Alternatively, if these temporary unique IDs aren’t passed to the final database, then why not create new records with sequential ID numbers? It’s not that difficult to make the last used or next available sequential number persistent.

   Using random numbers as unique keys is bad idea, one that’s pretty much guaranteed to drop your grade a full letter in database courses. Now you’ve seen why, but your response is make the random number larger. Make it as large as you want, but you’re still playing Russian Roulette.

2. Hmm… random numbers repeating… that is a nasty problem :D
   i am not sure this would help, but instead of assigning a random number if the record is not in accessdb, you can try this,

   If clsRoyaltyLine.RoyaltyLineID = 0 Then
           clsRoyaltyLine.RoyaltyLineID = ACCESS_MAX + ROW_NUM
           ROW_NUM = ROW_NUM+1
   End If

   both access_max and rownum are global numbers that are initiated to 100000 (or some really large number) and 1 before you start the loading process.

3. Hi Dick –

   Maybe the approach from the RND help–

   “To produce random integers in a given range, use this formula:

   Int((upperbound – lowerbound + 1) * Rnd + lowerbound)

   Here, upperbound is the highest number in the range, and lowerbound is the lowest number in the range.”

   …Michael

4. How about using the system date and time as a “random” number? If you could include “hundredths” of seconds and then maybe add a totally random number, it should be virtually impossible to end up with the same number twice.

5. Public Sub Add(clsRoyaltyLine As CRoyaltyLine)
   Randomize ‘do not use inside a loop

   If clsRoyaltyLine.RoyaltyLineID = 0 Then
   clsRoyaltyLine.RoyaltyLineID = Int(Rnd * 100000)
   End If

   On Error Resume Next ‘duct tape it
   Do
   mcolRoyLines.Add clsRoyaltyLine, CStr(clsRoyaltyLine.RoyaltyLineID)
   If Err.Number = 0 Then
   Exit Do
   Else
   clsRoyaltyLine.RoyaltyLineID = Int(Rnd * 100000)
   Err.Clear
   DoEvents ‘so the escape key will work
   End If
   Loop
   On Error GoTo 0

   If Not mobjParent Is Nothing Then
   Set clsRoyaltyLine.Parent = Me.Parent
   End If

   End Sub
   ‘––

   Something similar could also be done in an error handler.

6. How about trapping the error on the .add and just creating another ID?

7. Oops – Jim beat me to it.

8. Hi Dick,

   I’m no expert and certainly not in the same league as you and some of the other guys that post here…but have you considered creating an array of say 100 integer values, each of which can be selected randomly when needed and then removed from the array so that the value is no longer available for selection thus avoiding any duplication? For example:

   Sub RandomNum()
           Dim n As Integer, i As Integer
           Dim temp As Integer
           Dim iValues(1 To 100) As Integer
           Dim iDraw(1 To 7) As Integer
           ‘Creates an array of 100 values (1 to 100)’
          For i = 1 To 100
               iValues(i) = i
           Next
           ‘Selects 7 unique random numbers between 1 and 100
         i = 0
          For i = 1 To 7
               n = Int(Rnd * (100 – i) + 1)
               iDraw(i) = iValues(n)
               temp = iValues(n)
               iValues(n) = iValues(101 – i)
               iValues(101 – i) = temp
           Next i
   End Sub

   Here we select 7 unique values from a possible 100 values. I wrote this originally in VB and have adapted it for VBA here. Hope this helps.

   Dan Randles

9. I’ve used a timestamp function in sequel to do this, but NOW() function and the “time” VBA function don’t seem to carry out the milliseconds far enough to create unique values.

10. I count, which here would be

    If clsRoyaltyLine.RoyaltyLineID = 0 Then
    clsRoyaltyLine.RoyaltyLineID = mcolRoyLines.Count + 1

11. To generate unique numbers you do *not* need random numbers. Just generate sequential numbers. Or use today’s date + sequential numbers. Or, use Now. Or Timer.

12. Here is a possible alternative: rather than unique numbers, use Guids. They are very convenient to use ( myClass.Id = Guid.NewGuid), and have pretty strong guarantees of being unique.

13. To keep it really simple, why not start at -1 and count backwards(-2, -3,…). Because this negative sequence will never clash with the(positive) autonumbers

14. Replace Access with SQL Server Express, Int ID with GUID ID, although that is probably not want you want to hear :)

15. I won’t be calling the database for this number because it would produce too many calls and performance would suffer. I agree that I should be using built-in stuff whenever possible, but the cost is too high in this case. It seems like sequential is the way to go. Not sure why I didn’t think of that.

16. One problem with sequential: Some of the class members are already in the database and will have a autonumber assigned by Access. If I use a Count to go sequential, it could clash with one of those. So I think I’ll go with

    clsRoyaltyLine.RoyaltyLineID = mcolRoyLines.Count + 1 * -1

    The Count suggestion and the Negative suggestion combined should make it rock solid. Right?

17. Ok, I’ll take the bait – grin

    clsRoyaltyLine.RoyaltyLineID = mcolRoyLines.Count + 1 * -1
    Should be…
    clsRoyaltyLine.RoyaltyLineID = (mcolRoyLines.Count + 1) * -1

    -OR-

    This might be more efficient…

    Private Declare Function timeGetTime Lib “winmm.dll”() As Long
    ‘(Returns the number of milliseconds that have elasped since Windows started.)
    clsRoyaltyLine.RoyaltyLineID = timeGetTime

18. You’re building records outside Access, and you need unique ID fields in your collection outside Access. The records in this collection would eventually be fed to Access. And you’re feeding the temporary ID fields used in your collection in addition to the information fields to the Access table.

    Why?

    Access supports SQL INSERT statements for a subset of the fields in a table, and as long as you don’t include the temporary ID field from your collection in the INSERT operation into your Access table, Access will generate new unique, NONCLASHING primary keys or autonumber fields for the new records. You may need to dump your collection into a temporary table then insert that table into your main access table, but you’d never have to worry about key clashes again.

19. DK,

    (mcolRoyLines.Count + 1) * -1 would essentially give you what Dermot suggested, which is sequential negative numbers. This should be rock solid.

    Since I’m lazy, I would simply prefixed the manually generated autonumbering with text; As in Admin438?

20. Of course, my suggestion would require the field that holds your unique identifier to be text field.

    My granpa always told me (in a scruffy granpa voice), “Good database design prescribes the use of number fields only for values that are to be used in calculations. Stay away from numeric identifiers because you will never add them.”

    Crazy grandpa….

21. fzz: I don’t send these temporary identifiers into Access. I do use INSERT with every field except RoyaltyLineID which Access dutifully fills in. Here’s the scenario:

    Invoice has one or many InvoiceLines
    InvoiceLines as one or many RoyaltyLines

    When the user selects and invoice, the Invoice, InvoiceLines, RoyaltyLines and other classes are filled from Access. Then the user can add, delete, or edit the Royaltylines. If they add a RoyaltyLine, I don’t immediately write that to the database, only when they ‘Save’. But at this point, my RoyaltyLines collection contains both Access records and non-Access records. That means there’s the potential for a clash between Access’ autonumber and my temporary one.

Posting code? Use <pre> tags for VBA and <code> tags for inline.