Sorting records alphabetically by subform

  • Thread starter Thread starter James R.
  • Start date Start date
J

James R.

I have a database of journal articles in ACCESS. The
form for each article/record includes a subform listing
the author(s) of the article. I would like to sort the
articles alphabetically according to the name of the
first author (in the subform). How do I do this?
 
Open up the recordsorce for the subform. Select the field
you want sorted and under sorting click ascending. If your
record source looks like this Authors.* then add again the
author name but deselect the check box so that it doesn't
show up twice on the form.
 
Naturally enough, you cannot sort the main form on something that is not in
its RecordSource. However, you can change the RecordSource of the main form
to a query that includes the first author from the subform.

The RecordSource of the main form would need to be a string such as this:
SELECT tblArticle.* FROM tblArticle
ORDER BY
( SELECT First(tblAuthor.Surname) FROM tblAuthor
WHERE tblAuthor.ArticleID = tblArticle.ArticleID
ORDER BY tblAuthor.Surname )
 
Back
Top