BookMark Problem in VBA 1

[HenriM]

I have a number of objects in my database which I need to see recalculated on screen as soon as they have been (new record) entered or changed (amended existing record). I have the following code in the "On Exit" event:-

If Me.Dirty Then
On Error GoTo err:
Dim BM As Variant
BM = Me.Bookmark
Me.Recalc
Me.Bookmark = BM
err:
Me.Cost.SetFocus
End If

Changing an existing record, this works fine, and the focus moves happily to the named object ("Cost" in this case) in the active record. The Recalc works.

When I enter a NEW RECORD, however, the focus moves to the named object in the PREVIOUS RECORD.

I suspect that this is because at the time of exiting the object the record has not yet been saved in the Recordset.

I have tried using RecordsetClone to update the recordset on exiting the first field. This puts the bookmark in the right place but doesn't do the Recalc!

So what I need is something which makes the new Record into an existing record, closes it and then reopens REMEMBERING THE BOOKMARK. Since I only have this problem with new records I also need something which can differentiate between new and existing records.

I would have thought that nobody would want to change or input figures on a screen without seeing the revised totals immediately and then just passing on to the next object; that in fact the Bookmark would default to the active record, saved or not; but no such luck!

I hope I have explained myself clearly; does anyone have any ideas?

 

[RoyVidar]

Hi!

Some thoughts:

Not 100% sure, but I think that when using recalc on a new record, you'll risk the same effect on the bookmark as doing a requery - invalidating the bookmark (either that, or the bookmark isn't valid, or something to that effect).

If you haven't alredy tried it, try to issue a save record prior to your retrieving the bookmark.

[tt]docmd.runcommand accmdsaverecord[/tt]

Don't know if it's possible to rely on bookmarks, I would have tried using the primary key value of the new record, and use .findfirst, for instance like this with a numeric primary key value:

[tt]dim lPK as long
dim rs as dao.recordset
lPk = me!txtPK.Value
me.refresh
rs.findfirst "PKField = " & lPK
if not rs.nomatch then
me.bookmark = rs.bookmark
end if
set rs = nothing[/tt]

- needs a reference to Microsoft DAO 3.# Object Library

Roy-Vidar

 

[HenriM]

Thanks Roy!

I used the docmd.runcommand accmdsaverecord and that worked perfectly.

You also opened up a new vista for me!

After 3-4 years use of Access I had never seen or heard of the Docmd.RunCommand options and I hade been using a roundabout way of deleting (or rather hiding) unwanted records.

I have changed everything to accmddeleterecord and the whole thing works much more smoothly.

Sorry, there doesn't seem to be a way of givimg two stars, although you have actually solved two problems for me!

Best regards and thanks again

HenriM