Combo box NOT giving correct information!!! HELP

[apna]

I have a 4 column combo box. The row/source is

SELECT DISTINCT Evals.WorkshopTitle, Evals.Trainer, Evals.EvalDate,
Evals.Location FROM Evals;

When I click on my drop down arrow, I end up with 4 choices. Like this:
SRO Harris 2/20/05 CJI
SRO Harris 2/21/05 CJI
School Harris 2/19/05 Altel
Drugs Harris 2/15/05 CJI

If I select the first, third, or fourth items from the dropdown, every
things works great.
If I select the second item, I end up with the information about the first
item. I use the following to get what was selected in the combo box.

[text53]=combo50.column(0)
[text55]=combo50.column(1)
[text57]=combo50.column(2)
[text59]=combo50.column(3)

When selecting the second item in the dropdown, I should see the "date"
change BUT it doesn't. The Bound column is column 1 but still if the
information is correct when you click on the down arrow, why doesn't it
correctly update the [textxx] fields?

How do I fix this?

Thanks!

[Dirk Goldgar]

"apna" <ap...@etaleem.com> wrote in message
news:eQxt4$pGFHA...@TK2MSFTNGP14.phx.gbl

I heard about this phenomenon just recently. Obviously, because the
bound column is not unique in the combo's rowsource, Access is getting
confused about which row you actually selected. The safest solution is
probably to create a column that *is* unique, probably by defining a
calculated field in the query that concatenates several fields from the
table, and let that calculated column be the bound column. However,
I've read that you can probably make the combo box work as it is by
turning off the Allow Autocorrect option on the Other tab of the combo
box's property sheet.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

[apna]

Thanks for the info. At least I know it isn't me. I tried the Allow
Autocorrect but it didn't make any difference. So I guess I'll have to go
find/make something unique like you said.

"Dirk Goldgar" <d...@NOdataSPAMgnostics.com> wrote in message
news:Ox2x9GqG...@TK2MSFTNGP12.phx.gbl...

[Mark]

This is why I *almost always* add an autonumber field (usually called
RecordID) to every table and make it the primary key. So if I have two
records that are very similar, there is no chance of Access pulling the
wrong record. I set the bound column of the combo box to the autonumber
field (you can set the column width of the combo box to 0 for that field so
it doesn't show. Now when Access tries to find the record, it will search
the autonumber field and pull the correct record every time. So, your query
would look like this:
SELECT DISTINCT Evals.RecordID, Evals.WorkshopTitle, Evals.Trainer,
Evals.EvalDate, Evals.Location FROM Evals;

and your combo box would be bound to RecordID and the column widths would be
something like:
0";1",1",1";1"

"apna" <ap...@etaleem.com> wrote in message

news:%23uz$DMqGFH...@TK2MSFTNGP09.phx.gbl...

[Dirk Goldgar]

"Mark" <no_email> wrote in message
news:uBkJXGrG...@TK2MSFTNGP10.phx.gbl

> This is why I *almost always* add an autonumber field (usually called
> RecordID) to every table and make it the primary key. So if I have
> two records that are very similar, there is no chance of Access
> pulling the wrong record. I set the bound column of the combo box to
> the autonumber field (you can set the column width of the combo box
> to 0 for that field so it doesn't show. Now when Access tries to
> find the record, it will search the autonumber field and pull the
> correct record every time. So, your query would look like this:
> SELECT DISTINCT Evals.RecordID, Evals.WorkshopTitle, Evals.Trainer,
> Evals.EvalDate, Evals.Location FROM Evals;
>
> and your combo box would be bound to RecordID and the column widths
> would be something like:
> 0";1",1",1";1"

Note: there's no point in using the DISTINCT keyword in a query that
includes the primary key of the source table. I imagine that, if apna
is using the DISTINCT keyword in the rowsource query, the source table
has multiple occurrences of the field combinations being queried, and
its necessary to return only one occurrence of each combination. At
worst, with such a query, the combination of all selected fields will be
unique.