random numbers for an indexed field with No duplicates (1 Viewer)

sonny123

Registered User.
Local time
Today, 06:56
Joined
Apr 8, 2011
Messages
31
every time I create a new record it gets a sequential reference number Like this:
Code:
Me![CaseNumber] = Nz(DMax("[TableCaseNumber]", "[Main]"), 0) + 1
(BTW this is not the primary Key)

Whilst i imagine this is fine in most circumstances, the situation i'm in sequential numbers are causing confusion particularly when a service user has multiple cases open that opened at the same time.

What Id like to do is experiment with is giving a random number to each case when it opens so there is greater distinction between multiple cases opened at the same time sort of like this:
Code:
Me![CaseNumber] = (RandomNumber between 00000 And 99999 That Doesnt already exist in ("[TableCaseNumber]", "[Main]")

is this possible, is there such a function ??

being an access noob I enjoy working out solutions myself what i'm after is pointing in the right direction.
Pointers hints links & suggestions please.
 

John Big Booty

AWF VIP
Local time
Today, 17:56
Joined
Aug 29, 2005
Messages
8,263
Have a look at the Rnd() Function.

The following code should generate non repeating Random numbers between one and one thousand;
Code:
    Dim intRandomNum As Integer
        
    Randomize
    
    If Me.RandomNum = 0 Then
        intRandomNum = Int((1000 - 1 + 1) * Rnd + 1)   [COLOR="Green"] 'Generate random number[/COLOR]
    
    
        If DCount("RandomNum", "TBL_Records", "RandomNum = " & intRandomNum) = 0 Then     [COLOR="Green"]'Check if random number already exists and assign if it dosn't[/COLOR]
            Me.RandomNum = intRandomNum
            Exit Sub
        End If
    
        While DCount("RandomNum", "TBL_Records", "RandomNum = " & intRandomNum) > 0     [COLOR="Green"]'Regenerate and keep trying until new random number is reached[/COLOR]
            intRandomNum = Int((1000 - 1 + 1) * Rnd + 1)
            If DCount("RandomNum", "TBL_Records", "RandomNum = " & intRandomNum) = 0 Then
                Me.RandomNum = intRandomNum
                Exit Sub
            End If
        Wend
    End If

You will want to set your range to be fairly large, and also think about what will happen once your range has been used up, and add some code to account for that eventuality.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 17:56
Joined
Jan 20, 2009
Messages
12,849
Not the best idea really. Better to maintian a table with one record which is the next number to be allowated. When the number is required open a recordset with other uses locked out while it is read and incremented.

The LockEdit argument for the DAO OpenRecordset is dbDenyRead + dbDenyWrite

You need to include a retry loop so that other users will get several bites at getting a number until it is available to them.
 

sonny123

Registered User.
Local time
Today, 06:56
Joined
Apr 8, 2011
Messages
31
Cheers very much Ill have a play with that and let you know how I get on
 

sonny123

Registered User.
Local time
Today, 06:56
Joined
Apr 8, 2011
Messages
31
When the number is required open a recordset with other uses locked out while it is read and incremented.

The LockEdit argument for the DAO OpenRecordset is dbDenyRead + dbDenyWrite

You need to include a retry loop so that other users will get several bites at getting a number until it is available to them.

I didn't really understand what Galaxiom was on about as I was so keen to get the random number code working. Which I did after a bit of fiddling:D

So I can now use either option incremental or random

In any event It has now occured to me what Galaxiom might be on about.

When the button is pressed to generate the number. I need to prevent other users from also trying to get a number until the number has been assigned to the first user.. and i would need to this whether I use an Incremental or a random number (at least I think that's what he's on about)

Google, here I come
 

Users who are viewing this thread

Top Bottom