Access 2010 Can't Get To Table Design Mode

[hlewton]

I have no idea what is going on with this database that I created a few years ago other than I just never noticed all the oddities before. I have two forms in this database that can be used to enter new or edit existing data. They are both based on the exact same table since there is only 1 table in this database. When I opened the first one the other day I noticed it did not open to the first alphabetized name which starts with an “A” it opened instead to one that started with an “S.” I played around with the properties of this form and somehow got it to open correctly. However, the second form, which is entering all the same date but is broken up into separate tabs still opens to the data entry starting with a “S” and I can’t seem to find what in the properties I can look at to make it open correctly. Which property can I use to do this since I did not see any option in the ribbon to state the sort order?

[HansV]

The safest option is to create a query based on the table, and specify the sort order there:

S0681.png

Save the query, and set the Record Source of the forms to this query.

Another option is to set the Order By property of the form in the Data tab of the Property Sheet to the field(s) you want to sort on, and to set the Order By On Load property to Yes:

S0682.png

If the user changes the sort order, then saves the form, the above properties will be overwritten, however, so it's not dependable.

[hlewton]

Thanks Hans. I used the second method because after checking that is how the other form was designed and I'm not at all sure how that happened but it is working now. I am the only user and do not see any future circumstances where I would ever want to change the sort order unless it was done by accident.

Let me ask what is probably a silly question but I was not sure if there was any particular syntax that needed to be adhered to when filling that "order by" field and could not find any drop down allowing me to choose the lastname filed I wanted to use so I just copied it from the other form that was working. Had I not had that other form to copy from would I have just been able to type "Lastname" without the "str" you have or would I have had to use the []? I am not sure what "str" ahead of the field name is unless it is your field name itself. When I copied what was working it came out "address1.[Last Name]" so just curious how I would have gotten the correct entry in the order by field.

[HansV]

Unfortunately, there is no dropdown list or 'builder' dialog for the Order By property. You have to specify the sort order yourself, by entering the names of the fields on which you want to sort, separated by commas if you enter more than one field.
In the screenshot, strLastName and strFirstName are the names of the fields on which the form will be sorted. If a field name contains spaces or punctuation, it has to be enclosed in square brackets [ ].
By default, the sort order is ascending. If you want to sort descending, you have to add a space and DESC after the field name, e.g. if you enter

[Last Name] DESC

the form will be sorted descending on the Last Name field. And if you enter

[Last Name], [First Name] DESC

the form will be sorted ascending on Last Name, and within each last name, descending on First Name.

[hlewton]

Thanks for the explanation.