Combo box NOT giving correct information!!! HELP

  • Thread starter Thread starter apna
  • Start date Start date
A

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!
 
apna said:
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!
Click to expand...

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.
 
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 said:
apna said:
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!
Click to expand...

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)
Click to expand...
 
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 said:
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 said:
apna said:
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!
Click to expand...

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)
Click to expand...
Click to expand...
 
Mark said:
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"
Click to expand...

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.
 
Back
Top