How to Sort 2nd Column in a Lookup Field

Topic

New Reply

The screenshot shows the Publisher column is supposed to be sorted by Publisher by means of:
Private Sub Lbl_Publisher_Click()
Me.OrderBy = “Publisher, BookTitle”
Me.OrderByOn = True
End Sub

The screenshot shows the Publisher column is actually sorted by PublisherID (autonum). That explains why Apress is placed last.

Question:
How to sort the Publisher column by the Publisher, not PublisherID?
Publisher field is a Lookup field. It consists of 2 columns–PublisherID and Publisher. The 1st field, namely, PublisherID, is hidden.

Armstrong

Reply | Quote

Replies

The screenshot shows the Publisher column is supposed to be sorted by Publisher by means of:
Private Sub Lbl_Publisher_Click()
Me.OrderBy = “Publisher, BookTitle”
Me.OrderByOn = True
End Sub

The screenshot shows the Publisher column is actually sorted by PublisherID (autonum). That explains why Apress is placed last.

Question:
How to sort the Publisher column by the Publisher, not PublisherID?
Publisher field is a Lookup field. It consists of 2 columns–PublisherID and Publisher. The 1st field, namely, PublisherID, is hidden.

Armstrong

It sounds like the Books table has a field Publisher (number), getting values from PublisherID field of the Lookup table.
You need to add the Publishers table to the Record Source of the form , then sort by tblPublishers.Publisher.
Make sure the join in the query shows All books and those records from Publisher where the join fields are equal”, otherwise books without a publisher will drop off the list.

Reply | Quote

Hi John,

Thanks for your help. All your assumptions are correct as usual.
“You need to add the Publishers table to the Record Source of the form…” that would the most interesting statement to me.
How to add? Do you mean by “joining” the tblBook and tblPublisher by query?
Is it true that most forms/reports are based on queries, not tables?
Now I realize that queries are more versatile than tables.

Armstrong

Reply | Quote

You should indeed create a query based on tblBook and tblPublisher, with a join on the PublisherID field.
Sort the query on the Publisher field from tblPublisher.
Save this query, and use it as the record source for the form.

It is not possible to say whether “most forms/reports are based on queries, not tables”. For many forms and reports, a table suffices as record source, while for others you need a query. It depends on the requirements.

Reply | Quote

Is it true that most forms/reports are based on queries, not tables?
Now I realize that queries are more versatile than tables.

I find that most (but not all) forms and reports are indeed based on queries rather than tables.
Learning about the power and versatility of queries is, I think, one of the big steps forward in learning about Access.

But the query does not need to be a saved query!

If you have a table as the Recordsource of a form, and you click the three dots to the right of the table name in the properties box you get prompted to build a query.

You are then taken off to the query builder screen. When you have finished, you close the query builder and return to the form. You are prompted to Save the changes. Say yes.
You then find that the Record Source is no longer the table, but an SQL statement. An SQL statement is a query in words, but you won’t find this query in the list of queries. It is just stored here in the record source of the form.

PS Because of a bug in Access 2007 you should always tab out of the Record Source field of the property box after changing the SQL statement. In the past the changes you made were lost if you did not do this. The latest Service Pack is supposed to have fixed this, but I am not convinced that it has completely.

Reply | Quote

Hi John,

Thanks for your showing me a new approach. Saved Query is new to me.
You tell me where to start (…). You tell me the pitfall as well.
Thanks a lot.
Armstrong

Reply | Quote

Hi John,
Thanks for your solution, it now sorts as expected.
Is it possible to place all empty Publishers (PublisherID is null) to the end, instead of the beginning?
Armstrong

Reply | Quote

You can add another column to the query to change the sort order:

Reply | Quote

You can add another column to the query to change the sort order:

Hi HansV,
I’m always humbled by the sublime quality and thoroughness of your technical reply.
This time you even create a sample database to illustrate the solution.
Thanks a lot.
Armstrong

Reply | Quote