Combo box issues

[Sue]

I have designed some forms that use a combo box to pull from a table. The
table has a list of ~ 50 choices, and despite alphebetizing the source table,
I cannot get the list in the combo box to populate alphabetically. Even if I
blow up the forms & start from scratch, I can't get the full list to display
in alphabetical order.

What am I doing wrong?

Thanks!

 

[Mark]

Make the Rowsource of the combobox a query and sort alphabetically on the
appropriate field.

Steve

 

[Sue]

I tried that but wound up with a different issue this time. But first, If I
try to develop queries for alphabetizing all the combo boxes I may design
over time, it seems to me that I'll be loading up the database with objects
that would more logically be handled without this additional step. But maybe
I'm slow....

At any rate, when I designed the query you suggested, rather than the
alphabetized text then showing in my combo box, I see the primary key (not
the text) in the combo box. In the query I've included the text I want to
display & sorted it alphabetically, and included the primary key but
unchecked the "show" box before saving. The query then shows what I want to
see, but when I insert the combo box into my field, I see the primary key
displayed. If I go back to the underlying query you suggested, I no longer
see the primary key field when I look into design view.

What the heck???

 

[Sue]

Mark - I've also tried NOT including the primary field in the underlying
query at all (rather than simply not showing it) but still run into problems.

After selecting the query as source using the wizard & choosing the field(s)
to display, the next this the wizard to is to say that I can either store a
value from that row in the database or use the value later to perform an
action... at which point I'm asked to choose a field that uniquely
identifies the row. I choose the text I've alphabetized (which IS unique),
then am asked to select either "remember the value for later use" or "store
the value in this field". If I store the value as a field, I see the primary
key rather than the text. If I remember the value for later use, I see
nothing at all.

Waah.

Help, please? I clearly don't understand.

The SQL for the query is:

SELECT tblDiagnosticCodes.Diagnosis, tblDiagnosticCodes.ICD9Code
FROM tblDiagnosticCodes
ORDER BY tblDiagnosticCodes.Diagnosis;

 

[Mark]

Sue,

You're very close!! Do the following:
1. Change your query to put ICD9Code in the first field and Diagnosis in
the second field. Leave the sort for Diagnosis as is.
2. Open your form that contains the combobox and select the combobox. Open
Properties.
3. Go to the Data tab:
a) Be sure the Rowsource says the name of your query.
b) Set the Bound Column property to 1.
4. Go to the Format tab:
a) Set Colimn Count to 2
b) Set Column Width to 0;2
** The "2" above may need to be smaller or larger depending on the
width of your combobox. This value is the width of Diagnosis you want to
display in the combobox.
5. Close Properties and close your form saving the changes.

Now open your form and try the combobox.

NOTE: The value of your PK (ICD9Code) and not Diagnosis will be saved in
the table which is the recordsource of your form. This is correct for a
combobox!

Steve

 

[Sue]

Marvellous!
Thank you so much!