Hi Georgia,
What Arvin showed you was how to create then open a recordset in code. It
IS working, but you're not doing anything with the recordset after you've
created it. If you actually want to create and execute the query in the
Access GUI, you have to do something just a little different. You have to
create and save the query and then execute it. Something like this:
'-----------------------------
Private Sub cboTopValues_AfterUpdate()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim n As Integer
Dim strsql As String
Set db = CurrentDb
n = Val(Me![cbotopvalues].Text)
strsql = "SELECT TOP " & n & " [181+_total] from qryaginsummarytopN order
by [181+_total] desc;"
'*** delete the previous query
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strsql)
'*** open the query
DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit
Exit_cmdRunQuery_Click:
Exit Sub
Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the
sub
Resume Exit_cmdRunQuery_Click
End If
End Sub
'-----------------------------
Notes: The query it creates is called qryMyQuery. If you want it named
something else, change it in the code. The error trapping is for the case
where the query does not exist. If you try to delete a query that doesn't
exist, you'll get an error.
BTW, the sample I mentioned illustrates this technique and many variations
on it.
--
--Roger Carlson
Access Database Samples:
www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
GEORGIA said:
ok.. i thought it was working..but it's not.
how do i get to show the result?
it won't bring up the result.
here's my code:
Private 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 & " [181+_total] from qryaginsummarytopN order
by [181+_total] desc;"
Set rst = db.OpenRecordset(strsql)
End Sub
thank you
Arvin Meyer said:
As Roger suggests, you need to do it in code by supplying the Top value as a
variable (in this case n):
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
Is there a way to make TOP N value as a parameter query? Meaning, I want
to
have the parameter box pop up when i run this query and put in whatever
number i want. For example, if i want top 25, just type in 25 or 10 or 5
....
I want to eventually create a form to where user can input their own top N
value to pull. Thanks in advance.
