Years in a Subform Not In Order

[lindadee]

I have an Access form with a history subform embedded in it. The history subform consists of years, 2001, 2002, 2003. When someone renews a membership, I add the year to the subform. Always before the year went at the bottom of the subform and stayed there. For some reason the last few entries I made puts the year in the penultimate row, not the final row. I haven't made any changes to the program, so I don't know why this is happening. 2021 should not come before 2017, for example.

I am using Access 2016 on a Windows 10 on a PC.

[NeoPa]

Hi Linda.

If you specify the order of the data within the form then you can expect it to show in that order. However, if the order isn't specified then it could come in any order that makes sense to ACE, the database engine used by Access (Or Jet for much older versions). It can be very hard to predict what it will decide on so, if it's important for the user to see the data in a particular order then it's better to specify it within the design of the form (Order By in the Properties).

[lindadee]

Thank you NeoPa. "Order By" is just yes/no/blank. It was originally blank, but I changed to yes. That messed things up even more, so I set back to blank. As an example I want the years in the subform to show as follows:

2010
2011
2015
2017
2018
2020
2021

I didn't originally create this program, so I'm not sure how the dates are ordered. Adding a date in the subform adds it to the table Membership History.

Thank you for any assistance you can provide.

[NeoPa]

LindaLee:
"Order By" is just yes/no/blank.

I'm not sure exactly what it is you're trying to say with that, but I can assure you that it isn't correct. Sorry to be blunt but whatever it is supposed to mean is a long way from correct. Where did the idea come from? What led you to believe such a thing?

The Order By property of a form is a Text value that lists the Fields used to order by, in the order you want them ordered by if there's more than one of them. In your case I suspect there is just the one field, which is where you store the date data. If you set the value of the Order By property to the name of that field then I expect the form will work for you as you expect & hope.

[lindadee]

Sorry, I misunderstood your original response and didn't see any dropdown in the properties. I added "Year" to the Order By" and that solved my issue. Thank you very much.

[NeoPa]

Hi Linda.

Apology not required. There is no blame to being in a position where you don't understand - as yet. I was merely curious & confused myself as to where such an idea may have come from - and wanted to be very clear that it was mistaken. That's for your benefit, but also the many thousands of people who are likely to read this thread later after you're completely done with it.

As it happens, I was wondering, in my confusion as I can sometimes be like a dog with a bone, how that might get to be confused and I thought about the OrderByOn property - which of course is a Boolean (It takes Yes or No values.) - and I wondered if perhaps you'd been searching and came across that one. I hadn't mentioned it as, though it's certainly accessible by VBA code, it isn't one of the properties that shows when you're designing the form on the screen. I felt that would just make life more complicated for you.

The OrderByOn works with OrderBy and allows you to remove filtering temporarily without losing what it is you're filtering by. If you turn it on again later (OrderByOn=Yes/True) then it filters the same way you had it before.

Anyway, I'm very pleased to hear that your problem's now solved :-)