Question random records

[gusgaff]

I have one table (500 Club) with two fields (ID) (Name)
i would like to randomly pick a record from the table and display
the results in two seperate text boxes on a form one for (ID) and one for (Name). I intend to use this for a monthly draw at my workplace.
Any help would be greatly appreciated.

 

[bob fitz]

First, change the name of the table to 500Club. Spaces and special characters should not be used.
Change the name of the field from Name to MemberName. Name is a reserved word.
Call one of the text boxes: txtMemberName
Call the other one: txtID
Use the following code in the buttons On Click event:

  Dim intID As Integer
  Randomize
  intID = Int((DMax("ID", "[500Club]") - DMin("ID", "[500Club]") + 1) * Rnd + DMin("ID", "[500Club]"))
  
  Me.txtID = DLookup("ID", "[500Club]", "ID=" & intID)
  Me.txtMemberName = DLookup("MemberName", "[500Club]", "ID=" & intID)

EDIT:
Should have said that the field called "ID" in the table needs to have sequential numbers.

 

[DavidAtWork]

Bob's code will work even if you have numbers missing and they are not sequential. The only adjustment you need to make is to do a check that the random number generated exists as an ID in the 500Club table, if it doesn't try another random number and you can operate this by putting the code into a Do While Loop like this

Dim intID As Integer
Dim numberOK as Boolean
 
Randomize
numberOK = False
Do While numberOK = False
    intID = Int((DMax("ID", "[500Club]") - DMin("ID", "[500Club]") + 1) * Rnd + DMin("ID", "[500Club]"))
    If DCount("ID", "[500Club]", "ID=" & intID) > 0 Then: numberOK = True
 
Loop  
Me.txtID = DLookup("ID", "[500Club]", "ID=" & intID)
Me.txtMemberName = DLookup("MemberName", "[500Club]", "ID=" & intID)

David

 

[gusgaff]

Bob's code will work even if you have numbers missing and they are not sequential. The only adjustment you need to make is to do a check that the random number generated exists as an ID in the 500Club table, if it doesn't try another random number and you can operate this by putting the code into a Do While Loop like this

Dim intID As Integer
Dim numberOK as Boolean
 
Randomize
numberOK = False
Do While numberOK = False
    intID = Int((DMax("ID", "[500Club]") - DMin("ID", "[500Club]") + 1) * Rnd + DMin("ID", "[500Club]"))
    If DCount("ID", "[500Club]", "ID=" & intID) > 0 Then: numberOK = True
 
Loop  
Me.txtID = DLookup("ID", "[500Club]", "ID=" & intID)
Me.txtMemberName = DLookup("MemberName", "[500Club]", "ID=" & intID)

David

Thank you both vey much everything is sound.
As i use auto number for ID i dont think there will be any problems.
Or will there?

One more question please, if i needed to have non repeating random numbers how would i do that?

 

[pr2-eugin]

David's Code would capture that requirement. That's the reason he added the While loop to Bob's code..

 

[DavidAtWork]

To prevent repeating random numbers (or winners), you'd need a table to store the valid random numbers that are generated and do a check similar to the loop I suggested above only the check would would be against the previous random numbers table.
I don't know how many in your pool but using this method you will in time reach a stage where every random number generated is invalid because it's been used before, so I suggest the table you use to store previous winners includes a date field and add date criteria when you do a check against previous winners, so you're effectively saying:
"If the number generated exists in the previous winners table where (todays date - previous date) < 366 then number is invalid, try again"
This would prevent someone winning more than once in any 365 days

David

 

[gusgaff]

Cheers Guys,
I think i'll stick with the original code from Bob as it works fine.
The only thing about non repeating numbers is, as people pay into the draw by direct debit i surely would be breaching Gaming and Lottery Laws (Scotland) as i would not be giving everyone a fair chance every month.

Thank you again for all your help it is vey much appreciated.

Gus.