Using DAO to move to the new record

  • Thread starter Thread starter John S. Ford, MD
  • Start date Start date
J

John S. Ford, MD

I'm trying to write some code that will move the cursor to the new record.
I've tried the following:

Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.MoveLast
Me.Bookmark = rst.Bookmark

Unfortunately, this moves the cursor to the last "completed" record, not the
new one. I've also tried

rst.AddNew

which doesn't work either.

Any ideas?

John
 
The new record row in the form does not have an equivalent in the
RecordsetClone of the form, so you cannot use that approach.

If the form is at a new record, attempting to reference the form's Bookmark
*should* produce an error. It does if the user has not begun entering
something. If an entry has been started, Access wrongly reports the bookmark
of the most recent entry you visited. (This bug has been present since at
least version 2 of Access, and AFAIK is still not fixed.)

So, make sure the form has focus, and use:
RunCommand acCmdRecordsGotoNew
AFAIK, there is no way to make a form go to the new record if it does not
have focus.
 
John S. Ford said:
I'm trying to write some code that will move the cursor to the new record.
I've tried the following:

Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.MoveLast
Me.Bookmark = rst.Bookmark
Click to expand...

The recordsetClone is a recordset that is the same as your form, but you can
process data, and move around independently of the current record that the
form has (this I think is clear to you).

however, if you requery the main form, or add new records to the clone, then
the book marks are NOT valid any more.

Further, when you add records to the clone, they are NOT part of the main
forms reocrdset until you re-query.

So, in theory, you *can* actually do as you ask, but the code would look at
follows:

Dim lngNewID As Long

Me.RecordsetClone.AddNew
lngNewID = Me.RecordsetClone!ContactID
Me.RecordsetClone.Update

Me.Requery

' now move to the new record:

Me.RecordsetClone.FindFirst "contactID = " & lngNewID
Me.Bookmark = Me.RecordsetClone.Bookmark

Since adding a new record means that we MUST requery the main form. Note
that when we do requery, then both clone and reocrdset are the same again,
and we can use book marks.

To further confuse the issue, you actually do NOT have to use the
recordsetClone, but in fact can use the ReocrdSet (the reocrdSet is the
forms ACTUAL reocrdset - note this is true only for a2000 and later...you
can't do this with a97). So, to further this issue, we could go:


Me.RecordSet.AddNew


So, yes...you can do all of this with one line..but I figured it would help
to clear up the use of "clone" vs reocrdset (the clone is great for
processing the data, general totals etc...but the form will not move, or
jump around (which often is ideal when processing the data...but we don't
want the form to move).
 
Back
Top