Listbox

alastair · January 19th, 2006, 05:32 PM

I'm using a listbox to display names: the user clicks a name and various fields from the chosen person(address etc) are shown on the form.

The form has 3 toggle buttons which determine whether the listbox shows all names, male names only, or female names only. Each toggle button sets the listbox's rowsource to an appropriate query in which the names are selected from a table and sorted alphabetically.

The only point I can't figure is this: when the user clicks a toggle button, I want the form to display the fields for the first record in the listbox. Whereas the listbox is correctly updated to show the right names for that toggle button, the record displayed is not updated.

If an example file would help, mail [email protected]

Thanks

Bob Bedell · January 19th, 2006, 06:56 PM

Hi alastair:

Try:

Code:

Private Sub Command0_Click()

   Me.lstList.RowSource = "Query1"
   Me.lstList.Selected(0) = True
   Me.RecordsetClone.Findfirst "[ID] = " & Me.lstList.ItemData(lstList.ListIndex)
   Me.Bookmark = Me.RecordsetClone.Bookmark


End Sub

HTH,

Bob

Bob Bedell · January 19th, 2006, 07:02 PM

Probably also want to toss this in:

Code:

Private Sub lstList_AfterUpdate()
   Me.RecordsetClone.Findfirst "[ID] = " & Me.lstList.ItemData(lstList.ListIndex)
   Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

So the Form will reflect subsequent selections in the ListBox

HTH,

Bob

alastair · January 20th, 2006, 09:15 PM

Bob, I'm grateful to you for your reply. Your code is similar to mine, but even with yours a couple of problems remain:

1) The afterupdate event for the listbox causes the listbox to behave wrongly. Pressing "B" should cycle through the names (Bart, Bert, Bob, ...) but instead pressing B advances through these names in the order Bart, Bart, Bert, Bart, Bart, Bert, ... and so on. I know the code is producing this, because when I take out the event handler pressing B repeatedly moves through the items in the correct order.

2) The onclick event for the command button causes the listbox to lose the focus. Even if I set the focus as the last action in the event handler, after setting the form's bookmark to the proper record, the listbox doesn't receive the focus.

I have the form's rowsource set to the table on which all three of the queries are based - I assume this is the best solution.

Would appreciate your advice.

Bob Bedell · January 20th, 2006, 11:15 PM

Hi alastair,

Seems that Double_Click is the only event that doesn't interfere with the scrolling behavior. So the remedy is:

Code:

Private Sub lstList_DblClick(Cancel As Integer)
   Me.RecordsetClone.Findfirst "[ID] = " & Me.lstList.ItemData(lstList.ListIndex)
   Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

As for setting focus, this works for me:

Code:

Private Sub cmdMaleName_Click()
    Me.lstList.RowSource = "qryMaleNames"
    Me.lstList.Selected(0) = True
    Me.RecordsetClone.Findfirst "[ID] = " & Me.lstList.ItemData(lstList.ListIndex)
    Me.Bookmark = Me.RecordsetClone.Bookmark
    Me.lstList.SetFocus
End Sub

HTH,

Bob

alastair · January 25th, 2006, 01:33 PM

Thanks again Bob. It looks like the functionality of a listbox is more constrained than Microsoft let on !

I've spent some time trying to find information on this from the obvious sources - do you have good reference material or was it good old trial and error?

Bob Bedell · January 25th, 2006, 03:28 PM

Hi alastair,

The event bit was just trial and error. No idea why the other events interfere with that particular behavior.

As far as resources go, with the regular old Access Listbox its pretty much the Object Browser, VBA help file, and on-line snippets, though the VB help file has some more extensive (or at least different) code examples if you have Visual Studio installed or an MSDN Library subscription.

I also use the ListView ActiveX control that ships with Access a lot (considerably more functionality than the standard Listbox). The Access 2K2 version is in the MSComctlLib.ocx file that installs with Access 2K2. Unfortunately, Access doesnât know anything about the help files for the ActiveX controls it makes available (whatâs up with that?!?!?), so here you really need the VB help files (same controls) or MSDN library. Also, to get Intellisense to work with Access's ActiveX controls you have to instantiate your object variables a little, lets say, "unintuitively". You can, of course, read MSComctlLib in the Object Browser, however.

If you want to see some code by a guy whose really pushed the Access Listbox control to the limit, have a peak at Stephan Lebans stuff at:

http://www.lebans.com/List_Combo.htm

HTH,

Bob

alastair · January 25th, 2006, 05:53 PM

Thanks as ever !