determining next record in BeforeUpdate event

[todholt]

Hello,

I am trying to bypass a form's automatic update, and instead call a
stored procedure in sql server. I am using continuous forms. The
problem I am having is with returning to the next selected record once
I have performed the update.

i.e. user edits record A and then clicks on record B. Record A is
updated, and selection moves to record B. I don't know how to
determine what record B is...

The best that I am able to achieve so far is listed in my code below.
This returns to the most recently updated record once the update is
performed. Here's the gist of my code in the BeforeUpdate event:
Private Sub Form_BeforeUpdate(Cancel As Integer)

...code to manually call an update stored procedure

' bookmark record A position
vntBookmark = form.bookmark

' refresh form
Form.Undo ' this stops the regular update process from occuring
Form.Refresh ' this requery's the db so that the updates are visible.

' go to saved position
Form.Bookmark = vntBookmark

end Sub

The above code returns to record A after the refresh. Does anybody
have any suggestions on how to bookmark the "next" selected record
(record B)?

Any solutions/suggestions would be greatly appreciated.

Thanks,

Todd

[Gord]

Have you tried

DoCmd.GoToRecord , , acNext

?

[Todd]

That's a good idea, but the next record the user selected isn't
necessarily the next record on the form. Any other ideas?

[Todd]

Something I should probably mention to make things clearer is that the
record position is lost when a form.refresh command is given. This is
what creates the problem of determining which record the user clicked
on to initiate the beforeUpdate event.

[Arno R]

"Todd" <to*****@hotmail.com> schreef in bericht news:11**********************@g47g2000cwa.googlegr oups.com...

Something I should probably mention to make things clearer is that the
record position is lost when a form.refresh command is given. This is
what creates the problem of determining which record the user clicked
on to initiate the beforeUpdate event.

You have a unique id value in the record I guess?
Store the value (on current) in a var, and go back to (find) the record if needed.

Arno R

[Todd]

I do have a unique ID associated with each record but the problem with
that solution is that the form_current event actually occurs after the
beforeupdate event. If I were to move the form.refresh statement to
the form_current event I would encounter the problem of being stuck on
the record that is being updated (form.undo would keep me stuck in the
edited record). Thanks for the suggestion. Any other ideas?

[Arno R]

"Todd" <to*****@hotmail.com> schreef in bericht news:11**********************@o13g2000cwo.googlegr oups.com...

I do have a unique ID associated with each record but the problem with
that solution is that the form_current event actually occurs after the
beforeupdate event. If I were to move the form.refresh statement to
the form_current event I would encounter the problem of being stuck on
the record that is being updated (form.undo would keep me stuck in the
edited record). Thanks for the suggestion. Any other ideas?

I don't think I grasp what's happening here (what you are trying to do...)
I *do* think you will have to store the ID, one way or another.
- Use a var 'lastclicked' if the user really 'clicks' in a new record.
- Or use two vars: 'lastcurrent' and 'beforelastcurrent' (beforelast.. get's it's value from the last..)
But again, I am not sure this is what you need...

Arno R

[Todd]

I think you are on the right track. The problem is determining the ID
of the record the user clicked on. The hitch is that all of the events
(that I tried) occur after the beforeupdate event, so there's no way of
actually recording the ID before I screw things up by running a
form.undo and form.refresh. The undo and refresh are necessary (I
think) for me to bypass the regular update procedure, and call my
stored procedure instead. I hope this explanation helps clarify.