Hello everyone! . . . . . .

I've been programming Access since version 1.x, and since the advent of ListBoxes, a number of redundant questions & problems always seem to find me. Particularly in the handling of events and problems thereof. I offer the following sequence of events & special cases(generated by a ListBox) as an aid in developement, troubleshooting
and decision making.

If you want to set it up yourself, the event sequences can easily be generated & viewed as follows:

On a form setup two listboxes, lbxData & lbxEvents. lbxData is setup as you would any other. lbxEvents(where you view the actual events) is setup with a RowSourceType of Value List and an empty RowSource. In all the events of lbxData except Mouse Move, add a line of code that appends a descritpion of the event. Example:

Private Sub lstData_Click()
   Me!lstEvents.RowSource = Me!lstEvents.RowSource & "On_Click;"
End Sub

You do not have to include the Focus events as they always work as prescribed and always occur first. You should add a command button that resets lbxEvents RowSource to "".

With that, here's what a list box will give you.

***********************************
*          Main Setup #1          *
***********************************
* RowSourceType: Table/Query      *
* RowSource: Table/Query Has Data *
***********************************
Click an Item    DbClick an Item
--------------   ----------------
On_MouseDown     On_MouseDown
On_MouseUp       On_MouseUp
BeforeUpdate     BeforeUpdate
AfterUpdate      AfterUpdate
On_Click         On_Click
                 On_DblClick
                 On_MouseUp

Note: An AfterUpdate & Click event always occurs when an Item is selected!

Click a Header     DblClick a Header
or Empty Space     or Empty Space
under last Item    under last Item
---------------    -----------------
On_MouseDown       On_MouseDown
On_MouseUp         On_MouseUp
                   On_DblClick
                   On_MouseUp

************************************
*           Main Setup #2          *
************************************
* RowSourceType: Table/Query       *
* RowSource: Table/Query No Data   *
************************************
Click Anything    DbClick Anything
--------------   -----------------
On_MouseDown     On_MouseDown
On_MouseUp       On_MouseUp
                 On_DblClick
                 On_MouseUp

Note: On double-clicking anything the On_MouseUp event occurs twice! Running an event twice in this way is undesirable!

*******************************
*       Special Case #1       *
*******************************
* RowSourceType: Value List   *
* RowSource: Has Data         *
*******************************
Here, the only problem I've found to occur involves the Selected property. This property is read/write. Reading tells if the current indexed item is selected or not. Writing sets/resets the selected item(true/false). Specifically, reading works, writing does not. If you want to return the listbox to an unselected state for instance, you can't turn a selection off! As an example:

Me!ListBox.Selected(1)=True
Works . . .

Me!ListBox.Selected(1)=False
Does not.

The only way I've come upon to get an unselected list is as follows(LB=ListBoxName):

Me!LB.Recordsource = Me!LB.Recordsource

Although it appears circular, its not. The effect is the same as if you requeried the listbox.

Also, if you use Value List as the RowSourceType, there is a 2K limit on the RowSource string. That is 2048 characters including semicolon seperators.

*******************************
*       Special Case #2       *
*******************************
* RowSourceType = Table/Query *
* RowSource is Blank/Empty    *
* Column Heads : No           *
*******************************
This is a unique case. When the form is first opened the listbox is empty as expected. However, where the first item would go at the top is underlined. Clicking above this line (as if selecting an item) produces the same results as Main Setup #1. Since there are no items in the list, the event used needs to detect this and bypass running any code or alert the user.

Ironically, neither the ListIndex nor ListCount properties come up with a proper value for detection. Traversing all available properties that can be used, the only property which comes up true for this case was the Selected property (available only at runtime). The following code should be added to the event used to circumvent this condition:

   Dim Lbx As ListBox
   Set Lbx = Me!YourListBoxName

   If Lbx.Selected(Lbx.ListIndex) Then
      'Your normal code here
   Else
      'What you want do do if listBox is empty!
   Endif

   Set Lbx=nothing

Although its easy to detect the empty RowSource with something like:
   If Len(Me.RowSource & "") > 0
Its more logically sound to get the response from the ListBox itself.

All other combinations of RowSourceType, RowSource, Column Heads, and wether or not the RowSource has data, produces the same results as Main Setup #1 and #2.

Note: The special cases cited here are not bugs, its simply the way things are . . . . . . Also, referencing through ADO has not been tested in this way.

Note: There are times when the design of a ListBox just does'nt work right. I can't explain it, it just happens. Deleting and redesigning the ListBox from scratch takes care of this.

This should clear the field for an redundant array of questions and problems. I welcome any input, queries or opinions.

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

My FAQ Archive

Come Join Us!

Posting Guidelines

Jobs

Microsoft: Access Forms FAQ

List Boxes

My Archive

Join Tek-Tips® Today!

Join Tek-Tips^® Today!