Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Hubert, North Carolina, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ACCESS List box (97/2000)

    I have created a query that prompts you for a value to to type in called demoqry. I have then created a form with a list box called List2 that displays the results of the query. What I need to do is have the user select the correct record from the list box and then click an edit button which will bring up another form. Does anyone have any ideas on how to make this work?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: ACCESS List box (97/2000)

    This is most commonly done with a combo box rather than a list box, but either will work. There is a wizard available for creating combo boxes, and one of the options is to create a navigation combo box directly on the form you are working on that shows you the record selected on your form. The wizard generates all the code to make it work using a RecordsetClone statement in VBA, but we more typically use a filter to show just a single record on the bound form. If this doesn't make any sense, post back and I'll create a simple example.
    Wendell

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: ACCESS List box (97/2000)

    <<one of the options is to create a navigation combo box directly on the form you are working >>

    Wendell, is this available in A97 or in A2000 and beyond?

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ACCESS List box (97/2000)

    Both, but you only get that version of the wizard if you add a combobox to a bound form. If you're working with an unbound form, you never see that version of the wizard.
    Charlotte

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: ACCESS List box (97/2000)

    Actually, I think it's been around since Access 2.0 - for sure it was available in 95. When you have the wizard button active on the designer toolbar, and you place a combo on a form, it gives you a choice of three things to do - the bottom one is Find a record on my form based on the value I selected in my combo box. You step through the process, and it builds an unbound combo box which uses your table as the rowsource, and generates the code to go to the correct record.

    There are some issues with synchronizing the control with whatever record you want to start with when you open the form, but we typically solve that with code on opening the form. For whatever reason Access 2002 uses ADO by default to do the synchronizing, and the method doesn't seem to work as well. In any event we usually turn off the navigation buttons, and apply a filter much as you would when you use a command button to open a form to a specific record. It seems to work as fast or faster and is foolproof.
    Wendell

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: ACCESS List box (97/2000)

    Thanks Charlotte and Wendell.
    I have tried this in A97 and only get 2 options when using the combobox wizard, however I get 3 options when using A2000.
    I don't think it was around in Access 2.0 days because there was no RecordsetClone or was there.

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ACCESS List box (97/2000)

    Did you try it on a bound form? You won't see three options unless the form is bound to a recordsource. The wizard in 97 is apparently smart enough to know that you can't navigate through a recordset if there isn't one to navigate. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: ACCESS List box (97/2000)

    Sure did. I have never seen that in A97, that's why I was surprised when Wendell mentioned it.
    It's a great feature though.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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