Wrong column displayed in combo

  • Thread starter Thread starter Jonathan Blitz
  • Start date Start date
J

Jonathan Blitz

I have a combo box which is defined as based on a table.
This works ok. It displays the text column and saves the index column as I
require.

However, in my VBA code I replace the rowsource with an SQL statement. When
I do this it displays the index column after a value is selected instead of
displaying the text column.

Funny thing is that I have another version of the same form which does work.
The only change I made was to the SQL statement in the VBA code.

The change was to make the statement a join.

I though that maybe the join was confusing it so I changed the SQL statement
to an extra level so that the top level is selecting from one table.

The other point is that the new SQL statement displays more than one column
whereas the previous one only displayed one.

So, what do I do to get it to display the correct column?

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
Do you have multiple columns AND columnwidths specificed on the problem
control? By default, a combo only "exposed" a single column. For example, if
you wish to have 3 columns in the rowsource of your combo and expose the 2nd
and 3rd ONLY, then set you parameters to this:

..ColumnCount = 3
..ColumnWidths = 0;1;1

This would hide the first column (which is likely the Bound column, unless
you've changed that) and expose a multi-column combo showing the 2nd and 3rd
columns.
 
Seems I didn't explain correctly.

When the drop-down appears all is ok. But when a value is selected it shows
the key value instead of the string.

Example:
Column1 Key
Column2 Char
Column3 char

When I display the drop down then columns 2 and 3 are displayed (as I want).

After selecting the value the combo box shows the value in column1 instead
of that in column2.
This only happens after I change the row source via VBA. When I originally
define the combo box it does show the second column after selection.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
Hi Jonathan,

Thanks for your post. According to your description, I understand that the
hidden column displayed when you changed the row source via VBA. If I have
misunderstood, please feel free to let me know.

Based on my research, you need to set the "Column Widths" property to hide
the first column when you set the row source via VBA. In this case, you can
set the Column Widths as (0";1";1") so that the first column will be hidden.

Please reply directly to the thread with any updates. Please be aware that
you may receive this email notification before you able to view my reply in
the newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Hi Jonathan,

If you want to specify the returned value of the combo box control, you can
use the Column property to refer to a specific column. For example,

The combo box will return the value of column2 using the following codes.

Me.<combo box name>.column(1)

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
The problem is that the data is displayed ok.
I get all the columns displayed - as I want.

However, after a value is selected I don't want the key value to be
displayed in the combo. I need another field.
There is no problem when it is selected from a table.

It seems that if I select only one "data" column from the table to be
displayed in the combo then when I select a value all works ok.
The key value is saved and the text value is displayed to the user.

However, when I have more than one data field then the key field is
displayed to the user.
How can I force it to display a certain field to the user?
--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
Hi Jonathan,

Thanks for your feedback. As I understand, the column displayed in the
combo box is not what you want after a value is selected. If I have
misunderstood, please feel free to let me know.

Based on my research, the first column used in the select statements as Row
Source will be displayed in the combo box. If the key column is set as
hidden and as the first column in the select SQL statements, the second
column used in the SQL statements will be displayed. Does it meet your
requirements?

If not, can you please post the related sql statements and detailed
reproduce steps so that I can fully understand you r concerns and perform
further research on my side?

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Back
Top