Multi-Column List Box

[Guest]

I have created a multi-colum list box from a query, it is surname, firstname,
position, telephone number etc. It is set to acending on the surname
However we have a number of clarkes in the list and when i pick the second
or third clarke, it defaults back to the first clarke in the alphabetical
list.
Is there a way i can make it stick with the one i have chosen?

Many Thanks

 

[Guest]

Hi Cathy

Sort the query not the list box.
Sort by Surname in the query then the first name and any other that you
want. A tip is to move the columns in the query that want to sort bu in
order of preference to the left of the grid.
PrimaryField (not sorted) - Surname (Sorted) - 1stName (Sorted) - possition
- etc

 

[Guest]

Many Thanks for replying Wayne

I have tried what you suggested, also sorting the first name, my columns
were also in order of pref already. However it still wants to pick up the
first (for example) clarke in the alphabetical list. It lets me pick the one
i want then when i go back to the record it has changed back.

Thinking of another list box that that is in the database (someone else
designed that bit so i'm not sure why it doesnt work) where there is a list
box of just surnames and when you pick one, it populates the fields in the
form below with all the details. However this does the same when i try to
pick Clarke Simon it picks up the first one Clarke Emma.

Is this just the way it goes with this sort of thing and i am asking too much?

Appreciate your help if you can
Cathy

 

[Guest]

If the table of people has a unique primary key column include that in the
query's RowSource as the first column. If it doesn’t then give the table an
autonumber column as its primary key, e.g EmployeeID (never use names as
keys, they can be duplicated). The RowSource property of the query would
then be something like:

SELECT EmployeeID, Firstname, Surname,
Position, PhoneNumber
FROM Employees
ORDER BY Surname, Firstname;

Set the list box's ColumnCount property to the number of columns in the
query, i.e. 6 with the above example, and set its ColumnWidths property so
the first dimension is zero, e.g. 0cm;3cm;3cm;3cm;3cm;3cm to hide the first
column. Set the BoundColumn property to 1.

When you select an item from the list the value of the list box will be the
hidden EmployeeID, so if you are using the value as the basis for opening a
form or report etc the use the EmoloyeeIOD as the criterion, not the name.

Ken Sheridan
Stafford, England