odd thing happeningwith subform/new record

  • Thread starter Thread starter peg
  • Start date Start date
P

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
 
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.
 
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!
 
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.
 
Back
Top