Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Working with List Boxes

List Boxes

Working with List Boxes

by  TheAceMan1  Posted    (Edited  )
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:

[color blue]
Code:
Private Sub lstData_Click()
   Me!lstEvents.RowSource = Me!lstEvents.RowSource & "On_Click;"
End Sub
[/color]

You do not have to include the [color blue]Focus[/color] 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.

[color blue]
Code:
***********************************
*          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
[/color]

[color purple]Note: An AfterUpdate & Click event always occurs when an Item is selected![/color]

[color blue]
Code:
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
[/color]

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

[color blue]
Code:
*******************************
*       Special Case #1       *
*******************************
* RowSourceType: Value List   *
* RowSource: Has Data         *
*******************************
[/color]
Here, the only problem I've found to occur involves the [color blue]Selected[/color] 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:

[color purple]
Code:
Me!ListBox.Selected(1)=True 
Works . . .

Me!ListBox.Selected(1)=False
Does not.
[/color]

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

[color blue]Me!LB.Recordsource = Me!LB.Recordsource[/color]

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

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

[color blue]
Code:
*******************************
*       Special Case #2       *
*******************************
* RowSourceType = Table/Query *
* RowSource is Blank/Empty    *
* Column Heads : No           *
*******************************
[/color]
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 [color purple]is underlined[/color]. Clicking above this line (as if selecting an item) produces the same results as [color blue]Main Setup #1[/color]. 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 [color blue]ListIndex[/color] nor [color blue]ListCount[/color] 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 [color blue]Selected[/color] property [color purple](available only at runtime)[/color]. The following code should be added to the event used to circumvent this condition:

[color blue]
Code:
   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
[/color]

Although its easy to detect the empty RowSource with something like:
[color purple]
Code:
If Len(Me.RowSource & "") > 0
[/color]
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 [color blue]Main Setup #1 and #2[/color].

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.





Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top