Records Listed Out of Order in Form

[ThomasCurcio]

I have a very basic access database. The table that contains all the data has 221 records, all listed alphabetically.



The form that reads and writes data to the table only shows 219 records; there is a 2-record discrepancy. In addition, the form displays the records out of alphabetical order.

Example: If I look at the table at record 10, it will have the correct data I expect to find, if I go to record 10 in the form, different data is displayed.

How can I resolve this issue?

Thank you in advance for your assistance.

Thomas

[jojowhite]

you need to Add Sort Order on the Form.
Sort it by Primary Key.

Maybe you have a "filter" in place in your form, that prevent the 2 records from appearing.

[ThomasCurcio]

you need to Add Sort Order on the Form.
Sort it by Primary Key.

Maybe you have a "filter" in place in your form, that prevent the 2 records from appearing.

************************************************** *******************
Hello and good morning.

Thank you for your response. I will test your sorting suggestion.

I checked the Form, I do not have any filters applied that I can find. Odd that it is only 2 records.

Regards,

Thomas.

[Micron]

You can't expect the record in the 10th row in a table to match the one in the 10th row in a form if you're missing 2 records. If the form is based on a query, that can explain why you're missing 2 records. It also implies that you need a sort order in that query. If the form is based on a table and the order is different, that can be because tables are more like a bucket of marbles. Any semblance of order is attempted when you open the table. It may not be as you expect, unless you do things like sort on table fields. I can't say what happens if you put a sort order on a table field and base a form on it, because I rarely base forms on tables. It's not the best practice.

[Ken Sheridan]

Example: If I look at the table at record 10, it will have the correct data I expect to find, if I go to record 10 in the form, different data is displayed.

A table is a set. A set has no intrinsic sort order, that's a fundamental mathematical principle. The rows you see in a table's datasheet can be ordered in any way you wish, as can the order in which a form returns the rows. The two are not necessarily the same. The order in which a form returns rows can be governed by setting its OrderBy property to the name of one or more columns or to an expression referencing one or more columns, or the form's RecordSource can be a query whose ORDER BY clause returns the rows in a specific order. I prefer the latter, so a table of financial transactions might have the following as its RecordSource:

SELECT *
FROM Transactions
ORDER BY TransactionDate DESC

The transactions will be returned in descending date order, so the latest transactions will appear at the top of the form in continuous forms view, or will be the first records returned in single form view.

As regards the missing two rows, what happens if you change the form's RecordSource property to:

SELECT *
FROM [TableNameGoesHere]
ORDER BY [NameOfSortColumnGoesHere];

[mike60smart]

Can you upload a copy of the Db?