select first x amount of records (1 Viewer)

[SDB]

Hello All

if I have thirty records and have the end user select the first 8 or what ever amount they want, could be 3, 14, 9 etc, is this possible via a form ?

thanks

 

[SDB]

Added an image so people understand what I'm trying to do. I just want to select the first X amount of records.

any one got any ideas its driving me mad...

Cheers

 

[raskew]

Hi -

Lookup the Top predicate in the help file.

The following may be overkill for your purposes, but you might want to test it out.

http://www.access-programmers.co.uk/forums/showthread.php?t=139748

HTH - Bob

 

[SDB]

I've just found that post and I think it will do what I need..just trying to get my head around how it works.

thanks Raskew

 

[Moniker]

All it is:

SELECT TOP X * FROM TableName

Replace X with the number of records you want.

 

[SDB]

sorry for being slow (Full of head cold) does this give the user the chance to put in what ever amount they want or is it hardcoded at that amount??

Thanks

 

[Moniker]

Form has a control called txtRecords. The user enters the number of records that they want into that textbox.

You execute this:

DoCmd.RunSQL "SELECT TOP " & txtRecords & " * FROM YourTableName"

 

[SDB]

Sorry Moniker,

geting more confushed by the minute, is your code seperate from Raskews code?

Will have another look in the morning

Thanks

 

[Moniker]

The code raskew pointed to was way overkill (as he mentioned). All you're trying to do is get record 1 to record X. The TOP command does just that. It will get record 1 to record X, and X is your variable.

SELECT TOP 10 * FROM TableName -- Gets first 10 records
SELECT TOP 14 * FROM TableName -- Gets first 14 records
SELECT TOP X * FROM TableName -- Gets first X records, as noted in previous post

 

[RuralGuy]

Form has a control called txtRecords. The user enters the number of records that they want into that textbox.

You execute this:

DoCmd.RunSQL "SELECT TOP " & txtRecords & " * FROM YourTableName"

IIRC the Top predicate will not take a variable in this manner. I believe it is necessary to put the entire string together first and then DoCmd.RunSQL sqlString.

 

[Moniker]

RG may be right on that. I didn't test it as a string is a string, but Access has its quirks. Test it both ways and let us know what happens.

If RG is right, then you take the txtRecords from the form and do as he suggested:

strSQL = "SELECT TOP " & txtRecords & " * FROM TableName"
DoCmd.RunSQL strSQL

 

[SDB]

have tried this but with no joy getting an error message syntax error in FROM Clause.

cheers

 

[RuralGuy]

Please post the code you have and indicate the line with the error.

 

[SDB]

here you go

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click
strSQL = "Select Top " & Weights & " *FROM Collecting Yorks Qry" (think its here)
DoCmd.RunSQL strSQL
Exit_Command2_Click:
Exit Sub
Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub