Listbox Row Selection - Problems Passing Values

[Terry]

I have an Access2000 form with a listbox and use the AfterUpdate event to
pass values to a TextBox using the .column property.

Me.txtIDInvoicing = Me.MyListBox.Column(20)

In Access2000 it works great, in Access2007 (compatibilty mode) the listbox
fails to give up it column values, they all come up Null.

I have converted the backend/frontend to Access2007 and still get the same
result, clicking a row highlights it but no values come from the columns. I
have also found that I cannot read the Selected value for the selected row,
it show as False, MultiSelect is set to None. The recordsource is a query.
Very odd behaviour.

Regards

 

[Allen Browne]

Works fine here. Tested with Access 2007 SP1.

Terry, are you sure you have the right column?
The first column is zero, so Column(20) is the value from the 21st column.

Or, perhaps the code is not running at all?
Trust the folder where the database resides.

 

[Terry]

Hi Allen,

Column ID's are correct. Can't get a trust established because the database
resides in the root of a mapped drive letter e.g. L:\

The AfterUpdate event is being triggered as I can step through from a
breakpoint. Even tried stepping through the rows to find the Selected value
and then use it in .Column(Col,Row) but the loop code runs right through all
rows and finds all at False (none selected), which I guess would give Null
values back from .Column().

Oddly, if I use ListBox.Requery in the AfterUpdate event, just before I pass
the Column values, it works. The Column values are passed when normally the
ListBox would be back at the first row, however, even though the row
highlight is removed the row still has the focus. Peculiar, or what? It's a
fix, but an odd one.

Just wondering whether I should receate the control from new in the hope
that the problem will be resolved.

Regards

 

[Allen Browne]

Terry, I don't know what the difference is between what you did and what I
tested.

I tried a combo with 22 columns and about 10,000 rows, so I doubt it's the
quantity of data that's different. It was a local table (not linked.) It's
possible that it could be a timing issue, I suppose (i.e. your code is
running before Access has populated the columns), but I did not experience
that, and I doubt this is likely as you are responding to a user's actions
(AfterUpdate.)

I do have NameAutoCorrect turned off. It was an Access 2000 format MDB
(trying to emulate what you have.)

Don't know what else to suggest.

 

[Terry]

Hi Allen,

The curious thing about it all is Me.ListboxName returns the value in the
bound column with intellisense, however trying to get a value from the
..Column(0) gets a Null. The listbox is filtered by the contents of a TextBox
on the form and at max will only get to return 20 rows from a table of
approx 9000. I guess I will re-insert a new one in the hope that Access2007
will get it sorted. Thanks for the help.

Regards