I want to select the top X records using a user supplied number. How can I do that using Access 2000?

You would have to do this with code.

On the form, add a text box to take the number value.
Add a button to open the query.

On the button so something like:

Dim iTop As Integer
Dim sSQL As String

iTop = Me.txtTopField

sSQL = "SELECT TOP " & iTop & " FROM ..."

Then, where and how do you want to display the results... ?



mmcdonal

Have it show in a table. The results are then exported to Word Merge format (I know how to do that part).

Okay, here is code that takes data from one table and moves it to another. It also starts by deleting all the data in tblMerge first, to make sure it is clean before it starts.

This only moves the values from one field, but you can add others. Since I do mostly Access/SQL, this is ADO working on local tables. This will also work in Access, or Access/Access, as long as the tables are linked, otherwise you will need a connection object as well.

Don't worry about my table and field names, they are from another post database.

Dim iSelect As Integer
Dim sSQL1 As String
Dim sSQL2 As String
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim i As Integer

i = 0
iSelect = Me.Text3

DoCmd.SetWarnings False
DoCmd.OpenQuery "DELETEtblMerge"
DoCmd.SetWarnings True

sSQL1 = "SELECT TOP " & iSelect & " tblGus2.Num FROM tblGus2 ORDER BY tblGus2.Num"
Set rs1 = New ADODB.Recordset
rs1.Open sSQL1, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs1.MoveFirst

sSQL2 = "SELECT * FROM tblMerge"
Set rs2 = New ADODB.Recordset
rs2.Open sSQL2, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

    Do Until i = iSelect
        rs2.AddNew
        rs2("Num") = rs1("Num")
        rs2.Update
    rs1.MoveNext
    i = i + 1
    Loop

rs1.Close
rs2.Close


Did that help? It does work.


mmcdonal

I forgot to mention, the Text3 control is formatted:

Format: Standard
Decimal Places: 0



mmcdonal

A little customizing and I'll be fine. I've programmed Classic ASP and VB6 pages for SQL Server databases, and this looks like it's the same thing. Thanks for the direction (=