Combo box selection results

[lenny9]

I have a table of lab tests with fields of [ID], [Test], [TestAcronym]. As people often refer to the tests by their acronyms, I wanted to include the acronyms mingled alphabetically with the full names to allow selection of either in a combo box. I did this with a union query. This created a list with each record represented twice. The problem comes after a test has been selected. No matter what has been seleted, what shows up in the combo box is whatever term (Test or TestAcronym) is first in the list (ie: first alphabetically). What I want to show up is Test whether it or its acronym was selected and no matter which is listed first alphabetically. Any thoughts?

 

[Bry12345]

I'm not quite following what you mean by "shows up".

When the combo box opens, your are presented with data (probably one column?) from your query. This data should be sorted in the combo box in whatever manner you specified in your query. Good so far?

Now when you select one of those rows in the combo box, are you saying that value doesn't "stick" and that no matter what row you select, the combo box "resets" itself to the first row of the query??? - - - -

Bry

 

[WildHare]

What you probably want to do rather than a union query is a query that pulls both test and test acronym. Decide which one you really want to sort by, and sort by it. Then make sure BOTH columns are visible, but set the BOUND column to TEST. There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[tunsarod]

Here's a solution that works, sort of ...

Create a Union Query using:


SELECT tblTests.ID, tblTestTest, tblTests.Test As TestTitle
FROM TblTests
UNION SELECT tblTests.ID, tblTests.Test, tblTests.TestAcronym As TestTitle
FROM tblTests
ODRER BY TestTitle;


Set your combobox:
RowSource = Queryname
Columns = 3
Bound Column = 1
Column Widths = 0cms; 0cms; 2.5cms

Reduce the Combobox horizontally until all you can see is the dropdown button.

Add a textbox to the form and set its ControlSource to = MyCombobox.Column(1).

Now you have a Combobox which always refers to the TestID
value, offers a selection list of Test names and acronyms combined and has the appearance of displaying Test after selection.

Regards
Rod