odd thing happeningwith subform/new record

[peg]

i have a form which has one field that they update (a periodical update
based on 90 days), and the subform is the information (read-only) of the
item they are doing the status update on. Within the main form i have a
button which allows to add a new 'record' for which there will be status's
done on. It adds the record into the table via code, and then inserts a
record for the status for the period they are currently in, and then it
requeries the form & subform.

Now here is the weird thing. Lets say I had 2 records in the set, then I
add one. I have the records come back in an order that put the new added
ones 1st (identity column desc). It shows there are 3 records, but when i
use the navigation and try to get to the final record - it keeps shoving me
back to the 2nd one. I thought it had something to do with the last one
being the new record -that when i changed the order the recordset appears
in.

Anyone ever run into something like this? I'm going nuts trying to figure
it out!

Peg

 

[Roger Carlson]

There is no guaranteed order to records in an Access database unless you
explicitly impose an order by the use of a query with a sort (Order By
clause). I realize that using the Access interface, it may look like there
is, but when opening a record set (as in the record source for a table),
they can be in any order. This order is determined by the Jet engine and is
influenced by a number of factors. If these factors change, the order will
change. This is why you should ALWAYS base a form on an ordered query,
rather than the table directly.

 

[peg]

I do - on both my main & sub form the records are order with a "ORDER BY" in
the record source.

what i need to figure out is why when i try to go to the last record in the
set that it bumps me back to the previous record!

 

[peg]

Here is some more information.

lets say there was only one record when i 1st go into the form. Then i do
the add, it now show 2 records on the navigation, but a had it also do a msg
me.recordset.recordcount and that brings back '1'. when i try to get to the
2nd record - i see the data flash for a second on the form for the 2nd
record - but then access automatically takes me back to the 1st record.

If this makes any difference - this is an Access project (.adp) using MS SQL
server as the backend/linked tables.