Random Select Help 1

[Gootz11]

Hello,
I currently have a query that randomly selects 100 records from a table.
My SQL statement looks like this:
SELECT TOP 100 *
FROM [X1A Pick Slots]
ORDER BY Rnd(asc([slot]));

I'm trying to change my statement so that i can enter the number of records i want to select every time i run the query.
Please Help
Thanks in Advance
JG

 

[GoDawgs]

You could do this through code (create an SQL string with the number you want tied to a text box or something)...I don't think you can have a parameter in that portion of the statement otherwise. Hope that helps.

Kevin

 

[scriverb]

Create a public function with this code:
Function RandomPrompt()
RandomPrompt = InputBox("Enter # to select: ", "Random Select Prompt&quot
End Function

Now use this VBA code when you are calling the query to to run:

Dim db As DAO.Database
Set db = CurrentDb
db.QueryDefs("qryYourQueryName&quot.SQL = "SELECT TOP " & _ RandomPrompt() & _
" * FROM [X1A Pick Slots] " & _
"ORDER BY Rnd(asc([slot]));"
DoCmd.OpenQuery "qryYourQueryName"
db.CLOSE

Post back if you have any questions.



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

 

[Gootz11]

Scriverb,
Thanks for your reply! I'm not that familiar with VB but i been trying to run the code above but can't really figure out how to do it. do i have to make 2 different modules, or just 1? again.. i apologize for my ignorance but any help would be truly appreciated.
thanks

 

[scriverb]

Copy the Function statement code and paste it into a new database module.

Copy the other code and put it behind a button on a form in the buttons OnClick event procedure. Save the form and then open it and click the button. You will be prompted for the number of records that you want to select at random from the table. Enter the number and the query will execute with the number of records selected.

The code modifies the actualy .SQL property of the query itself. Each time you run the Top XXX number of records portion changes in the code.

Post back with any questions concerning this code.

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

 

[Gootz11]

I tried putting the code in the form, when i click on the button i get a error meassage that tells me "compile error, user defined type not defined" and then it highlights "Private sub command0_Click ()" in yellow and it also highlights "db As DAO.Database" in blue. the other error i am getting is "invalid charachter" for the _
any ideas
thanks for all of your help, i really appreciated, i think your getting me on the right track.

 

[scriverb]

This DAO code and you are probably using Access 2K which doesn't automatically have the library reference made. Open a form to design and click the Code button. Once in some VBA code select from the menu bar the Tools menu and then select References. Now a popup window will appear with a list of selected and non-select library references. You need to select the MS DAO 3.6 Object Library. After selecting it click the OK button and retry the code. This should resolve these issues.

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

 

[Gootz11]

I started to run the code like this and its working perfectly

Private Sub Command36_Click()
Set db = CurrentDb
db.QueryDefs("x1a - random pick slots&quot.SQL = "SELECT TOP " & RandomPrompt() & _
" * FROM [X1A Pick Slots] " & _
"ORDER BY Rnd(asc([slot]));"
DoCmd.OpenQuery "x1a - random pick slots"
DoCmd.Close
db.Close
End Sub

I am going to have any problems in the future if i keep this format? all i did was delete the error i was getting... thanks for all of your help, you have no idea how much i appreciated!

 

[scriverb]

If you have no errors this code should continue to work for you without any problems.

I was glad to help you with this problem.

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

 

[scriverb]

Gootz11: I am giving you a star for your posting of this question. The query SQL that you provided is excellent in being able to run a query and select randomly from the recordset. I have written VBA code to do this in a much more complicated manner but to do this solely in a query is something others should see.

Excellent SQL code.

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