Combo Box and ANSI92 Compatibility

P

Peter J. Veger

Access 2003
Selecting (Tool > Options > Tables/Queries) ANSI 92 Compatibility for this
database influences the behaviour of combo boxes.

The behaviour I expect is:
- if I start typing, there will be look-ahead;
- if I first open the box by clicking the down-pointing arrow, and then
start typing, the first entry corresponding to my typing prefix will be
shown.

This indeed happens when ANSI92 compatibility is off.
But not longer so with ANSI92 compatibility selected.

Is this a bug or is this by purpose (and then: why)?

Peter J. Veger, Best Netherlands
 
P

Pete

This is a bug. The workaround is to add the word DISTINCT to your queries,
i.e. SELECT DISTINCT etc
 
P

Peter J. Veger

Thank you, adding DISTINCT is possible in most (?) cases without changing
the meaning.
But in other cases it is apparently not allowed, e.g. I get an error
message: ORDER BY clause conflicts with DISTINCT
(and, after a slight, semantically-equivalent change the Form changes to
read-only)

So ANSI 92 is not possible
 
P

Pete

I found the knowledge base article no that describes the problem - KB824189

The bound column of the combo must contain a unique value otherwise you get
the error message you describe. I have just rewritten a big system in ANSI 92
syntax with a view to moving the back-end to SQL Server Express 2005 and have
yet to come against a scenario where this is a problem. It may be that I have
made sure that every table has an autonumber primary key (even if it is just
a lookup table) as this enables SQL Server to synchronise with Access more
easily.

Perhaps you can give me more details if you still have this problem.
 
P

Peter J. Veger

In general this indeed is not a problem, but in my case it is complete
overkill.
It involves a db (around 25 tables) for a sailing club with a few hundred
members.
The database maintains the history of the club: membership and
boat-ownership, so no row in Persons (around 1000 rows now) and in Boats
(400 now) is ever deleted.
To maintain the history of historically/structurally less relevant info
(e.g. person telephone number or boat has-radar) I have a table that logs
events.
Those events have a date, the ID of the person involved, the ID of the boat
involved, an event type, and then info (and the previous info).
It is just a log, used for two purposes, history, and as source for the club
magazine.
There is no need for "distinctness", no need for indexing: and yes, each
record is distinct but only if you use all fields together as key.
The form used to view the log needs a query in its record source, to get the
names of the persons and the boats, and to order the record set by date,
person, event type.
And here is the conflict: (potential) non-distinctness and order.
 
F

fec

Peter J. Veger said:
In general this indeed is not a problem, but in my case it is complete
overkill.
It involves a db (around 25 tables) for a sailing club with a few hundred
members.
The database maintains the history of the club: membership and
boat-ownership, so no row in Persons (around 1000 rows now) and in Boats
(400 now) is ever deleted.
To maintain the history of historically/structurally less relevant info
(e.g. person telephone number or boat has-radar) I have a table that logs
events.
Those events have a date, the ID of the person involved, the ID of the boat
involved, an event type, and then info (and the previous info).
It is just a log, used for two purposes, history, and as source for the club
magazine.
There is no need for "distinctness", no need for indexing: and yes, each
record is distinct but only if you use all fields together as key.
The form used to view the log needs a query in its record source, to get the
names of the persons and the boats, and to order the record set by date,
person, event type.
And here is the conflict: (potential) non-distinctness and order.
Click to expand...
 
P

Pete

This begs the question then, why have you got ANSI 92 compatibility turned
on? A Jet back-end will cope with this easily.

I would also say, that irrespecitve of the size of the database adding a
numerical primary key can considerably reduce the size and improve
performance - on the system I have just rewritten the back-end has gone from
50Mb to 20Mb and all I have really done is add primary keys to all tables and
use these to link tables.

With your combo boxes you can always have your primary key field as a hidden
(0 width column) so the user still sees the name or the boat, but the key
value is the one that is actually stored.
 
Top