Have a Form open records in a certain order

[pbrown2]

When the form opens it shows the records according to the order they were entered. However, I need them to show in order as to the date in the field [Date] then next ordered sequencially as to if the record was:
Breakfast
Lunch
Dinner

Example (I know the dates are wrong):
If the user enters the following:
2/24/04 Sunday
Dinner

2/24/04 Sunday
Lunch

2/23/04 Saturday
Breakfast

The next time the form is opened to allow more entries it should be ordered like:
1)
2/23/04 Saturday
Breakfast

2)
2/24/04 Sunday
Lunch

3)
2/24/04 Sunday
Dinner

4)
Would be a NEW RECORD

Basically, no matter in what order the information is entered, when the form is next used, the records will automatically sort first by date then by Breakfast / Lunch / Dinner.



Thank you for any and all help,

PBrown

 

[randy700]

Open the forms property box and put it on the Order By line.

i.e. Order By [Date],Meal

 

[pbrown2]

[Date] in the order by does not order the records by date. I still have 2/24/04 and 2/25/04 being mixed together...... Any other ideas?

Thank you for any and all help,

PBrown

 

[cheerio]

Is your field [Date] actually a DateTime field or is it text.

Also it's never a good idea to name fields with function names such as Date. Although Access may understand you if you remember use the [] it's more difficult to read. You could have called your field EntryDate say.

 

[randy700]

You could try basing your form on a query of the table and perform the sorting in the query.

SELECT * FROM tblTable ORDER BY DateEntry,Meal;

 

[pbrown2]

The only worry about basing the form via a query is that the form has a subform. Will that affect it in anyway?

Thank you for any and all help,

PBrown

 

[randy700]

Basing the main form on a query rather than a table should not affect the subform.

 

[DarrenBoyer]

Just in case anyone else is searching for this answer here is a response that I found that works. The other two mentioned in this post did not help:

A sub form is just a form like any other, so to order the records, you put an order by in the query on which the form is based, or if at present it is based directly on a table, you click the builder button to the right of the recordsource property and make a query with an appropriate order by

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

Thanks Ken