Allen Browne's sort order code posted 4/15/05

[Guest]

I have used Allen's sort code in my application and it works great...except
for one thing I can't figure out how to deal with.

Alot of my table fields are look-ups o other tables. For instance TableA has
EmployeeID, which has a relationship with TableEmployees, where the person's
actual first and last names are held. TableA only stores the EmployeeID.

In my form, if I use Allen's code, it sorts on the EmployeeID, not the
user's name. I've tried all sorts of work-arounds, like trying to tell it to
look at the field's column numer with the name, but it doesn't like that.

Suggestions?

 

[Allen Browne]

Hi Christine. I can't remember what I posted, but if you want to sort by a
form by the employee name and you only have the EmployeeID in the source
table, you could create a query to use as the source for your form.

The query will contain TableA and the Employee table, e.g.:
SELECT TableA.*
FROM TableA INNER JOIN Employee
ON TableA.EmployeeId = Employee.EmployeeId
ORDER BY Employee.,Surname, Employee.FirstName, TableA.ID;

You should find that this form is still updatable.

There is a problem you can run into if any of the fields in the Employee
table have a Default Value set. When you try to create a new record, Access
mistakenly applies the Default Value, and since you are not actually adding
a record to the Employee table, that fails and it gives a rather cryptic
error. Removing the Default Value from all fields in the lookup table solves
the problem.

 

[Guest]

Thank you for your responses, Allen. I have to leave work now, but will try
it on Tuesday after the bank holiday and let you know how I get on.

Cheers,
Christine