Help.. Selecting "n" number of records

[Toya2176]

I was wondering if it was possible to select any given number of records without changing Top Value in the Queries properties. I want to be prompted : "Please enter # of records to return". I don't need any particular order because I have a mod already set up to randomly pick what ever amount of numbers are picked.

All help is welcome
Thanks in advance

 

[mikevh]

I think you'd have to get your number for TOP n
from a textbox on a form and then build your SQL
string in code.

After you've got your number in the textbox (in the
AfterUpDate event probably)

strSQL = "SELECT TOP " & _
Me!YourTextBoxName & " ... rest of your SQL statement;"

 

[Toya2176]

Mikevh

Ok, I'm not sure if I'm doing this right. I created a form and added my text box. Then in the after update I entered your code as an expression, and its giving me errors. Can you take it step by step if you have the time.

Toya

 

[mikevh]

Can you paste the code you have in the AfterUpDate
procedure? I'll take a look.

 

[Toya2176]

Here you go...

"SELECT TOP" & _
Me!NoTechCheck & "Heading.HeadingID, Heading.[File:], Heading.Location, Heading.Building, Heading.[File Clerk], Random.TOID, Random.[Technical Order No], Random.QTY, Random.TmpRandNbr, Random.[Audited Date];"

 

[mikevh]

You need a space after TOP and a space before first
occurence of Heading; these are to separate the value
in Me!NoTechCheck from the rest of the string.
Also, you need the name of the table in your SQL.
I'm assuming this is in the AfterUpDate procedure for
NoTechCheck. See below.

Dim strSQL as String

strQSL = "SELECT TOP " & _
Me!NoTechCheck & " Heading.HeadingID, Heading.[File:], Heading.Location, Heading.Building, Heading.[File Clerk], Random.TOID, Random.[Technical Order No], Random.QTY, Random.TmpRandNbr, Random.[Audited Date] FROM YOURTABLENAME;"

DoCmd RunSQL(strSQL);