Using a variable 'Top n' value from a form control

efandango · Jan 3, 2008

Is it possible to have a form control that can adjust the 'Top n' value that
is based on a criteria in a query?

I have a query that I want to use with of a varying number of Top n values
based on the dynamic figure that I input into a control on a form. Is this
possible?.

I seem to remember from the early days of Access 1.0 & 2.0 that the 'Top n'
value can be used in a criteria on the QBE grid, or did I imagine that?

Allen Browne · Jan 3, 2008

No. You cannot use a parameter for the TOP n value.

You could build the query statement dynamically, and assign it to the SQL
property of the QueryDef.

efandango · Jan 3, 2008

Allen,

thanks for replying with a definitive answer; building the statement
dynamically sounds complex and beyond my abiliites. The other point is that
this query is part of a group of in-development interlinked queries, which
means I like to see the QBE grid as i go along.

regards

Allen Browne · Jan 3, 2008

It's not that difficult, and it doesn't really matter how complex the SQL
statement is. You just break it into 2 after TOP, and piece it back together
with the number in between.

This kind of thing:

Dim strSql As String
If Me.txtTop >= 1 Then
strSql = "SELECT TOP " & Me.txtTop
Else
strSql = "SELECT "
End If
strSql = strSql & " Table1.* FROM Table1;"
CurrentDb.QueryDefs("Query1").SQL = strSql

efandango · Jan 3, 2008

Allen,

How would that work with my SQL?, would this code go in the afterupdate
property of my 'input number' control? which you currently have ref as
'txtTop'. My query is called: 'Test Points generator'. its underlying Table
is called 'Test_Points_Feeder'

Dim strSql As String
If Me.txtTop >= 1 Then
strSql = "SELECT TOP " & Me.txtTop
Else
strSql = "SELECT "
End If
strSql = strSql & " Test_Points_Feeder.* FROM Test_Points_Feeder;"
CurrentDb.QueryDefs("Test Points generator").SQL = strSql

My Current SQL (Test Points generator):

SELECT TOP 5 Test_Points_Feeder.Run_No, Test_Points_Feeder.Point_Quiz_ID,
Test_Points_Feeder.Run_point_Venue_A, Test_Points_Feeder.Run_point_Address_A,
Date() AS TestDate
FROM Test_Points_Feeder
GROUP BY Test_Points_Feeder.Run_No, Test_Points_Feeder.Point_Quiz_ID,
Test_Points_Feeder.Run_point_Venue_A, Test_Points_Feeder.Run_point_Address_A,
Date(), Rnd([Point_Quiz_ID])
HAVING (((Test_Points_Feeder.Run_No) Between
[Forms]![frm_Runs].[RunFromCombo] And [Forms]![frm_Runs].[RunToCombo]))
ORDER BY Rnd([Point_Quiz_ID]);

Allen Browne · Jan 4, 2008

Everything after TOP 5 goes into the line:
strSql = strSql & ...

txtTop is assumed to be the name of the text box. Sustitute your own text
box named (in square brackets if the name contains a space.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

efandango said:
Allen,

How would that work with my SQL?, would this code go in the afterupdate
property of my 'input number' control? which you currently have ref as
'txtTop'. My query is called: 'Test Points generator'. its underlying
Table
is called 'Test_Points_Feeder'

Dim strSql As String
If Me.txtTop >= 1 Then
strSql = "SELECT TOP " & Me.txtTop
Else
strSql = "SELECT "
End If
strSql = strSql & " Test_Points_Feeder.* FROM Test_Points_Feeder;"
CurrentDb.QueryDefs("Test Points generator").SQL = strSql

My Current SQL (Test Points generator):

SELECT TOP 5 Test_Points_Feeder.Run_No, Test_Points_Feeder.Point_Quiz_ID,
Test_Points_Feeder.Run_point_Venue_A,
Test_Points_Feeder.Run_point_Address_A,
Date() AS TestDate
FROM Test_Points_Feeder
GROUP BY Test_Points_Feeder.Run_No, Test_Points_Feeder.Point_Quiz_ID,
Test_Points_Feeder.Run_point_Venue_A,
Test_Points_Feeder.Run_point_Address_A,
Date(), Rnd([Point_Quiz_ID])
HAVING (((Test_Points_Feeder.Run_No) Between
[Forms]![frm_Runs].[RunFromCombo] And [Forms]![frm_Runs].[RunToCombo]))
ORDER BY Rnd([Point_Quiz_ID]);

Allen Browne said:

It's not that difficult, and it doesn't really matter how complex the SQL
statement is. You just break it into 2 after TOP, and piece it back
together
with the number in between.

This kind of thing:

Dim strSql As String
If Me.txtTop >= 1 Then
strSql = "SELECT TOP " & Me.txtTop
Else
strSql = "SELECT "
End If
strSql = strSql & " Table1.* FROM Table1;"
CurrentDb.QueryDefs("Query1").SQL = strSql

Click to expand...

efandango · Jan 4, 2008

Allen,

I pasted this together, but I get some red error lines in the following
code. I have marked them (R)

Private Sub txtTop_AfterUpdate()
Dim strSql As String
If Me.txtTop >= 1 Then
strSql = "SELECT TOP " & Me.txtTop
Else
strSql = "SELECT "
End If
strSql = strSql & "SELECT Test_Points_Feeder.Run_No,
Test_Points_Feeder.Point_Quiz_ID, Test_Points_Feeder.Run_point_Venue_A,"

(R) Test_Points_Feeder.Run_point_Address_A, Date() As TestDate FROM
Test_Points_Feeder
GROUP BY Test_Points_Feeder.Run_No, Test_Points_Feeder.Point_Quiz_ID,
Test_Points_Feeder.Run_point_Venue_A,(R)

Test_Points_Feeder.Run_point_Address_A , Date, Rnd([Point_Quiz_ID])

(R) HAVING (((Test_Points_Feeder.Run_No) Between
[Forms]![frm_Runs].[RunFromCombo] And [Forms]![frm_Runs].[RunToCombo]))
ORDER BY Rnd([Point_Quiz_ID]);(R)

I don't know how to resolve these red Error lines; this the 1st time I have
used SQL in a VBA Window

Should I just concatenate the whole thing, or somehow format it with "

Allen Browne said:
Everything after TOP 5 goes into the line:
strSql = strSql & ...

txtTop is assumed to be the name of the text box. Sustitute your own text
box named (in square brackets if the name contains a space.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

efandango said:

Allen,

How would that work with my SQL?, would this code go in the afterupdate
property of my 'input number' control? which you currently have ref as
'txtTop'. My query is called: 'Test Points generator'. its underlying
Table
is called 'Test_Points_Feeder'

Dim strSql As String
If Me.txtTop >= 1 Then
strSql = "SELECT TOP " & Me.txtTop
Else
strSql = "SELECT "
End If
strSql = strSql & " Test_Points_Feeder.* FROM Test_Points_Feeder;"
CurrentDb.QueryDefs("Test Points generator").SQL = strSql

My Current SQL (Test Points generator):

SELECT TOP 5 Test_Points_Feeder.Run_No, Test_Points_Feeder.Point_Quiz_ID,
Test_Points_Feeder.Run_point_Venue_A,
Test_Points_Feeder.Run_point_Address_A,
Date() AS TestDate
FROM Test_Points_Feeder
GROUP BY Test_Points_Feeder.Run_No, Test_Points_Feeder.Point_Quiz_ID,
Test_Points_Feeder.Run_point_Venue_A,
Test_Points_Feeder.Run_point_Address_A,
Date(), Rnd([Point_Quiz_ID])
HAVING (((Test_Points_Feeder.Run_No) Between
[Forms]![frm_Runs].[RunFromCombo] And [Forms]![frm_Runs].[RunToCombo]))
ORDER BY Rnd([Point_Quiz_ID]);

Allen Browne said:

It's not that difficult, and it doesn't really matter how complex the SQL
statement is. You just break it into 2 after TOP, and piece it back
together
with the number in between.

This kind of thing:

Dim strSql As String
If Me.txtTop >= 1 Then
strSql = "SELECT TOP " & Me.txtTop
Else
strSql = "SELECT "
End If
strSql = strSql & " Table1.* FROM Table1;"
CurrentDb.QueryDefs("Query1").SQL = strSql

Allen,

thanks for replying with a definitive answer; building the statement
dynamically sounds complex and beyond my abiliites. The other point is
that
this query is part of a group of in-development interlinked queries,
which
means I like to see the QBE grid as i go along.

regards

:

No. You cannot use a parameter for the TOP n value.

You could build the query statement dynamically, and assign it to the
SQL
property of the QueryDef.

Is it possible to have a form control that can adjust the 'Top n'
value
that
is based on a criteria in a query?

I have a query that I want to use with of a varying number of Top n
values
based on the dynamic figure that I input into a control on a form.
Is
this
possible?.

I seem to remember from the early days of Access 1.0 & 2.0 that the
'Top
n'
value can be used in a criteria on the QBE grid, or did I imagine
that?

Click to expand...

Click to expand...

Allen Browne · Jan 4, 2008

1. You already have the SELECT at the top. Don't add it again.

2. You need to either enter all those lines as one line, or else close the
string and concatenate the line line, e.g.
strSql = strSql & "Test_Points_Feeder.Run_No, " & _
"Test_Points_Feeder.Point_Quiz_ID, " & _
"Test_Points_Feeder.Run_point_Venue_A, " & ...

efandango · Jan 4, 2008

Allen,

This is what I now have in my AfterUpdate property for the txtTop control on
my main form: I managed to make the SQL all one line, but nothing happens to
the underlying query that feeds my subform.

Private Sub txtTop_AfterUpdate()
Dim strSql As String
If Me.txtTop >= 1 Then
strSql = "SELECT TOP " & Me.txtTop
Else
strSql = "SELECT "
End If
strSql = strSql & "Test_Points_Feeder.Run_No,
Test_Points_Feeder.Point_Quiz_ID, Test_Points_Feeder.Run_point_Venue_A,
Test_Points_Feeder.Run_point_Address_A, Date() AS TestDate FROM
Test_Points_Feeder GROUP BY Test_Points_Feeder.Run_No,
Test_Points_Feeder.Point_Quiz_ID, Test_Points_Feeder.Run_point_Venue_A,
Test_Points_Feeder.Run_point_Address_A, Date(), Rnd([Point_Quiz_ID])HAVING
(((Test_Points_Feeder.Run_No) Between [Forms]![frm_Runs].[RunFromCombo] And
[Forms]![frm_Runs].[RunToCombo]))ORDER BY Rnd([Point_Quiz_ID]);"

End Sub

Allen Browne said:
1. You already have the SELECT at the top. Don't add it again.

2. You need to either enter all those lines as one line, or else close the
string and concatenate the line line, e.g.
strSql = strSql & "Test_Points_Feeder.Run_No, " & _
"Test_Points_Feeder.Point_Quiz_ID, " & _
"Test_Points_Feeder.Run_point_Venue_A, " & ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

efandango said:

Allen,

I pasted this together, but I get some red error lines in the following
code. I have marked them (R)

Private Sub txtTop_AfterUpdate()
Dim strSql As String
If Me.txtTop >= 1 Then
strSql = "SELECT TOP " & Me.txtTop
Else
strSql = "SELECT "
End If
strSql = strSql & "SELECT Test_Points_Feeder.Run_No,
Test_Points_Feeder.Point_Quiz_ID, Test_Points_Feeder.Run_point_Venue_A,"

(R) Test_Points_Feeder.Run_point_Address_A, Date() As TestDate FROM
Test_Points_Feeder
GROUP BY Test_Points_Feeder.Run_No, Test_Points_Feeder.Point_Quiz_ID,
Test_Points_Feeder.Run_point_Venue_A,(R)

Test_Points_Feeder.Run_point_Address_A , Date, Rnd([Point_Quiz_ID])

(R) HAVING (((Test_Points_Feeder.Run_No) Between
[Forms]![frm_Runs].[RunFromCombo] And [Forms]![frm_Runs].[RunToCombo]))
ORDER BY Rnd([Point_Quiz_ID]);(R)

I don't know how to resolve these red Error lines; this the 1st time I
have
used SQL in a VBA Window

Should I just concatenate the whole thing, or somehow format it with "

Click to expand...

Allen Browne · Jan 5, 2008

Assign the SQL statement to the RecordSource of the form in the subform
control:
Me.[Sub1].Form.RecordSource = strSql

If you have problems, add the line:
Debug.Print strSql
Then when it fails, open the Immediate Window (Ctrl+G) and copy what came
out. Paste into a new query in SQL View, and see what happens.

Generally you want a space before new words (like HAVING and ORDER BY),
though you may get away with it with the brackets there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

efandango said:
Allen,

This is what I now have in my AfterUpdate property for the txtTop control
on
my main form: I managed to make the SQL all one line, but nothing happens
to
the underlying query that feeds my subform.

Private Sub txtTop_AfterUpdate()
Dim strSql As String
If Me.txtTop >= 1 Then
strSql = "SELECT TOP " & Me.txtTop
Else
strSql = "SELECT "
End If
strSql = strSql & "Test_Points_Feeder.Run_No,
Test_Points_Feeder.Point_Quiz_ID, Test_Points_Feeder.Run_point_Venue_A,
Test_Points_Feeder.Run_point_Address_A, Date() AS TestDate FROM
Test_Points_Feeder GROUP BY Test_Points_Feeder.Run_No,
Test_Points_Feeder.Point_Quiz_ID, Test_Points_Feeder.Run_point_Venue_A,
Test_Points_Feeder.Run_point_Address_A, Date(), Rnd([Point_Quiz_ID])HAVING
(((Test_Points_Feeder.Run_No) Between [Forms]![frm_Runs].[RunFromCombo]
And
[Forms]![frm_Runs].[RunToCombo]))ORDER BY Rnd([Point_Quiz_ID]);"

End Sub

Allen Browne said:

1. You already have the SELECT at the top. Don't add it again.

2. You need to either enter all those lines as one line, or else close
the
string and concatenate the line line, e.g.
strSql = strSql & "Test_Points_Feeder.Run_No, " & _
"Test_Points_Feeder.Point_Quiz_ID, " & _
"Test_Points_Feeder.Run_point_Venue_A, " & ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

efandango said:

Allen,

I pasted this together, but I get some red error lines in the following
code. I have marked them (R)

Private Sub txtTop_AfterUpdate()
Dim strSql As String
If Me.txtTop >= 1 Then
strSql = "SELECT TOP " & Me.txtTop
Else
strSql = "SELECT "
End If
strSql = strSql & "SELECT Test_Points_Feeder.Run_No,
Test_Points_Feeder.Point_Quiz_ID,
Test_Points_Feeder.Run_point_Venue_A,"

(R) Test_Points_Feeder.Run_point_Address_A, Date() As TestDate FROM
Test_Points_Feeder
GROUP BY Test_Points_Feeder.Run_No, Test_Points_Feeder.Point_Quiz_ID,
Test_Points_Feeder.Run_point_Venue_A,(R)

Test_Points_Feeder.Run_point_Address_A , Date, Rnd([Point_Quiz_ID])

(R) HAVING (((Test_Points_Feeder.Run_No) Between
[Forms]![frm_Runs].[RunFromCombo] And [Forms]![frm_Runs].[RunToCombo]))
ORDER BY Rnd([Point_Quiz_ID]);(R)

I don't know how to resolve these red Error lines; this the 1st time I
have
used SQL in a VBA Window

Should I just concatenate the whole thing, or somehow format it with "

Click to expand...

Click to expand...

efandango · Jan 5, 2008

Allen,

"Assign the SQL statement to the RecordSource of the form in the subform
control:
Me.[Sub1].Form.RecordSource = strSql"

this is what i was afraid of when I mentioned earlier about multiple
queries, etc.

The problem is that my form is not directly linked to the query in question
that i want to control. The form's underlying table is created as part of a
an Append query sequence first, then the final table is created for the
subform. This table's data is designed to be dispensible one the data is used
for posing quiz questions. Then the form is requeried, setting of a complex
chain of delete & append queries that essentially load a new set of questions
into the form. The reason for me wanting to set the Top value on one of the
queries is to allow the user to decide how many questions he wants to answer.

Allen Browne said:
Assign the SQL statement to the RecordSource of the form in the subform
control:
Me.[Sub1].Form.RecordSource = strSql

If you have problems, add the line:
Debug.Print strSql
Then when it fails, open the Immediate Window (Ctrl+G) and copy what came
out. Paste into a new query in SQL View, and see what happens.

Generally you want a space before new words (like HAVING and ORDER BY),
though you may get away with it with the brackets there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

efandango said:

Allen,

This is what I now have in my AfterUpdate property for the txtTop control
on
my main form: I managed to make the SQL all one line, but nothing happens
to
the underlying query that feeds my subform.

Private Sub txtTop_AfterUpdate()
Dim strSql As String
If Me.txtTop >= 1 Then
strSql = "SELECT TOP " & Me.txtTop
Else
strSql = "SELECT "
End If
strSql = strSql & "Test_Points_Feeder.Run_No,
Test_Points_Feeder.Point_Quiz_ID, Test_Points_Feeder.Run_point_Venue_A,
Test_Points_Feeder.Run_point_Address_A, Date() AS TestDate FROM
Test_Points_Feeder GROUP BY Test_Points_Feeder.Run_No,
Test_Points_Feeder.Point_Quiz_ID, Test_Points_Feeder.Run_point_Venue_A,
Test_Points_Feeder.Run_point_Address_A, Date(), Rnd([Point_Quiz_ID])HAVING
(((Test_Points_Feeder.Run_No) Between [Forms]![frm_Runs].[RunFromCombo]
And
[Forms]![frm_Runs].[RunToCombo]))ORDER BY Rnd([Point_Quiz_ID]);"

End Sub

Allen Browne said:

1. You already have the SELECT at the top. Don't add it again.

2. You need to either enter all those lines as one line, or else close
the
string and concatenate the line line, e.g.
strSql = strSql & "Test_Points_Feeder.Run_No, " & _
"Test_Points_Feeder.Point_Quiz_ID, " & _
"Test_Points_Feeder.Run_point_Venue_A, " & ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I pasted this together, but I get some red error lines in the following
code. I have marked them (R)

Private Sub txtTop_AfterUpdate()
Dim strSql As String
If Me.txtTop >= 1 Then
strSql = "SELECT TOP " & Me.txtTop
Else
strSql = "SELECT "
End If
strSql = strSql & "SELECT Test_Points_Feeder.Run_No,
Test_Points_Feeder.Point_Quiz_ID,
Test_Points_Feeder.Run_point_Venue_A,"

(R) Test_Points_Feeder.Run_point_Address_A, Date() As TestDate FROM
Test_Points_Feeder
GROUP BY Test_Points_Feeder.Run_No, Test_Points_Feeder.Point_Quiz_ID,
Test_Points_Feeder.Run_point_Venue_A,(R)

Test_Points_Feeder.Run_point_Address_A , Date, Rnd([Point_Quiz_ID])

(R) HAVING (((Test_Points_Feeder.Run_No) Between
[Forms]![frm_Runs].[RunFromCombo] And [Forms]![frm_Runs].[RunToCombo]))
ORDER BY Rnd([Point_Quiz_ID]);(R)

I don't know how to resolve these red Error lines; this the 1st time I
have
used SQL in a VBA Window

Should I just concatenate the whole thing, or somehow format it with "

Click to expand...

Click to expand...

Allen Browne · Jan 6, 2008

Okay. I need to move on to other threads now.

efandango · Jan 6, 2008

Allen, I understand.

Thanks for trying with me all the same.