UPDATE QUERY - TOP?

B

Bonnie

Hi,

I need to update the TOP ? records (based on input from a control on a
form). Any suggestions on how I should attack it?

Thanks in advance,

Bonnie
 
A

Arvin Meyer [MVP]

Build your Top values query first. Then build a second query with the Top
values query as the source, adding those field you want to update.
 
B

Bonnie

Thanks for responding Arvin. Is it possible to use input from a control for
the top parameter? So it would be TOP forms!myform.control?

Thanks,

Bonnie
 
A

Arvin Meyer [MVP]

Not in a query, but you can do it in code. Suppose txtTopValue is the
textbox with the Top value. Save the recordset data in a temporary table
(air code):

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

Set db = CurrentDb

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

strSQL = "SELECT TOP " & n & " ID, Qty FROM Table1 ORDER BY Table1.Qty
DESC;"

Set rst = db.OpenRecordset(strSQL)
Set rst2 = db.OpenRecordset("tmpTable", dbOpenDynaset)

With rst
.MoveFirst
Do Until .EOF
rst2.AddNew
rst2!ID = !ID
rst2!Qty = !Qty
rst2.Update
.MoveNext
Loop
End With

Exit_Here:
'Close everything
End Sub


Now you can pull a query on the data in the temp table. If you plan on
doing this regularly, add code in the beginning to delete the contents of
the temp table.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
A

a a r o n . k e m p f

SQL Server 2005 supports this syntax natively ;)

Update Top (100) Employees
Set Salary = Salary * 1.1


-Aaron
 
B

Bonnie

Thanks Arvin - that's exactly what I was looking for.

Arvin Meyer said:
Not in a query, but you can do it in code. Suppose txtTopValue is the
textbox with the Top value. Save the recordset data in a temporary table
(air code):

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

Set db = CurrentDb

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

strSQL = "SELECT TOP " & n & " ID, Qty FROM Table1 ORDER BY Table1.Qty
DESC;"

Set rst = db.OpenRecordset(strSQL)
Set rst2 = db.OpenRecordset("tmpTable", dbOpenDynaset)

With rst
.MoveFirst
Do Until .EOF
rst2.AddNew
rst2!ID = !ID
rst2!Qty = !Qty
rst2.Update
.MoveNext
Loop
End With

Exit_Here:
'Close everything
End Sub


Now you can pull a query on the data in the temp table. If you plan on
doing this regularly, add code in the beginning to delete the contents of
the temp table.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Bonnie said:
Thanks for responding Arvin. Is it possible to use input from a control
for
the top parameter? So it would be TOP forms!myform.control?

Thanks,

Bonnie
Click to expand...
Click to expand...
 
Top