Random query question

[cruisy]

Hi there,
What is the best way around generating a query to generate the contents of a table listed by id in random order?

Using the Int((n * Rnd) + 1) example will not be accurate if an autonumber id row has been previously deleted. As the number generated may point to a autonumid that doesn't exist and hence the row can't be referenced.

What I thought had to be done was an initial count of all record rows in the table and then a new query needs to generate a new dynamic autonumber id (on the fly as a seperate column) that can be used as a reference point for the row (does that make sense)?

Or am I totally missing something? and there is a far easy way to do all this.

Secondly (if someone wants to be nice)... what is the best way that I can then access that row via a form? (ie via recordset, what method/action etc)

Thanks heaps

 

[cruisy]

I have sort of worked it out now....

What I did was create a seperate query that orders the record set by: ORDER BY Rnd(primarykey), and selects only the 1st record: SELECT TOP 1.

Then in the form module I did this:

Dim intRndNum As Integer
Dim rsRndRecord As Object
Set rsRndRecord = CurrentDb.OpenRecordset("qryRandom")

rsRndRecord.FindFirst "1 = 1"
DoCmd.GoToRecord acDataForm, "frmDetail", acGoTo, rsRndRecord.Fields(0)
rsRndRecord.Close


Thus I am always able to guarantee a record selected and it navigates to the random record.


Only flaw now is that it would be nice to write in some logic that would not reselect the current record randomly ie if rec#ID 4 is selected then that ID is omitted from the query for the next random generation and then put back in the pool after.... so I am never able to select the same record twice in a row....

If anyone knows a simple way to do this?

 

[CJBIRKIN]

Hi

For the last bit of the why not use the current record rec#ID from the form as a criteria in the query

<> form!formname!id (not sure of syntax here)


So the query can only select a record that isn't shown in the form.

Hope that makes sense.


Chris