Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Microsoft: Access Forms FAQ

List Boxes

Working with List Boxes by TheAceMan1
Posted: 21 Jan 04 (Edited 23 Jan 04)

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

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

*           Main Setup #2          *
* RowSourceType: Table/Query       *
* RowSource: Table/Query No Data   *
Click Anything    DbClick Anything
--------------   -----------------
On_MouseDown     On_MouseDown
On_MouseUp       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:

Works . . .

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
      'What you want do do if listBox is empty!

   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

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close