Combo Box Sorting and Selecting

[Secret Squirrel]

I have a combo box that has a row source based on a query. The query has my
PKid, partdescription, and partno. I have it sorted by the partdescription
because the users want to be able to type in the description and have it jump
to that section of part numbers. I have the combo box bound to the partno but
when they select a part it shows the partdescription in the combo box but in
the table it stores the partno. Is there a way to have it show the partno in
the combo box instead of the partdescription? I can't figure out how to do
that...if it's possible. They key is I need to have the combo box sorted by
the partdescription so the users can jump to a partdescription to be able to
select a partno.

 

[Arvin Meyer MVP]

Sure. Set the PartNo column to be the first visible column, or if they are
selecting by description, just add a textbox which displays the PartNo from
the combo. You can do that by setting the controlsource like:

=Forms!FormName!ComboName.Column(0)

That is the first column.

 

[KARL DEWEY]

Open in design view, double click and select properties.
Scroll to Colomn Widths. It should be showing 0"; 1.5"; 0" or some such
listing. Change to 0"; 0"; 1.5" and check. The numbers a widths to be
displayed for the columns.
Set the width as needed to display you partno.

 

[Secret Squirrel]

I currently have the column widths set like this:

0";2";1.5"

It shows the description and partno but the combo box is sorted by the
partdescription to make it easier for the users. But once they select a part
it shows the description in the combo box but stores the partno in the table.
I want to use the same set up but have it show the part no in the combo box
instead.

 

[Jeanette Cunningham]

The combo will display the contents for the first non-zero width column when
it is not dropped down.
If you wish it to show the part no, make the part no the first column after
the hidden column.
This will interfere with sorting by description , so you may like to try a
work around.

Workaround-->
This is the general idea for this type of work around. I have not tested it
for this particular case.
Leave the columns as you have them.
Put a text box over the combo, just narrow enough to leave the drop down
arrow visible.
Make the textbox bound to the part no field.
Remove the tab stop from the combo.
Put code in the textbox to setfocus to the combo on its got focus or enter
event.

When the user gets to the textbox in front of the combo, the combo will drop
down and user can choose from the combo using description. Once user tabs
out of the combo, the textbox slips in front and displays the part no.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia