How to get a listbox to display the first row

[JONeill]

I'm binding a runtime query to a listbox. I hit the cancel button and the listbox displays the last several rows in the recordset rather than display the first row. ListIndex and the properties I use in Excel cannot be assigned values in Access. How can I get the listbox to display the first row? Tried to refresh the recordset but that doesn't work either.

 

[xenou]

One way: you can edit the query source to run a Top (1) query.

 

[JONeill]

Not sure what you mean by that.

 

[xenou]

Googling MSAccess Top 1 query gets tons of hits to explain what that is. Have you tried that yet?

 

[JONeill]

After rereading my initial post, I realized I didn't explain my question very well. I don't need just the first row. I need the whole recordset. So, say I have 50 records in the recordset but the listbox is only tall enough to display 25 of them. When I initially load the form, I see the first 25 records but on all subsequent refreshes after an some sort of update. Access scrolls down and see the last 25. I'd like to see the first 25 again when I first brought up the form. Sorry about the confusion.

 

[xenou]

Hmm that is a little different. Offhand I'd assume you are doing something different in the subsequent refreshes compared to the first one. How is the listbox being used? Because otherwise a refresh should simply refresh it and it would look the same (to test that theory, add a button so you can call a refresh right after you load it for the first time to confirm that the refresh itself isn't changing the way it looks).

Note that you do have ways of accessing list properties but it's a little different depending on what type of list box you are actually using (and how you set it up (multiselect, single select, extended selection). So you have to be patient and figure it out.

 

[JONeill]

I'm new to Access so, I think I'm programming more like VB/C# programmer. I'm not using ADO the same way since I'm hacking through it. All my queries are in modules and they either return a recordset or rowsAffected. So, I don't have any queries wired to controls. I have seen the DoCmd.??? to refresh a record or recordset. It was originally going to be an Excel app but it's better suited to Access.

 

[xenou]

It's true that there are lot of ways to get data into a listbox. You probably don't have to use ADO for *all* your queries - if you literally are doing that. Access Queries can be created without ADO just going to query designer and typing in the SQL. For that matter listboxes can just have the query source in the listbox properties (rowsource I think is what it's called).

No matter, the main thing I think is that probably you want to do a refresh at the earliest possible point just to determine the behavior of your refreshes before anything else happens. If it is different later on then probably their is a change in the state of the form that is causing the difference (which I guess is obvious - but you would need to figure out what the difference is).

 

[JONeill]

I realized that the listbox is actually scrolling to the bottom of the recordset on Form_Load. So, it has to be something I changed in between and didn't notice. I'm using DAO but I guess I'm using it in an ADO kind of way.
In Form_Load, the call is
lstMaintLog.Recordset = GetListboxData()

GetListboxData() actually calls the function that calls the query into a recordset, checks for errors or 0 record counts and returns a recordset. Not sure if that's overkill for Access or not. I will do some more research and post what I find.

 

[xenou]

Interesting. I guess I'm not in possession of any other ideas at the moment, although I would hazard that in general a recordset starts with the record pointer at the beginning of the recordset. If you are checking records counts though, you may be moving forward to the end of the recordset to make sure the recordset is fully loaded before you get the count (and with some types of recordsets that could even be necessary to get a correct count). In which case you'd want to move the recordset pointer back to the beginning.