Re: Append to table

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" <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:m1u6629l5604nigci3eftbkgo2hvsm885s@xxxxxxxxxx
On Thu, 11 May 2006 13:15:38 +0100, "Derek Brown"
<derek@xxxxxxxxxxxxxxx> wrote:

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]


.

Relevant Pages

  • Can I autonumber an append query?
    ... I have a problem with an append query where i need to increment ... Is there any way round this so it updates the select query for each ...
    (microsoft.public.access.queries)
  • Re: Creating Updateable Datasheet Based on Mult. Queries
    ... <MS ACCESS MVP> ... I also added all of the key fields from ... "Ken Snell " wrote: ... Do you think if I included the other key fields in the query it may ...
    (microsoft.public.access.queries)
  • Re: Creating Updateable Datasheet Based on Mult. Queries
    ... <MS ACCESS MVP> ... Do you think if I included the other key fields in the query it may help? ... "Ken Snell " wrote: ...
    (microsoft.public.access.queries)
  • Re: Creating Updateable Datasheet Based on Mult. Queries
    ... I also added all of the key fields from each ... "Ken Snell " wrote: ... <MS ACCESS MVP> ... Do you think if I included the other key fields in the query it may help? ...
    (microsoft.public.access.queries)
  • Re: Plus One Button on Form
    ... "John W. Vinson" wrote: ... Dont get me wrong, of course there are tables in my DB and this is where all ... I was thinking of making a query for the 'plus ... What value do you want to increment? ...
    (microsoft.public.access.forms)
Loading