Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How to get a listbox to display the first row

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to get a listbox to display the first row

    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.

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,304
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to get a listbox to display the first row

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

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular
    Join Date
    Sep 2018
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get a listbox to display the first row

    Not sure what you mean by that.

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,304
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to get a listbox to display the first row

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

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    Board Regular
    Join Date
    Sep 2018
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get a listbox to display the first row

    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.

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,304
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to get a listbox to display the first row

    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.
    Last edited by xenou; Mar 19th, 2019 at 02:30 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    Board Regular
    Join Date
    Sep 2018
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get a listbox to display the first row

    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.

  8. #8
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,304
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to get a listbox to display the first row

    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).
    Last edited by xenou; Apr 22nd, 2019 at 10:42 AM. Reason: changed 'with ADO' to 'without ADO'

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  9. #9
    Board Regular
    Join Date
    Sep 2018
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get a listbox to display the first row

    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.

  10. #10
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,304
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to get a listbox to display the first row

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •