Table doesn't start at 1st record

[jpl458]

I have a form that allows the users to run through the data in the Master file one record at a record at a time. However, I can't get the form to look at record with autonumber ID of 1. It starts on the 5th record. Prior to this I ran a make table query of the older Master, saved it and changed the names to protect the innocent, in order to get rid of spurious IDs that were created during development. Any suggestions would be appreciated.

 

[plog]

You need to use VBA, specifically .OrderBy

Change the filter or sort order of a form or report

Office VBA reference topic

learn.microsoft.com

 

[Pat Hartman]

Tables and queries are by definition unordered sets. If you want to enforce order in a query or table and you should ALWAYS want to enforce order, add an Order By clause to the RecordSource query.

Autonumbers have no meaning and you should not assign any meaning to them. Their sole purpose is to provide a unique identifier for a record.

Reports are a special case since the report has an internal order by so you can't control sort order of a report by using the Order By clause of the RecordSource, you MUST use the order by property exposed in the report design view.

And finally, when you compact a database, Access rewrites all tables into PK order. Records get out of order during the update process. If a record becomes longer when you update it, Access can't put it back where it was so it replaces the original data with a link and then places the updated record at the end of the table. Sometimes you will see this behavior but you will need enough data to fill the "block" used in the I/O process. It is never a single record. It is called different things, sometimes physical record versus logical record, sometimes block, sometimes sector, etc. Anyway, it represents the level at which data is actually read from or written to the physical device.

 

[GPGeorge]

I have a form that allows the users to run through the data in the Master file one record at a record at a time. However, I can't get the form to look at record with autonumber ID of 1. It starts on the 5th record. Prior to this I ran a make table query of the older Master, saved it and changed the names to protect the innocent, in order to get rid of spurious IDs that were created during development. Any suggestions would be appreciated.

AutoNumbers are guarantied only to be unique within a table--barring corruption of course. Nothing else about them should be of interest or concern to the human eye. Missing values and gaps are normal and to be expected. Normally, no one is even aware of that. It doesn't matter.

 

[jpl458]

Tables and queries are by definition unordered sets. If you want to enforce order in a query or table and you should ALWAYS want to enforce order, add an Order By clause to the RecordSource query.

Autonumbers have no meaning and you should not assign any meaning to them. Their sole purpose is to provide a unique identifier for a record.

Reports are a special case since the report has an internal order by so you can't control sort order of a report by using the Order By clause of the RecordSource, you MUST use the order by property exposed in the report design view.

And finally, when you compact a database, Access rewrites all tables into PK order. Records get out of order during the update process. If a record becomes longer when you update it, Access can't put it back where it was so it replaces the original data with a link and then places the updated record at the end of the table. Sometimes you will see this behavior but you will need enough data to fill the "block" used in the I/O process. It is never a single record. It is called different things, sometimes physical record versus logical record, sometimes block, sometimes sector, etc. Anyway, it represents the level at which data is actually read from or written to the physical device.

I believe you explained the part about using a query in the record source to set the order. I will go ahead and forget about the autonumbers. While i have your attention, I was just looking at the example you sent that explains bookmarks and Word docs. But I cant figure out how you get the form to look so trim and tidy. I scrounged around in that form in design mode, but still can't get mine to be as neat as yours.

1 No Trump

 

[gemma-the-husky]

I have a form that allows the users to run through the data in the Master file one record at a record at a time. However, I can't get the form to look at record with autonumber ID of 1. It starts on the 5th record. Prior to this I ran a make table query of the older Master, saved it and changed the names to protect the innocent, in order to get rid of spurious IDs that were created during development. Any suggestions would be appreciated.

You don't want the process to run through the data in the TABLE. You want to run through the data in a QUERY based on the table, so that the query has the order you want, and the items you want.

It starts on the 5th record is meaningless in this context. It starts on the first record, but the first record is not the record you expect, or need to be first. Hence the need for a query.

So if you want to process the items in autonumbrr order, you can do that, but the autonumber order may not be significant in a business sense.

 

[jpl458]

You don't want the process to run through the data in the TABLE. You want to run through the data in a QUERY based on the table, so that the query has the order you want, and the items you want.

It starts on the 5th record is meaningless in this context. It starts on the first record, but the first record is not the record you expect, or need to be first. Hence the need for a query.

So if you want to process the items in autonumbrr order, you can do that, but the autonumber order may not be significant in a business sense.

Understand, thanks.

 

[Pat Hartman]

If you have a question about formatting forms, please start a new thread in the forms forum. Post your attempt and mine from the Word automation tool if that makes sense. If the question is about the tool, I will answer it if I can. I am currently working on a new version of the database that uses tables for the automation. It will be much more useful in the real world but I may not post it for free. I have described how I built applications that manage thousands of documents using something similar to what I am building so the technique is not a secret. The hard-coded method from the sample you have is only useful for a couple of documents that share most bookmarks.

 

[jpl458]

I played with your app again this AM and tried different property settings, mainly the one you have in your app, and my form looks a lot better.

Thanks

 

[Pat Hartman]

I'm assuming you changed from tab to overlapping windows view.