Controlling the number of records displayed.

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

How can I control how many records will be returned without going to
the design mode of a query and manually changing the number? On a form
that I set parameters for query I want to have a textbox that tells
the query how many records to return. Sometimes I only want the top 50
and other times I want just the top 25. Right now I have to open the
query in design mode and set the return number to the number of record
that I want. In the properties sheet this is called top values.
I have Access 2007.
Thanks so much,
Anthony
 
You'd need to build the SQL statement via VBA code, and then use that SQL
statement as the form's RecordSource property. This allows you to put the
desired TOP xx value into the SQL statement.

Tell us more about the situation where you want to do this. Are you wanting
to do this for the form that is already open and on which you're entering
the parameters and "Top" value? Or is it for a form that will be opened
subsequent to entering those values on the first form?
 
You'd need to build the SQL statement via VBA code, and then use that SQL
statement as the form's RecordSource property. This allows you to put the
desired TOP xx value into the SQL statement.

Tell us more about the situation where you want to do this. Are you wanting
to do this for the form that is already open and on which you're entering
the parameters and "Top" value? Or is it for a form that will be opened
subsequent to entering those values on the first form?
Click to expand...

I have a form that is already open and I need a textbox in the form
that I can change the value of that will determine the number of
records that are returned. Right now the form consist of a subform and
command button. The command button opens the query. The whole database
is for making a exams. The sub form is a continuous form with chapter
numbers and a check box next to each number. I select the chapter
numbers that I want and then open the query. The questions table and
the table that is linked to the subform are both linked in the query.
This allows me to select the chaptes that I want and then using the
selected criteria row in the query, I can filter out all the questions
that aren't from the chapters that I want.
Thanks for your help!
It is much appreciated. I learn so much from all of you guys, and
doing projects is the best way to learn!

Anthony
 
I have a form that is already open and I need a textbox in the form
that I can change the value of that will determine the number of
records that are returned. Right now the form consist of a subform and
command button. The command button opens the query. The whole database
is for making a exams. The sub form is a continuous form with chapter
numbers and a check box next to each number. I select the chapter
numbers that I want and then open the query. The questions table and
the table that is linked to the subform are both linked in the query.
This allows me to select the chaptes that I want and then using the
selected criteria row in the query, I can filter out all the questions
that aren't from the chapters that I want.
Thanks for your help!
It is much appreciated. I learn so much from all of you guys, and
doing projects is the best way to learn!

Anthony
Click to expand...

here is the sql for the query right now.
how do I convert it to VB and put a variable in?

SELECT TOP 25 [question import].AutoNumber, [question import].Book,
[question import].Chapter, [question import].Verse, Verse.[Quote
Verse], [question import].Question, [question import].Answer, [make
chapters talbe auto?].selected
FROM [make chapters talbe auto?] INNER JOIN ([question import] INNER
JOIN Verse ON ([question import].Chapter=Verse.Chapter) AND ([question
import].Verse=Verse.[Verse number]) AND ([question
import].Book=Verse.Book)) ON ([make chapters talbe
auto?].Book=Verse.Book) AND ([make chapters talbe
auto?].Chapter=Verse.Chapter)
WHERE ((([make chapters talbe auto?].selected)=True))
ORDER BY Rnd([question import]![AutoNumber]);
 
Your post says you're opening the query? Does this mean that you're viewing
the query's results in Datasheet view of the query? Or are you opening a
form (or a report) that is bound to the query? The programming will depend
upon what you're actually doing.
--

Ken Snell
<MS ACCESS MVP>



Anthony said:
I have a form that is already open and I need a textbox in the form
that I can change the value of that will determine the number of
records that are returned. Right now the form consist of a subform and
command button. The command button opens the query. The whole database
is for making a exams. The sub form is a continuous form with chapter
numbers and a check box next to each number. I select the chapter
numbers that I want and then open the query. The questions table and
the table that is linked to the subform are both linked in the query.
This allows me to select the chaptes that I want and then using the
selected criteria row in the query, I can filter out all the questions
that aren't from the chapters that I want.
Thanks for your help!
It is much appreciated. I learn so much from all of you guys, and
doing projects is the best way to learn!

Anthony
Click to expand...

here is the sql for the query right now.
how do I convert it to VB and put a variable in?

SELECT TOP 25 [question import].AutoNumber, [question import].Book,
[question import].Chapter, [question import].Verse, Verse.[Quote
Verse], [question import].Question, [question import].Answer, [make
chapters talbe auto?].selected
FROM [make chapters talbe auto?] INNER JOIN ([question import] INNER
JOIN Verse ON ([question import].Chapter=Verse.Chapter) AND ([question
import].Verse=Verse.[Verse number]) AND ([question
import].Book=Verse.Book)) ON ([make chapters talbe
auto?].Book=Verse.Book) AND ([make chapters talbe
auto?].Chapter=Verse.Chapter)
WHERE ((([make chapters talbe auto?].selected)=True))
ORDER BY Rnd([question import]![AutoNumber]);
Click to expand...
 
Your post says you're opening the query? Does this mean that you're viewing
the query's results in Datasheet view of the query? Or are you opening a
form (or a report) that is bound to the query? The programming will depend
upon what you're actually doing.
--

Ken Snell
<MS ACCESS MVP>


here is the sql for the query right now.
how do I convert it to VB and put a variable in?
Click to expand...
SELECT TOP 25 [question import].AutoNumber, [question import].Book,
[question import].Chapter, [question import].Verse, Verse.[Quote
Verse], [question import].Question, [question import].Answer, [make
chapters talbe auto?].selected
FROM [make chapters talbe auto?] INNER JOIN ([question import] INNER
JOIN Verse ON ([question import].Chapter=Verse.Chapter) AND ([question
import].Verse=Verse.[Verse number]) AND ([question
import].Book=Verse.Book)) ON ([make chapters talbe
auto?].Book=Verse.Book) AND ([make chapters talbe
auto?].Chapter=Verse.Chapter)
WHERE ((([make chapters talbe auto?].selected)=True))
ORDER BY Rnd([question import]![AutoNumber]);
Click to expand...
Click to expand...

I figured it out. Thanks for the help.
This is what it looked like in the end.
This is under a "click" sub for a command button

Set dbs = CurrentDb
strSQL = "Select top " & c & " * FROM [make chapters talbe auto?]
INNER JOIN ([question import] INNER JOIN Verse ON ([question
import].Book = Verse.Book) AND ([question import].Verse = Verse.[Verse
number]) AND ([question import].Chapter = Verse.Chapter)) ON ([make
chapters talbe auto?].Chapter = Verse.Chapter) AND ([make chapters
talbe auto?].Book = Verse.Book)WHERE ((([make chapters talbe
auto?].selected) = True))ORDER BY Rnd([question import]!
[AutoNumber]);"
Set qdf = dbs.CreateQueryDef("Quiz2", strSQL)
 
Back
Top