Help with Random function code

[teluser]

I'm trying to figure out some code to select random records out of a temp table that is created from code. Below is my attempt to create a table using the fields that I need, then I create a new field to populate the random number in the table. Then I want to take 50% of the total table and append to another database so those records can be analyzed. Can anyone be of assistance. The code below works until I get to the Do...Loop part. I'm not familiar with using the Do...Loop and feel I am missing something considering it wont stop running when I execute the code. Then I still have to get the 50% part added to it as well. Any assistance would be greatly appreciated. BTW I'm using Access 2000 on an NT machine if that helps any..

Sub RandNum()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strTableName As String

strSQL = "SELECT table1.*, table2.NAME INTO tblTemp FROM table2 INNER JOIN table1 ON table2.NAME=table1.id WHERE (((table2.NAME)=[Forms]![Form1]![CmbX].[value]));"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Set db = CurrentDb()
Set tdf = db.TableDefs("tblTemp&quot
With tdf
.Fields.Append .CreateField("RandNumb", dbLong)
End With
Set rst = db.OpenRecordset("tblTemp&quot
Do
rst.MoveFirst
rst.Edit
Randomize
rst![RandNumb] = Rnd()
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing


End Sub

 

[psemianonymous]

Put the "rst.MoveFirst" BEFORE the "Do" line.

That should do it. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[mikevh]

The reason your loop won't stop running is that you
have "rst.MoveFirst" INSIDE the Do...Until. Each time
your loop iterates, you're moving back to the first
record, so rst.EOF never becomes true. Move "rst.MoveFirst"
BEFORE the "Do" at the beginning of the loop.

But, I think you could get a random 50% of your records without all that VB. Try

SELECT TOP 50 PERCENT FieldList
FROM YourTable
ORDER BY RND(SomeNumericField);

"SomeNumericField" can actually just be numeric-LOOKING.
(I do it with phone numbers, which are actually text.)
This should give you what you're after.

 

[teluser]

Thanks foolio12 and mikevh that got my code to stop looping endlessly. mikevh I'm not sure what you are suggesting will work for what I have or either I just don't understand how to go about it. Here is my scenario, let's say there are thousands of customers. Each customer has several customer ID's (4 digit alphanumeric). They are submitting orders and getting assigned order numbers (8 digit alphanumeric) and a version number (2 digit Numeric). If there is a change to an existing order number then that order number's version is incrementally advanced by 1. Then each individual customer ID, order number, version number could have several different error numbers associated to them. The random records I need are for a certain customer ID, order number, version number, (first three fields being all unique)and error number (which are all the same), randomly give me 50% of those to review. Again thanks for the quick responses.

 

[mikevh]

What I'm suggesting is that you can get a random 50% of
the records that meet your selection criteria without
writing so much code. Try modifying strSQL like so:

strSQL = "SELECT TOP 50 PERCENT table1.*, table2.NAME
INTO tblTemp
FROM table2
INNER JOIN table1 ON table2.NAME=table1.id
WHERE (((table2.NAME)=[Forms]![Form1]![CmbX].[value]))
ORDER BY RND("SomeNumericField""

then say

DoCmd.RunSQL(strSQL)
or
db.Execute(strSQL)

and you should have the records you want in tblTemp.
Of course, do whatever feels right to you. Just a suggestion.