Prompt for properties on a query....

L

Lynn

I have a query that I use to pull the Top 25, top 50 and top 100 records. Is
there a way that you can set up the query to prompt you for the top value
property when you open the query instead of going into the design view of the
query each time?
 
D

Douglas J. Steele

No.

However, you could have a form that gives the user the option of selecting
the number to use, and dynamically change the query's SQL before executing
it.
 
B

Brendan Reynolds

You can't do it using the query alone, no. You can do it by generating SQL
dynamically at run time, however. For example, the following code would fill
a list box with a number of records determined by the number entered in a
text box ...

Private Sub txtNumberOfEmployees_AfterUpdate()

Me.lstEmployeeList.RowSource = "SELECT TOP " & _
Me.txtNumberOfEmployees & " Employees.* " & _
"FROM Employees ORDER BY EmployeeID"

End Sub
 
A

Arvin Meyer [MVP]

Lynn said:
I have a query that I use to pull the Top 25, top 50 and top 100 records. Is
there a way that you can set up the query to prompt you for the top value
property when you open the query instead of going into the design view of the
query each time?
Click to expand...

How about using a combo box to get the TopValues property. Something like:

Sub cboTopValues_AfterUpdate()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim n As Integer
Dim strSQL as string

Set db = CurrentDb

n = Val(Me![cboTopValues].Text)

strSQL = "SELECT TOP " & n & " Qty FROM MyTable ORDER BY Qty DESC;"

Set rst = db.OpenRecordset(strSQL)

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
L

Lynn

I tried this but it is not working. It appears to be trying to pull the data
but nothing ever happens.

Arvin Meyer said:
Lynn said:
I have a query that I use to pull the Top 25, top 50 and top 100 records. Is
there a way that you can set up the query to prompt you for the top value
property when you open the query instead of going into the design view of the
query each time?
Click to expand...

How about using a combo box to get the TopValues property. Something like:

Sub cboTopValues_AfterUpdate()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim n As Integer
Dim strSQL as string

Set db = CurrentDb

n = Val(Me![cboTopValues].Text)

strSQL = "SELECT TOP " & n & " Qty FROM MyTable ORDER BY Qty DESC;"

Set rst = db.OpenRecordset(strSQL)

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
Click to expand...
 
L

Lynn

I tried this but it did not seem to work. It appears to be running but
nothing ever happens......any suggestions?

Arvin Meyer said:
Lynn said:
I have a query that I use to pull the Top 25, top 50 and top 100 records. Is
there a way that you can set up the query to prompt you for the top value
property when you open the query instead of going into the design view of the
query each time?
Click to expand...

How about using a combo box to get the TopValues property. Something like:

Sub cboTopValues_AfterUpdate()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim n As Integer
Dim strSQL as string

Set db = CurrentDb

n = Val(Me![cboTopValues].Text)

strSQL = "SELECT TOP " & n & " Qty FROM MyTable ORDER BY Qty DESC;"

Set rst = db.OpenRecordset(strSQL)

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
Click to expand...
 
Top