Wrong result of listindex and column property when reading from combobox control

[Georg Schulte Althoff]

Dear NG,

I have a strange problem when reading values from my combobox control.
Sometimes the correct value is returned, but in some cases I get a wrong
result which is not corresponding to the selected data.


My form contains a combobox control (named cboGz) to select a
workcenter-group from a table. Column 1 is the bound column
The rowsource contains a simple SQL-statment:
SELECT GzId, Bez, StId FROM Gz ORDER BY GzId, StId;
The table is a linked table in an Oracle database.

When the user finishes seletion, I have to read the current values for GzId
and StId from the combobox:
vGzId = cboGz.Value ' bound column GzId
vStId = cboGz.Column(2)

sGzId is always filled with the correct value. But depending on the selected
row of data sStId is sometimes filled with data from another row or even
remains empty. The listindex property is then set to an unexpected value or
contains -1.


In my tests to find out what went wrong I checked three configurations
for my combobox control.

I know that the result of ORDER BY is different whether it is done by Access
or Oracle. So I created a local table in my Access database and a link to
the original table in Oracle. The primary key is (GzId, StId).
Table Gz_Access
Definition
GzId Text(30)
StId Text(30)
Bez Text(50)
Data (queried with no order)
GzId;StId;Bez
"1";"Aachen";""
"1";"Werk Dausenau";""
"APV-Gesamt";"Aachen";""
"APV-Gesamt";"Köln"
"APV-Gesamt";"Werk Dausenau";"Artikel-Prüf-Vorrichtung-Gesamt"
Table Gz_Oracle
Definition
GzId Varchar2(30)
StId Varchar2(30)
Bez Varchar2(50)
Data (queried with no order)
GzId;StId;Bez
"APV-Gesamt";"Werk Dausenau";"Artikel-Prüf-Vorrichtung-Gesamt"
"APV-Gesamt";"Aachen";""
"APV-Gesamt";"Köln"
"1";"Werk Dausenau";""
"1";"Aachen";""

I also created a pass-through-query to perform sorting in Oracle:
Definition:
SELECT * FROM Gz ORDER BY GzId, StId;
Data (queried with no order in access)
"APV-Gesamt";"Aachen";""
"APV-Gesamt";"Köln"
"APV-Gesamt";"Werk Dausenau";"Artikel-Prüf-Vorrichtung-Gesamt"
"1";"Aachen";""
"1";"Werk Dausenau";""

In a new form I created a combobox for each datasource:
cboGz_A_T
rowsource
"SELECT GzId, Bez, StId FROM Gz_Access ORDER BY GzId, StId;"
data in combobox
"1";"Aachen";""
"1";"Werk Dausenau";""
"APV-Gesamt";"Aachen";""
"APV-Gesamt";"Köln"
"APV-Gesamt";"Werk Dausenau";"Artikel-Prüf-Vorrichtung-Gesamt"
cboGz_O_T
rowsource
"SELECT GzId, Bez, StId FROM Gz_Oracle ORDER BY GzId, StId;"
data in combobox
"APV-Gesamt";"Aachen";""
"APV-Gesamt";"Köln"
"APV-Gesamt";"Werk Dausenau";"Artikel-Prüf-Vorrichtung-Gesamt"
"1";"Aachen";""
"1";"Werk Dausenau";""
cboGz_O_Q
rowsource
"SELECT GzId, Bez, StId FROM Gz_Query;"
data in combobox
"APV-Gesamt";"Aachen";""
"APV-Gesamt";"Köln"
"APV-Gesamt";"Werk Dausenau";"Artikel-Prüf-Vorrichtung-Gesamt"
"1";"Aachen";""
"1";"Werk Dausenau";""

Here are the results when selecting a row and reading some properties
(the values column(0) / column(2) / listindex are read from the control
from left to right)
cboGz_A_T
1/Aachen 0 / 1 / Aachen < correct
1/Werk Dausenau 0 / 1 / Aachen
APV-Gesamt/Aachen 2 / APV-Gesamt / Aachen < correct
APV-Gesamt/Köln 2 / APV-Gesamt / Aachen
APV-Gesamt/Werk Dausenau 2 / APV-Gesamt / Aachen
cboGz_O_T
1/Aachen -1 / /
1/Werk Dausenau -1 / /
APV-Gesamt/Aachen 0 / APV-Gesamt / Aachen < correct
APV-Gesamt/Köln 0 / APV-Gesamt / Aachen
APV-Gesamt/Werk Dausenau 0 / APV-Gesamt / Aachen
cboGz_O_Q
1/Aachen 3 / 1 / Aachen < correct
1/Werk Dausenau 3 / 1 / Aachen
APV-Gesamt/Aachen 0 / APV-Gesamt / Aachen < correct
APV-Gesamt/Köln 0 / APV-Gesamt / Aachen
APV-Gesamt/Werk Dausenau 0 / APV-Gesamt / Aachen


I suppose those many wrong results depend on the internal implementation
of the combobox control and its interaction with the data source.

I need to know why
1. the listindex is set to -1 if the datasource is a linked table.
2. only the first value of StId could be selected.

Any hints about the behaviour of the combobox control is welcome.
Is there an other control which is able to solve this selection?


At least some information about my environment:
OS: WinNT 4 SP6a or WinXP (German)
Access 2002 (10.4302.4219) SP-2 (German)
ODBC: Oracle 9.02.00.06
Oracle server: 9.2.0.1.0 (language AMERICAN, territory AMERICA)


Thanks

Georg Schulte Althoff

 

[Michael, Cheng [MSFT]]

Hi Georg,

From your description, I understand that you would like to know why your
listindex will be set to -1 and some data from for your combo box is not
correct.

Would you please show me how do you add those columns? Detailed information
that you can provide will make things clear and help us move closer to the
causes and resolutions.

Also, the following documents may be helpful to you in combobox control
ACC2000: Four Ways to Move to a Record from a Combo Box Selection
http://support.microsoft.com/default.aspx?scid=kb;en-us;209537

ACC: How to Move to the Next Item in a Combo Box with DblClick
http://support.microsoft.com/default.aspx?scid=kb;en-us;184034

Programming Combo Box and List Box Controls in Microsoft Access
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/h
tml/office03022000.asp

ACC2000: List Box Column Property Receives Incorrect Row Information
http://support.microsoft.com/?id=209146

Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are here to be of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

 

[Georg Schulte Althoff]

Hello Michael,

From your description, I understand that you would like to know why your
listindex will be set to -1 and some data from for your combo box is not
correct. ....
Also, the following documents may be helpful to you in combobox control ....
ACC2000: List Box Column Property Receives Incorrect Row Information
http://support.microsoft.com/?id=209146

I think I found the information I needed in the last article you pointed me
to.
After I had posted my question I mad some more tests with unique values
in the bound column which worked as I expected. So I guessed it had to do
something with the nonunique values depending on the internal implementation
of the combobox control.

Thank you for this hint.

Georg Schulte Althoff