Limit number of records in List Box 2

[dcurtis]

Is there a way to limit the number of records that will show in a list box? The data source of my list box will show many more records, but I want to limit it to the 5 or 10 most recent entries.

 

[luceze]

You can use this code to populate the listbox with the last 10 records in your recordset.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("YourTableOrQuery")
rs.MoveLast
    For x = 1 To 10
        Me.ListX.AddItem rs!YourField
        rs.MovePrevious

    Next

Change all the references to "your..." to the appropriate objects. You will also need a reference to DAO 3.X. I can give you an ADO solution if you need it.

HTH,
Eric

 

[dcurtis]

luceze

I get a "Variable Not Defined" for the "For x =" in your code.

Thanks for your help so far.

 

[luceze]

Sorry.
Add this line at the beginning

Dim x as integer

HTH,
Eric

 

[dcurtis]

I double checked to make sure I had all my references, but still get an error on the

Me.ListX.AddItem rs!YourField

line of code. Says that listx is not defined (I don't remember exactly and I'm away from my laptop with the DB on it).

Thanks again.

 

[luceze]

ListX should be the name of your listbox.

Eric

 

[dcurtis]

OK, now I feel foolish. Thanks again.

 

[luceze]

You're welcome.

 

[jrbarnett]

Alternatively you use as the recordsource:

SELECT TOP 10 Field1, Field2, Field3
FROM Queryname
WHERE condition
ORDER BY Field1

John

 

[luceze]

John,

I'm not much of a SQL programmer. Will the "Top 10" select the top 10 records according to sequence? For example, I have 100 records, only 90 through 100 are displayed.

Eric

 

[dcurtis]

Thanks again for your help, worked like a charm. I am going to try jrbarnett's suggestion also, just so I have another option available to me in the future.

 

[jrbarnett]

Luceze

SELECT TOP 10 ... will return the top 10 records as a recordset. In a recordset without that would return 100 records, putting TOP 10 would only return 10 records.

I would strongly recommend learning SQL if you are going to be working with relational databases of any sort on a professional basis as it is very useful to help understand what the Access query designer generates. Additionally SQL code is far faster at doing certain data related tasks than VBA code.

John

 

[luceze]

Thanks John. I am learning, but as with most things I do at my company I am teaching myself. So my SQL is not anywhere near my VB thus my tendency for VB solutions over SQL.

Eric