Listbox acts like it is bound when it is not

[Steve Jensen]

I have a listbox that has a query rowsource. When I change a value in a
dropdown list, the listbox is requeried and displays a different set of rows.
There is no multiselect (you can only choose one item on the list). The
list is not bound to a field in the form recordsource.

The problem lies in trying to make the first row selected whenever the
listbox is requeried. I have tried several methods of setting row index 0 as
selected, with the same result. The control is left in a state which
prevents any further action on the form (you cannot requery the form, move
the focus to a different control, or basically do anything. Once you
manually click the first row in the listbox, you can do other things. The
error messages (2110, 2115, 2118 depending on what I am trying to do after
setting the list selection) all indicate that the control must be updated
before doing anything else. It says that the Before Update event or
Validation is preventing Access from performing the action (say, setting the
focus to a different control).

If I type me.(listname).undo in the immediate pane, I can move the focus,
but that also unselects row 0.

The whole thing is making me crazy. This control is unbound, so there is no
updating to do. There are no beforeupdate events, and no validation.

I have created a practice database with this scenario if anyone wants to
look at it. It is Access 2002.

 

[Martin]

The BeforeUpdate event can be tricky like this. Can you use the AfterUpdate
event instead? Just be careful as this can sometimes loop annoyingly (so
save before running it!). Alternatively OnExit/Enter.

 

[Steve Jensen]

Martin, there is no BeforeUpdate event on the entire form. This is something
that is happening to the listbox control that I do not understand. I would
expect that once I set the list index of the listbox control to 0 that would
be the end of it; however, once I do this, I cannot programmatically do
anything because Access thinks there is some BeforeUpdate event being
triggered when there is not. The listbox is not bound to a field on the form.

I have an unbound combo box that shows a list of years. The after update
event of the combo box requeries the list box, which displays a list of
events filtered by year. The list box is not bound to a field on the form.

The code goes like this:

sub combo1_afterupdate()

me.listbox1.requery
me.listbox1.setfocus
me.listbox1.ListIndex = 0

me.requery

end sub

After executing the first 3 lines (setting the listindex), the listbox
control is put in a strange state, wherein Access thinks there is some saving
or updating that needs to be done. This causes the following error on the
next line:

Error 2115: The macro or function set to the BeforeUpdate or ValidationRule
property for this field is preventing Microsoft Access from saving the data
in the field.

If I set a breakpoint at line 4 and type 'me.listbox1.undo' in the immediate
window, line 4 executes, but the first item in the list box becomes
unselected.

Again, this is Access XP. However, I have tried it with Access 2003 with
the same results.

 

[Sam D]

Martin, there is no BeforeUpdate event on the entire form. This is
something
that is happening to the listbox control that I do not understand. I
would
expect that once I set the list index of the listbox control to 0 that
would
be the end of it; however, once I do this, I cannot programmatically do
anything because Access thinks there is some BeforeUpdate event being
triggered when there is not. The listbox is not bound to a field on the
form.

I have an unbound combo box that shows a list of years. The after update
event of the combo box requeries the list box, which displays a list of
events filtered by year. The list box is not bound to a field on the
form.

The code goes like this:

sub combo1_afterupdate()

me.listbox1.requery
me.listbox1.setfocus
me.listbox1.ListIndex = 0

me.requery

end sub

After executing the first 3 lines (setting the listindex), the listbox
control is put in a strange state, wherein Access thinks there is some
saving
or updating that needs to be done. This causes the following error on the
next line:

Error 2115: The macro or function set to the BeforeUpdate or
ValidationRule
property for this field is preventing Microsoft Access from saving the
data
in the field.

If I set a breakpoint at line 4 and type 'me.listbox1.undo' in the
immediate
window, line 4 executes, but the first item in the list box becomes
unselected.

Again, this is Access XP. However, I have tried it with Access 2003 with
the same results.

Steve,

I haven't read the entire thread, however I would not select the first item
in a list box using

me.listbox1.ListIndex = 0

rather I would use...

me.listbox1.Selected(0)=True

Could be worth a try.

Sam

 

[Steve Jensen]

Sam, I have tried it this way. No difference. About the only thing I
haven't tried is making the rowsource a value list and programmatically
populating and unpopulating the control.

 

[Steve Jensen]

Sam, I have since written some code to populate and unpopulate the listbox
using the additem and removeitem methods. It makes not one bit of
difference. As soon as you set the listindex to 0 Access thinks there is
something to update. If you do not set the listindex to 0, the selected item
varies from nothing to something other than 0. It is as if the control
remembers the last index selected and sticks to it no matter what. for
instance, if you select item 3 on the list, then repopulate it with other
data, item 3 will still show as selected. If there are not 3 items, either
the last item will be selected or none. It is baffling and very frustrating.