Selecting a Row in access (1 Viewer)

[Jaspal]

Hi,

The problem that i have is that i have a random number generator, between 1 and 5.

now with the random number returned i want to selct froma table that row number!

Now the table concerned does have an indexed autonumber field, but i dont want to match this in a query to return that value. Im looking to return the row number!

Is this possible, if so please help!

Thanks in Advance.

 

[Uncle Gizmo]

But you already have the row number? The random number is the row number you are trying to look up? Or have I missed something?

Cheers Tony

 

[Jaspal]

sorry dont think i explained it properly. The random number that is generated, i want that row number from the table to be returned.

E.g. Lets say the random number generates the number 4, i want to then select from the the table all the values that are in rown number 4!

Hope this makes sense

 

[Uncle Gizmo]

so why don't you want to use a query?

 

[Jaspal]

well if a query will do it then ill try that, so i may have posted in the wrong thread.....Any ideas ???

 

[Uncle Gizmo]

I don't know your level of expertise, so I would suggest you first experiment with a query, providing it with a "Test" random number, a known value you enter yourself. Once you have got it working then replace the "Test" with a function that returns your Random number.

Your function should look something like this .....
pseudo code:

fRandNumb() as Integer

'generate random number here give it a name: intRandNo
'then pass it to this function :

fRandNumb = intRandNo

End Function

You can test this function by providing your form with a command button, when the command button is pressed it should display a message box: place the following code in your command button:

btnTest .....

MsgBox " Rand No >>> " & fRandNumb

End Sub

 

[FireStrike]

hmmm maybe I can help. I know there is a function to do this, but as I always forget the unction name, and have had problems with it pulling the proper row anyway, I use my own code to do similar. let's say that intRndNum is your random number. You can use code like the following to get the data.

dim rcd as recordset
dim strSql as string
dim I as integer

strSql="select * from table" 'where table is your table name
set rcd = currentdb.openrecordset(strSql)
rcd.movefirst
for I = 1 to intRndNum
    rcd.movenext
next I

This will at least get you to the proper record. From here you can do whatever you want with the data.

 

[Jaspal]

Thanks for that i understand that but wht im unsure about is how in the query i can get it to return the relevant row from giving it the row number, that is what the syntax ofr it is!

Bearing in mind as per my previous example, if 4 is generated and i want row number 4, the id for that row may not be 4

 

[Jaspal]

Thanks for the FireStrike.........Works a treat!