Top N value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I have originally posted this question and got the answer but I cannot
seem to find the original post. so sorry, I am posting again. My question
was, I wanted to created a combo box or dropdown list with such values : "10,
15, 20, 25" or simply a text box for user to enter the number and get the top
n records based on that value. I did not want to create one query for each
top whatever and wanted to know if there's a way for to be parameter. and
this is the code I received:

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


I just cannot get this to work. I replaced "Qty" with balance and "mytable"
with tblTag2 from my table.
I have also created the form with combo box and name it "cboTopValues".
Instead of Sub cboTopValues_AfterUpdate(), I have made it as
Function topvalue()

and created a command button on my form and on click to call that function.
What I want to do is, once the user uses the top whatever value and click on
the bottom, bring up the recrods.
Please help! Thanks
 
thank you very much!

got it to work now...


Tom Wickerath said:
Hi Georgia,

I think this is the thread you are looking for:

http://groups.google.com/group/micr..._frm/thread/d262f57f822ba67a/e582771b10037769


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

GEORGIA said:
Hi. I have originally posted this question and got the answer but I cannot
seem to find the original post. so sorry, I am posting again. My question
was, I wanted to created a combo box or dropdown list with such values : "10,
15, 20, 25" or simply a text box for user to enter the number and get the top
n records based on that value. I did not want to create one query for each
top whatever and wanted to know if there's a way for to be parameter. and
this is the code I received:

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


I just cannot get this to work. I replaced "Qty" with balance and "mytable"
with tblTag2 from my table.
I have also created the form with combo box and name it "cboTopValues".
Instead of Sub cboTopValues_AfterUpdate(), I have made it as
Function topvalue()

and created a command button on my form and on click to call that function.
What I want to do is, once the user uses the top whatever value and click on
the bottom, bring up the recrods.
Please help! Thanks
Click to expand...
Click to expand...
 
one quick questions...

as it mentioned it in the link below.. it is excuting after_update. like so:
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 & " [balance], tbltag2.* from tbltag2 order by
[balance] desc;"



db.QueryDefs.Delete "qrytopx"
Set qdf = db.CreateQueryDef("qrytopx", strsql)


DoCmd.OpenQuery "qrytopx", acNormal, acEdit


Exit_cmdRunQuery_Click:
Exit Sub


Err_cmdRunQuery_Click:
If Err.Number = 3265 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_cmdRunQuery_Click
End If
End Sub

and it works perfectly.

however, when i try to put it under

Private Sub Command2_Click()

End Sub

it gives me run-time error 2185. you can't reference a property or method
for a control unless the conntrol has the focus.
i'm not exactly sure what that means. I tried setting the box on focus but
that didn't work.
thanks!

Tom Wickerath said:
Hi Georgia,

I think this is the thread you are looking for:

http://groups.google.com/group/micr..._frm/thread/d262f57f822ba67a/e582771b10037769


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

GEORGIA said:
Hi. I have originally posted this question and got the answer but I cannot
seem to find the original post. so sorry, I am posting again. My question
was, I wanted to created a combo box or dropdown list with such values : "10,
15, 20, 25" or simply a text box for user to enter the number and get the top
n records based on that value. I did not want to create one query for each
top whatever and wanted to know if there's a way for to be parameter. and
this is the code I received:

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


I just cannot get this to work. I replaced "Qty" with balance and "mytable"
with tblTag2 from my table.
I have also created the form with combo box and name it "cboTopValues".
Instead of Sub cboTopValues_AfterUpdate(), I have made it as
Function topvalue()

and created a command button on my form and on click to call that function.
What I want to do is, once the user uses the top whatever value and click on
the bottom, bring up the recrods.
Please help! Thanks
Click to expand...
Click to expand...
 
ok sorry.. i spoke too soon. I guess I should have thought about it before
replying..

i replaced
n = Val(Me![cboTopValues].Text)
to
n = Val(Me![cboTopValues].value)
then it works under click event.

thanks!
GEORGIA said:
one quick questions...

as it mentioned it in the link below.. it is excuting after_update. like so:
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 & " [balance], tbltag2.* from tbltag2 order by
[balance] desc;"



db.QueryDefs.Delete "qrytopx"
Set qdf = db.CreateQueryDef("qrytopx", strsql)


DoCmd.OpenQuery "qrytopx", acNormal, acEdit


Exit_cmdRunQuery_Click:
Exit Sub


Err_cmdRunQuery_Click:
If Err.Number = 3265 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_cmdRunQuery_Click
End If
End Sub

and it works perfectly.

however, when i try to put it under

Private Sub Command2_Click()

End Sub

it gives me run-time error 2185. you can't reference a property or method
for a control unless the conntrol has the focus.
i'm not exactly sure what that means. I tried setting the box on focus but
that didn't work.
thanks!

Tom Wickerath said:
Hi Georgia,

I think this is the thread you are looking for:

http://groups.google.com/group/micr..._frm/thread/d262f57f822ba67a/e582771b10037769


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

GEORGIA said:
Hi. I have originally posted this question and got the answer but I cannot
seem to find the original post. so sorry, I am posting again. My question
was, I wanted to created a combo box or dropdown list with such values : "10,
15, 20, 25" or simply a text box for user to enter the number and get the top
n records based on that value. I did not want to create one query for each
top whatever and wanted to know if there's a way for to be parameter. and
this is the code I received:

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


I just cannot get this to work. I replaced "Qty" with balance and "mytable"
with tblTag2 from my table.
I have also created the form with combo box and name it "cboTopValues".
Instead of Sub cboTopValues_AfterUpdate(), I have made it as
Function topvalue()

and created a command button on my form and on click to call that function.
What I want to do is, once the user uses the top whatever value and click on
the bottom, bring up the recrods.
Please help! Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top