Taking ComboList Items & writing a qry to select records randomly 2

[fazm]

Help! I am on the verg taking a sledge hammer to me PC!

I have a form with a combolist on it with 3 items to choose from. Based any one of these items, I need to randomly select a match from another table and then display it either in a datasheet or msgbox.

I know SQL pretty good so I was trying to do it in a series of queries, but I am finding out that maybe this should be done in VBA with some flow control, which I little of. It could my table design that is causing the problemm too. Any help would be greatly appreciated.

If need be I can send the Access 2000 database since it is very small.

Thank you for your time.

Fazm

 

[Rolliee]

Send me your email and I will send you some code using random numbers.

rollie@bwsys.net

 

[fazm]

Its on the way Rolliee, Thanks!

 

[scriverb]

Here is a some VBA code that can be modified to do what you need:
SUBROUTINE
Public Sub RandomSelection(vTableName As String, vIndexFieldName As String, vFlagFieldName As String, vStartRecCounter As Long, vRecordsToFlag As Long)
Dim vRandomSelect As Long
Dim vTableCount As Long
Dim I As Long
Dim MyDB As Database
Dim MyRS As Recordset
Set MyDB = CurrentDb
vStartRecCounter = vStartRecCounter - 1
vTableCount = DCount("*", vTableName)
Set MyRS = MyDB.OpenRecordset(vTableName, dbOpenDynaset)
Randomize
For I = 1 To vRecordsToFlag Step 1
Random:
vRandomSelect = CLng(Int(Abs((vTableCount * Rnd) + 1))) + vStartRecCounter
MyRS.FindFirst vIndexFieldName & " = " & vRandomSelect
If Not (MyRS.NoMatch) Then
MyRS.Edit
If MyRS(vFlagFieldName) = False Then
MyRS(vFlagFieldName) = True
MyRS.Update
Else
GoTo Random
End If
Else
GoTo Random
End If
Next I
End Sub

A call to this subroutine with the parameters specified will randomly select records from a table and flag them for selection. It requires that you table have a boolean field for selection and an Autonumber field. The names of both are passed through the parameters.

The same process can be executed through the following VBA code which prompts for the parameters:
Dim vRandomSelect As Long
Dim vTableCount As Long
Dim vRecordsToFlag As Long
Dim vTableName As String
Dim vIndexFieldName As String
Dim vFlagFieldName As String
Dim vStartRecCounter As Long
Dim I As Long
Dim MyDB As Database
Dim MyRS As Recordset
Set MyDB = CurrentDb
vTableName = InputBox("Enter the name of the table to be random sampled.", , "tblCases&quot
vIndexFieldName = InputBox("Enter the name of the Autonumber field", , "RecCounter&quot
vFlagFieldName = InputBox("Enter the name of the Yes/No field to be flagged", , "Select&quot
vStartRecCounter = InputBox("Enter the beginning record counter to begin selecting&quot - 1
vTableCount = DCount("*", vTableName)
Set MyRS = MyDB.OpenRecordset(vTableName, dbOpenDynaset)
vRecordsToFlag = CLng(InputBox("How many records are to be randomly selected?&quot)
Randomize
For I = 1 To vRecordsToFlag Step 1
Random:
vRandomSelect = CLng(Int(Abs((vTableCount * Rnd) + 1))) + vStartRecCounter
MyRS.FindFirst vIndexFieldName & " = " & vRandomSelect
If Not (MyRS.NoMatch) Then
MyRS.Edit
If MyRS(vFlagFieldName) = False Then
MyRS(vFlagFieldName) = True
MyRS.Update
Else
GoTo Random
End If
Else
GoTo Random
End If
Next I

With both of these I would create a temp table with an autonumber, all the field from the original table, and a boolean select field. Just the structure is all that is needed. Then create a query to append the selected records from your table into this table. Then either make the subroutine call or run the above code.

Post back if you have any further questions about these routines.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[fazm]

Thanks scriverb! I will give it a try.

Fazm