Bizarre Access 2003 bug - Please help

[Gilad]

Hey,
I'd appreciate help figuring out this *very* strange Access 2003 bug that is
easily reproducible in 3 minutes. I've searched everywhere online and haven't
seen any mention of this problem.

To reproduce and see what I'm talking about:
1) Start a new blank database
2) Create a table in design view
3) Create one row titled "UserID" of data type "text"
4) Create a second row titled "Car color" of data type "text"
- under the lookup tab, change to "combo box"
- set "row source type" = "value list"
- set "row source" = "black";"orange";"green";
- set bound column = 0
5) close the table and let access create an automatic "autonumber" primary
key.
6) Open the table and put in three random rows of data.
7) Choose "Filter by form"
8) search for * in the userid field.---> Apply filter.


Voila! Bizarre giberish characters appear in the "car color" field. Also, it
becomes impossible to choose any value. If you do and exit, there is also
this weird "can't find macro" error with random chinese characters.
Note that there is no trace of this bug until a filter is applied.

I appreciate ANY ideas and help.

 

[Allen Browne]

The BoundColumn property is not zero based.

Setting the Bound Column to 0 is invalid, and is the probable cause of this
issue. Change it to 1, and select the values again in all records.

Perhaps Access should not allow the zero setting for Bound Column. The
stored value seems to be the numeric index of the list, as text.

 

[RoyVidar]

Perhaps Access should not allow the zero setting for Bound Column.
The stored value seems to be the numeric index of the list, as text.

Yes, that's exactly what the help file says is the effect of setting
the
bound column to 0. You deal with the listindex, in stead of the value
of
any of the columns ;-)

For those who dare venture into the land of composite keys when working
with combos or lists, setting the bound column to 0 is interesting.

 

[g.evrony]

Hey,
Yup, the whole reason I'm using boundcolumn=0 in the first place is to
allow for sorting according to the row index in select queries.
Boundcolumn=0 is a feature of Access that is even explained in the help
file, and many people have used it specifically for sorting text values
not according to alphabetical order but rather according to their
position in the list.
I still don't understand why this bug only happens after filtering. If
I don't filter, everything is fine, including reports, SQL queries,
record updates, etc.
Thanks for any help,
Gilad

 

[Allen Browne]

Access 2002 introduced the ability to sort or filter a form on a field that
is not in the form's RecordSource, if it is in the RowSource of one of the
combos.

It's a horrid concept that actually breaks a heap of things, such as where
the RowSource already uses multiple tables and another one wrecks the joins
or results in a non-updatable form, not to mention that code that applies
the form's filter to a report is broken.

Without testing, I suspect that your issue is another case of this
half-baked idea that is best avoided IMHO.

If your combo's RowSourceType is Table/Query, surely you could add a sorting
field to the lookup table, and set the RowSource to ORDER BY that field. If
it is a ValueList, you could add a 2nd column to the combo (zero width) if
that helps.

Either way, the idea of referring to the index of the combo like that is
going to give you problems in Access 2002 and 2003 that may not have been
there in previous versions.

That's my limited understanding of it anyway. If anyone else knows better,
we'd love to hear.