Random Selection From a Table

[Jaspal]

Hi all,

Okay heres my question I have a table with two fields ID and number, the number field is a a list of numbers (obviously) which are used as reference numbers. This field is already populated with values.

My problem is that the user is to click a button (access front end) and a reference number is assigned. Basically, within the onClick event i need the reference number to be randomly picked from the table.

eg if my Number field has the following values:

4
5
15
16
47
98

then i want the onCLick event to select randomly one of these values, can this be done, if so what is the best way ?

Thanks in advance

 

[deBassMan]

Hi

What version of Access are you using?

please present the table design

cheers

 

[Jaspal]

Im using Access 2000, the table design is as follows:

ID (autonumber), RefNo (Number)

As stated befre the table is prepopulated with values, and just want some kind of random number picker!

Thanks

 

[gemma-the-husky]

just use rnd to get a decimal number, and multiply it by the max index or count of your file. then do a lookup to get the corresponding value for that that lookup vector.

you will get the same random sequence each time, so you may need to reseed the rng eg with now to get a different no each time. omly becomes a slight problem if you have gaps in your index field

 

[deBassMan]

Hi

try something like this:

Private Sub cmdGetRandomVal_Click()
Dim x As Integer

x = Rnd(1) * 5

Me.lblDisplayNo.Caption = DLookup("Refno", "tblRefs", "id=" & x)


End Sub


good luck matey

 

[deBassMan]

... forgot to say

the 5 = the number of records

or you could use DMAX