Data mixed up

[neo651]

I have a table that keeps track of my company's servers. A few examples of the fields are ServerID, CommonName, Office, Location, Purpose, IP. Today I opened up the datasheet form to update information on one of the servers and I saw that none of the data matched up anymore. Technically all the data was there, but nothing was associated to the right record. It was as if each column were independently shuffled and so now the whole table is ruined and I have to manually gather all that information again.



So without going over the top expressing how angry I am, let me just say, what happened!?!? And how do I make sure it NEVER happens again. Please help.

Thank you.

[boblarson]

First off I guess we need to explain that Access does NOT store data in tables in any particular order. In order to have order you need to use a query to apply a sort based on something you have in the record to give it order. Many times someone will use a record ID but the best, in my opinion, is to add a date/time stamp to the records as they are added and then in the query you can sort by that field and have the records in the order they were entered.

And just so you are also aware - do not get into the habit of using those "record numbers" that appear at the bottom of the form with the navigation controls. They are meaningless. They only are a relative measure of the recordset and they are not going to be associated with a particular record. If the recordset changes or if you don't use a query as the form's record source and use a table instead, then opening the form one time can have differing results (as you have found).

And one last item - when you open a table, you aren't actually viewing the table. But you are viewing basically a system query of the data.

[neo651]

I think I need to clarify. Let's say for example I had 3 servers like this:
ID Hostname Location IP
1 Exchange Rack 1 192.168.1.1
2 PhoneSvr Rack 2 192.168.1.2
3 SQL Rack 3 192.168.1.3

Today I opened up the datasheet form, and latered verified that it displays the same in the table, all the fields are mixed up amongst the records. Like this:

ID Hostname Location IP
1 Exchange Rack 2 192.168.1.3
2 PhoneSvr Rack 3 192.168.1.1
3 SQL Rack 1 192.168.1.2

Nothing is missing. But everything is associated to the wrong server.

[boblarson]

The only way that could really happen is if someone changed the data. It wouldn't do that itself.