Random Number Shuffle Generated (1 Viewer)

[Robecca]

Our summer bowling leagues does brackets. We’ve been determining the bowler order for the bracket “manually” by drawing a random playing card. I would like to have Access assign the order randomly instead. A bracket has 8 bowlers. We have 10 brackets a night.

I’m very new to coding VBA but I’ve searched and arrived at this code. While it will give me a random number between 1 & 8 for each record, it will assign a number multiple times for new records.

Here is my code:

Private Sub BracketNo_Exit(Cancel As Integer)

' Generate random value between 1 and 8.
Dim RndSlot As Integer
Randomize
RndSlot = Int((8 - 1 + 1) * Rnd + 1)
If Me.BracketNo = 1 And Me.Week = Date Then
Me.BracketSlot = RndSlot
End If
End Sub

This produces:

Bowler Week Bracket BracketSlot
Robecca 4/13/2017 1 5
David 4/13/2017 1 7
Steve 4/13/2017 1 2
Nancy 4/13/2017 1 5
Toni 4/13/2017 1 8
Don 4/13/2017 1 7
Lola 4/13/2017 1 3
Bob 4/13/2017 1 5

5 & 7 are used multiple times, but 1, 4 & 6 aren't used. I'm sure it is because it's doesn't take in account the previous records entered.

What I need is more like shuffling 8 numbers into a random order and then in the new random order each number fills in the to the BracketSlot field as the bowler is entered.

I am stumped even at how to search.

 

[plog]

You seem like you know how to code, so I won't do this for you, just give guidance.

Since you are working on only single digit numbers, you can store the numbers you have used in a string. Declare a string variable and set it to an empty string

Dim UsedNumbers = ""

Then when you generate a random number you use Instr() to see if that number is in UsedNumbers. If so, choose another number, if not use that number and add it to the string.

To expand this to all numbers, and really the correct way to achieve this, is with an array.
Same general method--check the array to see if a number exists in it, if so choose another number, if not use that number and add it to the array.

 

[Cronk]

The string approach of storing selected numbers is an interesting approach. Maybe faster than checking through an array each time. Although for most situations, the time difference would be insignificant.

For a selection pool with more than 9 numbers, a separator would be used between the multi digit number selections.

 

[Robecca]

Thank you Plog for a direction to explore. I've never done strings or arrays, so I will need to do some research. I may get back to this with questions or I will get back with a proper thanks for the solution!

 

[Robecca]

Here is the code I've come up with from my searching into strings and InStr(). The result is null. Any tips would be appreciated.

Private Sub BracketNo_Exit(Cancel As Integer)
' Generate random value between 1 and 8.

'String to store used position slot numbers
Dim UsedSlot As Integer
'Random position slot number
Dim RndSlot As Integer
'Variable to hold physical location of RndSlot in the UsedSlot string
Dim LocSlot As Integer

'Create RndSlot position number
Randomize
RndSlot = Int((8 - 1 + 1) * Rnd + 1)

'Determine if the RndSlot is in the UsedSlot string
LocSlot = InStr(UsedSlot, RndSlot)

If LocSlot > 0 Then
RndSlot = Int((8 - 1 + 1) * Rnd + 1)
Else

'Assign RndSlot to BracketSlot control in form
If Me.BracketNo = 1 And Me.Week = Date Then
Me.BracketSlot = RndSlot
End If
End If

'Add RndSlot position number to UsedSlot string
UsedSlot = UsedSlot + RndSlot

End Sub

I feel I am way off base.

 

[Cronk]

Firstly, Plog's suggestion was to store the selected numbers in a string, not an integer.
Dim UsedSlot as string
and append the selected numbers
UsedSlot= UsedSlot & RndSlot

The test for whether a digit has already been selected should be in a loop because you need to repeat the generation until you get a digit not already selected

Do
RndSlot = Int(8 * Rnd + 1) '--There is no need to subtract/add 1
LocSlot = InStr(UsedSlot, RndSlot)
until LocSlot >0

This will generate the first random number. You want 8 so wrap the code above in a loop that repeats 8 times.

When the outer loop completes, UsedSlot will contain something like
74318265

 

[Robecca]

I apologize for the week that has gone by. I have been working overtime and/or researching Do Loops. I do appreciate the help in figuring this out. I am still having problems getting the result I am wanting.

The code is looping 8 times before assigning the value to Me.BracketSlot for the 1st entry into the form. Then the next entry into the form starts the loop all over again. Therefore, I am still getting duplicate numbers in my bracket.

I did change until LocSlot >0 to =0 because nothing happened, it was like the database froze and I had to close out of the application.

I know I am doing something wrong…

Here is my current code:

Private Sub BracketNo_AfterUpdate()
' Generate random value between 1 and 8.

'String to store used position slot numbers
Dim UsedSlot As String
'Random position slot number
Dim RndSlot As Integer
'Variable to hold physical location of RndSlot in the UsedSlot string
Dim LocSlot As Integer

'Loop until Length of UsedSlot is 8
Do
'Loop until LocSlot is unique
Do
Randomize
RndSlot = Int((8 - 1 + 1) * Rnd + 1)
'Determine if the RndSlot is in the UsedSlot string
LocSlot = InStr(UsedSlot, RndSlot)
Loop Until LocSlot = 0
If Me.BracketNo = 1 And Me.Week = Date Then
'Assign RndSlot to BracketSlot control in form
Me.BracketSlot = RndSlot
'Add RndSlot position number to UsedSlot string
UsedSlot = UsedSlot & RndSlot
End If
Loop Until Len(UsedSlot) = 8
End Sub

It's probably easy but I don't know what I am doing. Thanks!

 

[plog]

Try and use code tags when you paste code into a post (use the Go Advanced button).

I generated some psuedo-code to show what your code should look like. Psuedo code, like the name implies is not actual code. It's like an outline--you will have to put in actual code to do the things I prescribed in it:

Sub FunctionName()
    ' assigns 8 unique random numbers  to string variable

    dim str_Slots = ""			' will hold unique random numbers
    dim tmp_SlotValue			' will hold random number to see if can use as slot value

    For i=1 to i=8
    ' loops 8 times, once for each slot

    	Do 
    		tmp_SlotValue = get_RandomNum()
		Loop While InStr(str_Slots, tmp_SlotValue)<1 
    ' finds random value that hasn't been used yet

    	str_Slots &= tmp_SlotValue
    ' adds new value to ones used

    ' here is where you would put  code to use the unique random value in tmp_SlotValue

    Next    
End Sub

 

[static]

Looked like fun so I had a try.

Sub examples()
    Dim s
    For Each s In Split(GetRndNums(1, 8), " ")
        Debug.Print s
    Next
    
    Debug.Print
    
    Debug.Print GetRndNums(1, 8)
    Debug.Print GetRndNums(-1, -8)
    
    Debug.Print GetRndNums(10, 20)
End Sub

Private Function GetRndNums(RngFrom As Integer, RngTo As Integer) As String
    Dim ary() As Boolean
    Dim i As Integer, j As Integer
    Dim lf1 As Integer, lt1 As Integer, ls1 As Integer
    Dim lf2 As Integer, lt2 As Integer, ls2 As Integer
    
    If RngFrom > RngTo Then
        i = RngFrom
        RngFrom = RngTo
        RngTo = i
    End If

    ReDim ary(RngFrom To RngTo, RngFrom To RngTo)
    Randomize
    For i = RngFrom To RngTo
        j = ((RngTo - RngFrom) * Rnd()) + RngFrom
        ary(i, j) = True
    Next
    
    If CInt(Int((1 * Rnd()))) Then
        lf1 = RngFrom: lt1 = RngTo: ls1 = 1
    Else
        lf1 = RngTo: lt1 = RngFrom: ls1 = -1
    End If
    For j = lf1 To lt1 Step ls1
        If CInt(Int((1 * Rnd()))) Then
            lf2 = RngFrom: lt2 = RngTo: ls2 = 1
        Else
            lf2 = RngTo: lt2 = RngFrom: ls2 = -1
        End If
        For i = lf2 To lt2 Step ls2
            If ary(i, j) Then
                If Len(GetRndNums) Then GetRndNums = GetRndNums & " "
                GetRndNums = GetRndNums & i
            End If
        Next
    Next
End Function

GetRndNums returns a string of values for the given range that you can then Split()

 

[Robecca]

Still not working. The program freezes. Won't move from BracketNo to BracketSlot. I have a feeling this is simple but I haven't used any of this code before and I don't know what to do.

Here is the code now.

Private Sub BracketNo_AfterUpdate()
' Assign 8 unique random numbers to string variable

Dim UsedSlots As String 'String to hold unique random numbers - example is str_Slots
Dim RndSlot As Integer 'Holds random number to see if it can be used as the BracketSlot value - example is tmp_SlotValue
Dim i As Integer

For i = 1 To 8 Step 1
'Loop 8 times, once for each slot

Do
Randomize
RndSlot = Int((8 - 1 + 1) * Rnd + 1)
Loop While InStr(UsedSlots, RndSlot) < 1
' finds random value that hasn't been used yet

UsedSlots = UsedSlots & RndSlot
' adds new vlaue to ones used


If Me.BracketNo = 1 And Me.Week = Date Then
Me.BracketSlot = RndSlot
End If
Next i

End Sub

 

[Cronk]

Have you stepped through your code?

If you look at your loop

Do
......
Loop While InStr(UsedSlots, RndSlot) < 1

the first time through the loop, UsedSlots will be "" and RndSlot will contain a number between 1 and 8, let's say it is 3

Instr("", 3) will equal zero, which is less than one. So your code will loop forever.

 

[Robecca]

Ah, I had Loop Until, not sure why I changed to Loop While. I switched back to Loop Until and the cursor did move to the BracketSlot control.

I put in a msgbox to see UsedSlots after assigning the RndSlot to BracketSlot and it loops 8 times before moving to the next row in the form. I've attached 2 jpg files to show what I mean.

RndSlot-1 is 1st and RndSlot-8 is after I have clicked "OK" 8 times on the msgbox.

Thanks! Robecca