ComboBox

BillBee

Registered User.
Local time
Today, 16:20
Joined
Aug 31, 2008
Messages
137
I have a Table with 6 fields, 2 are named Initial and Surname. Is it at all possible to have a combo box on a form with these to fields "joined" with the alphabetical order on the Surname. I understand that using a query will not work as the result is based on an expression. Thanks for any advise.
 
Have a rowsource in your combobox something like

SELECT ID, Surname & ", " & Initial FROM myTable ORDER BY Surname, Initial
 
I probably did not explain enough. There are two tables. One contains a list of sections. The other contains the list of names. The record source for the Form is on the Sections and the record source for the combo box is from the second table Names. I can produce a list in the Combo box Surname, Initial with order on the surname. I cannot reverse these Initial surname as the order them is placed on the initial. If it is possible I would like to have initial, surname with the order on the surname. I have attached a file to view data.
 

Attachments

This is your FE. There are no tables, just links to tables.
 
What is FE?. Can I link my combo box to the tblJudge on this Form?
 
OK try this

SELECT ID, Initial & ", " & Surname FROM myTable ORDER BY Surname
 
There are no tables in the file you posted. There are links only. FE is Front End.
 
Have attached updated tables to file. Can display judge list correctly in Combo Box but when I click on a name all sections get the same name. I want to place a single name in each row except it may be the case that the Yorkshire Champion and Yorkshire Novice (and other Sections) may have the same Judge. :banghead:
 

Attachments

You should link the judge by ID. You should not store the judges name in the sections table, just the unique ID of the selected judge.

The combo should have a rowsource like . . .
Code:
SELECT JudgeID, JudgeName FROM tJudge ORDER BY JudgeName;
with a BoundColumn of 1, ColumnCount of 2, ColumnWidths like 0";2" (so the ID is hidden and the name is visible). Then when you make a selection in the combo, the ID is stored in the sections table, which is how you do the link.

Hope this helps,
 
I have attached copies of the 2 Tables and Forms from my original file. I have set this up in the Table with one field JudgeName. In this case the drop down list shows the Name in alphabetical order on the Initial. In the previous attachment I changed JudgeName into two fields JudgeInitial and JudgeSurname trying to Display the names in the drop down list with the Alphabetical order on the surname. The names displayed correctly but would not remain on the Form when selected. I tried to follow your instructions but could not get things to work any better. Maybe I should stick with the original method.
 

Attachments

Here is the approach I would take. First and last names should be stored separately if you want to sort on last name. Links are made by ID so that names are not stored multiple times.
 

Attachments

That is great. Far beyond my knowledge. Thanks very much
 

Users who are viewing this thread

Back
Top Bottom