Append to table

[Derek Brown]

Hi All

I have an append query that adds a record to a Table. The table has a key
field that is incremental.
to my utter amazement when the query runs the new record does not
nessassarily append the new record to the end of the table. It fits the
record in where it can. This is not a problem when the records run
concurrently on the key field, it then sticks the new record at the end of
the table but if some one deletes records the append query fits new records
in the middle of the table? So when I ask the form that uses the table to go
to the new record by choosing acLast I do not always find the new record.

Any help?

 

[Guest]

Hello there,

You can use the SQL builder (Record source) to have your records sorted
according to the needed field.

Hope this helps.

George

Ο χÏήστης "Derek Brown" έγγÏαψε:

 

[John Vinson]

Hi All

I have an append query that adds a record to a Table. The table has a key
field that is incremental.
to my utter amazement when the query runs the new record does not
nessassarily append the new record to the end of the table. It fits the
record in where it can. This is not a problem when the records run
concurrently on the key field, it then sticks the new record at the end of
the table but if some one deletes records the append query fits new records
in the middle of the table? So when I ask the form that uses the table to go
to the new record by choosing acLast I do not always find the new record.

Any help?

Your error is assuming that a Table has a meaningful order. It
doesn't.

A Table should be viewed as an unordered heap of records. If you want
to see the records in some particular order, you must - no option! -
use a Query sorting it by some field or fields within the table.

acLast (and the First and Last totals-query options) are pretty
useless; they return the last or first record in disk storage order,
and as you have seen, that order is arbitrary and uncontrollable.

John W. Vinson[MVP]

 

[Derek Brown]

Thank you.

The problem appeared to be that I had not set the key fields increment
property to Yes No Duplicates. This will effectively stop access from
re-using an increment number again, even when a record is deleted. All
records that are appended under these conditions are appended to the end of
the table and as such do not need sorting. I was trying to avoid setting up
a DMax field and adding "1" to each new record and then using a query to
sort as you suggested.

 

[John Vinson]

Thank you.

The problem appeared to be that I had not set the key fields increment
property to Yes No Duplicates. This will effectively stop access from
re-using an increment number again, even when a record is deleted. All
records that are appended under these conditions are appended to the end of
the table and as such do not need sorting. I was trying to avoid setting up
a DMax field and adding "1" to each new record and then using a query to
sort as you suggested.

I think you're still confusing the issue.

An Increment property can either be Increment - or Random. "Yes no
duplicates" is not a property of an increment - it's a property of an
Index.

An Autonumber field (even if it's sequential) *WILL* have gaps. It
will never, except in occasional situations where there's a bug in
Access, reuse any number it's ever assigned before.

If you want gapless numbers, you must program them yourself using the
Max() option. If you want to fill in gaps after deleting records...
you need even more complex options, and you should really really
consider whether it's either necessary or wise! Remember, the number
of the record you deleted might be on a Post-It note, printed on a
report or memo, or firmly embedded in someone's memory; reusing it for
a different entity might be a Really Bad Idea.

John W. Vinson[MVP]