Inconsistent Results when I use AddNew on a Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the following code to add a new record to an existing table.
If Rotation!Division = Division!Division Then
Rotation.AddNew
Rotation!Division = Division!Division
Rotation!Code = Division!Code
Rotation.Update
Rotation.MoveLast
GoSub GetStartDate
GoSub GetEndDate
GoSub WriteNameToDateField
Division.MoveNext
I use the MoveLast command to move to the record I just created the GoSub
procedures also edit the same record. Nine out of ten times the result is
exactly what I expect. However, 10% of the time the AddNew command moves the
new record to the begining of the table vice the end and the results are
unuseable. I never see this problem when I step through the program. Usually
when it does happen, I rerun the procedure again an I get the normal results.


Why does this occur and how can I make it stop?

Note: The help file says the following (Iam using a table type recordset),
The position of the new record depends on the type of Recordset:
In a dynaset-type Recordset object, records are inserted at the end of the
Recordset, regardless of any sorting or ordering rules that were in effect
when the Recordset was opened.
In a table-type Recordset object whose Index property has been set, records
are returned in their proper place in the sort order. If you haven't set the
Index property, new records are returned at the end of the Recordset.
The record that was current before you used AddNew remains current. If you
want to make the new record current, you can set the Bookmark property to the
bookmark identified by the LastModified property setting.
 
MoveLast is not guaranteed to give you the record you just added.

Instead se the Bookmark of the Recordset to be the same as the one you
LastModified, i.e.:
...
Rotation.Update
Rotation.Bookmark = Rotation.LastModified

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
=?Utf-8?B?UmFtYm8gZnJvIEphcGFu?= <Rambo fro
(e-mail address removed)> wrote in (e-mail address removed):
GoSub GetStartDate
GoSub GetEndDate
GoSub WriteNameToDateField
Click to expand...

GoSub... now there's nostalgia for you!

All the best


Tim F
 
Thanks for your prompt answer.
I guess I am out of luck. I just read your web site. The problem is I was
using the following code to go back to where I was in the Table.

'Moves the cursor to where we left off.
Rotation.MoveFirst
Rotation.Move (i)

Where "i" was an iterator that kept track of where we were in the table.
Any idea on how to keep track of the positions of the original records?
 
A naval officer choosing to go sub? :-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
 
Hmm.

Moving by record number is something we don't do in multi-user software like
Access. There's an old article entitled:
What, no record numbers?
at:
http://members.iinet.net.au/~allenbrowne/xbase-03.html

It illustrates how to save your place as a bookmark, and move back there
again later.

The other alternative is to save the primary key value of the record you
want to return to, and then FindFirst again later. This article explains how
to use that technique even after Access has been closed to return to the
same record again:
Return to the same record next time form is opened
at:
http://members.iinet.net.au/~allenbrowne/ser-18.html

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
Ouch. no pun-ishment please. Ironically enough I am a submarine officer.
Thanks for your help. The table I am working on is dynamically created to
generate a report. It creates a list of divisions from one table, then based
on date entries from the user, it appends each month from the start date to
the end date as a field. The module then takes the start and stop dates for
assignments to a division from another table, and writes the responsible
division officer into the appropriate month field. Since a different divison
officer will be assigned during later months, the program adds the division
entry again into the report table, then writes the new division officers data
into the fields. The module then goes back to where it left off to continue
adding data from the division officer field. It works very well with the
exception of what I described before. Right now I just re run the module and
it usually works fine.

Normally I would run a crosstab query to get the result I'm looking for, but
I run into two problems; (1)Often times I need more fields than the crosstab
will return, (2) I need the fields to comeback in month /year order.

Again thanks for your help.
Rambo
 
Back
Top