Hi,

Can anyone suggest what to do here...

I have created a database for a radio station so they can look up any song title and find out which CD it is on. Part of this is a form for entering details of the CD. Some of the fields in the form are:

Title - the album's name, text field.
Year - year of release, number field.
Artist - performer's name, Combo box menu, based on a query album_artist. The query contains a hidden autonumber field (artistID) and a text field (artist). Columns in the Combo Box properties are set to 0cm;2.54cm.

The station want the form to display in the following order: artist, year, title. So that it's sorted first, by the artist name, then the year of release, then (if that artist released 2 in the same year) by the recording title. It should look like this when I enter 'artist, year, title' in the Order By Dialogue box:

Artist Year Title

Adams, Bryan 1984 Reckless
Adams, Bryan 1991 Waking Up the Neighbours
Adams, Bryan 2002 Spirit: The Stallion of the Cimarron
Adams, Bryan 2002 The Early Years
Hootie & the Blowfish 1998 Musical Chairs
Hootie & the Blowfish 2003 Hootie & The Blowfish
INXS 1985 Listen Like Thieves
INXS 1987 Kick
INXS 1997 Elegantly Wasted
Madonna 1989 Like a Prayer
Madonna 1998 Ray of Light
Madonna 2000 Music
REM 1992 Automatic for the People
REM 2003 In Time: The Best of REM 1989-2003
REM 2004 Around the Sun

Etc... You get the idea.

But, the form always sorts by the hidden artistID field instead, and as the artists were not added in alphabetical order, it looks more like this (artistID is hidden, just showing you for reference):

Artist ID Artist Year Title

1 Madonna 1989 Like a Prayer
1 Madonna 1998 Ray of Light
1 Madonna 2000 Music
2 INXS 1985 Listen Like Thieves
2 INXS 1987 Kick
2 INXS 1997 Elegantly Wasted
3 Hootie & the Blowfish 1998 Musical Chairs
3 Hootie & the Blowfish 2003 Hootie & The Blowfish
4 REM 1992 Automatic for the People
4 REM 2003 In Time: The Best of REM 1989-2003
4 REM 2004 Around the Sun
5 Adams, Bryan 1984 Reckless
5 Adams, Bryan 1991 Waking Up the Neighbours
5 Adams, Bryan 2002 Spirit: The Stallion of the Cimarron
5 Adams, Bryan 2002 The Early Years

If I sort by just the artist field (not year or title), then that works correctly, but the year and title are then not sorted correctly. As soon as a I re-add year and title, it reverts to using artistID.

Any ideas? I'm stuck...

Make a query where your data is sorted in the way you choose, then make this query the data source for your form.

hth

Ok, the sorting should be done in the query that provides the row source for the combo box. The query will sort by the first column that you have sorting applied to, then the second and third etc... I have attached a screen shot to illustrate (Table, design, or names may not match what you have, I have compiled it only as a visual aid). From the screenshot, the ArtistID field will not be part of the sorting, only the fields where Ascending has been chosen as the sort order. It will sort first by Artist, then by year, then by title.

HTH

I think you misunderstand me...

The fields artist, year and title are in a form based on a table called albums

Year and title are simple entry fields.

Artist is a drop down

This combo box is based on a query called album_artist which is sorted ascending by artist name (and not artistID).



Edited by: energize on Tue Feb 1 6:53:32 EST 2005.

Can you clarify. Is your desired to sort on the combobox or the form.
If you need to sort by multiple fields on the form, create a query to request the required fields instead of 'hooking' directly into the table. Sort using thequery.
HTH
Jim

Sorry - I didn't make things clear...

The list in the combo box is in the order I want and it displays correctly...

What I want to do is sort the form into the correct order. The form is used primarily for data entry, but the users would like to be able to flick through the records in the form too, and view them in the staed order.

Is there anyway to do this without having make a different form?

Read my post, it tells you.
simply base the form on a query instead of on table.
HTH
Jim

WIll that allow for the data entry side of things though?

Yes. Remeber, the query will have EXACTLY the same fields as it currently does from the table.
Jim

Tried it - still sorts incorrectly as in original post. Any more ideas?

Got it - the 'order by' in the form properties had to be set as 'Lookup_artist.Artist, year, title'.

Thanks for trying anyway.

:-)

PC