Sort records on an unbound form (2000/2007)

Topic

New Reply

I have a database in 2000 format being run with the backend on the server and the frontend on the desktop. They have recently upgraded to 2007 but the database remains in 2000 format.

One of the forms is very complicated and accesses a large recordset and in a previous post we came up with making the form unbound and setting recordsources on load. The item in question uses the following:
Me!frmDep.Form!frmISAPSubMeetings.Form.RecordSource = “tblISAPMeeting”

Last week, I got a call that records for meetings were no longer sorting chronologically. The order showed no logic I could grasp. With one record with 50+ meetings dating back 3 years, the first meeting displaying was the most recent (late June) then meeting #2 was the very first meeting in 2004 and and they proceeded in chronological order from there. There was an orderby in the table properties which I removed while the backend was still on the server to no avail. I eventually discovered that if I took the backend off the server, fixed it on a desktop and then put it back, it was OK.

2 Questions:
1) is there a way to set the order in VBA when the recordsource is set so that even if the table is flaky, the records will be in chronological order? I suppose I could use a query, but I’d rather avoid that if I can.
2) Any speculation as to why I could fix it on a desktop but not over the network? Has anyone else encountered this little quirk before and is it 2000 or 2007 related?

Reply | Quote

Replies

Are the dates unique? If so, you could set the date field to be the primary key of the table. Records should then be sorted by date automatically.
Or you can create a query that sorts the records by date and set that as record source (I don’t understand why you want to avoid that).
Or use

Me!frmDep.Form!frmISAPSubMeetings.Form.RecordSource = “SELECT * FROM tblISAPMeeting ORDER BY [DateField]”

where DateField is the name of the date field.

(I don’t have Access 2007 myself)

Reply | Quote

Thanks Hans. That gives me the info I need.

I don’t want to deal with a query because there is so much code all over in this database I have concerns about changing one thing and having something else break. Not too mention there are already reams of queries and the interface for 2007 is a big old pain in navigating through them.

I can’t change the primary key and date wouldn’t work anyways. About 600 new meetings are recorded by about 12 workers every month. Primary key is an autonumber. There are about 30,000 meetings currently in the database.

The simple solution is the SELECT statement. Why this cropped up all of a sudden when it has been working fine for 2 years and fine (mostly…) in 2007 since January is beyond me.

Reply | Quote

In a relational database, the records in a table aren’t stored in any particular order, so you cannot rely on the records being in the desired order. It may work correctly for a while, but it’s not reliable. That’s why you need a query or SELECT statement with an ORDER BY clause.

Reply | Quote

That is a given and I understand that, but in the absence of any OrderBy statements in the properties of a table doesn’t a table sort itself by its primary key if it is an autonumber? I have certainly never seen this behaviour before and why would it crop up all of a sudden?

Reply | Quote

Normally, the records would be returned sorted by the primary key if you don’t specify another sort order explicitly. But I never rely on that, I always specify the sort order, just to prevent the kind of behavior you experienced. (I don’t have an explanation why it suddenly changed)

Reply | Quote