Need help understanding OnDeactivate event

[TheresAlwaysAWay]

This should be an Access 101 question and it's almost embarrassing to ask, but I'm finding it confusing and I'm asking for some clarification from one of the wizards out there.

If I set a form's OnDeactivate event with a Msg that says, "OnDeactivate ID " (that's just so I know which event triggered it) & [ID] (so I can tell which record is being deactivated), here's what I find.

If I'm on ID 5 and I move to ID 6 for example, the OnDeactivate message says "OnDeactivate ID 6". I would have assumed it would be ID 5 that is deactivated before moving to 6, but it always displays the ID of the record I'm going to, not the one I'm leaving. I want to run some instructions before leaving the record. According to [URL unfurl="true"]https://support.office.com/en-us/article/order-of-events-for-database-objects-e76fbbfe-6180-4a52-8787-ce86553682f9#bm2[/url], there is supposed to be a RecordExit event in Access 2007, but I don't see it anywhere in the list.

Ideally I want to run the instructions before I:

a) Move to another record
b) Move from the active form to a different object
c) Close the form

The OnDeactivate event runs for all three of the above conditions, but it always indicates the next record's ID rather than the current ID.

I know that I must be missing something simple, but nonetheless I'm missing it and I'm hopeful someone can help me resolve it. As always, all assistance is appreciated in advance.

 

[dhookom]

I don't think you can use the Deactivate event like that.

I would create a variable in the form general declarations like:

Dim lngID as Variant

Private Sub Form_Current()
    MsgBox "Previous ID: " & lngID
    lngID = Me.ID
End Sub

You might also need to use some code in the deactivate and/or close events to grab the lngID value.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TheresAlwaysAWay]

Your code sample identifies the previous ID number. What I just don't understand is why the deactivate event is looking at the record I'm headed for rather than the one I left. It appears intuitively that one would be deactivating ID 5 before arriving at ID 6.

Weird!

 

[dhookom]

I don't believe the deactivate event is appropriate for your needs. Deactivate is triggered when the form or report loses focus. It is not triggered when a form moves from one record to another.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TheresAlwaysAWay]

OnDeactivate does fire changing from one record to another. Just put in a message and you can see it.

 

[dhookom]

Sorry, it doesn't in my database (Access 2016). I tested before I posted.

Are you putting the code in the Form or some other object event?

I don't get any msgbox while navigating from record to record with this code:

Private Sub Form_Deactivate()
    MsgBox "Deactivate"
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TheresAlwaysAWay]

I've come up with a workaround. The OnDeactivate event does trigger at the times I want, so I set a variable with the ID of the record I'm leaving, and then ran SQL based on that variable rather than the current ID. It works, but still doesn't explain why OnDeactivate wants to show me the record I'm headed for rather than the one I left. I hope someone can explain it!

 

[dhookom]

Does your code look anything like mine?

When I close the form, on the Deactivate code I get the value from the first record on the form which has nothing to do with the current or previous record.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TheresAlwaysAWay]

The message I created was "Deactivate " & [ID] to identify the record that was being deactivated. I always get the record I'm headed for, not the one I'm leaving. Don't you get the same result?

 

[dhookom]

I tested this and found the ID (my field was different) was always from the first record in the form's record source. It had nothing to do with the current or previous records. At first I thought it was the previous but after checking with lots of iterations it was always the first. I only checked by closing the form which trigger the deactivate. I may need to try opening another form and switching back and forth.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TheresAlwaysAWay]

We're doing something different then. I have tested this repeatedly and it always shows the ID of the record I'm moving to. I can't imagine what the difference is, but I assure you that's the result I'm seeing.

 

[dhookom]

I believe you and will try a couple more tests that involve changing to another form.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TheresAlwaysAWay]

This is WEIRD!! I created a dummy database with one form and one table and it did not work as I described. I had neglected to mention that I'm working in a network environment, so I figured maybe that was why. I created a linked table and a test form, and again, it did not work the way I noted originally in the post. Yet I swear that in my active forms the event does fire as I have described. I haven't a clue why, although I do have many other events actively running instructions (OnOpen, OnCurrent, OnDelete, OnDblClick, OnDirty, OnOpen, OnClose, OnActivate, and OnTimer) on that form. Maybe the firing of one of them has something to do with triggering the OnDeactivate event.

Regardless, I figured out a workaround as I described earlier, harnessing the fact that it fires at all to tell it to do what I want it to do, but honestly I'm clueless why I can't reproduce it with either a new form on my network or even with a wholly new database using one form and only one event specified. I guess I'm just gonna have to chalk this up to a mystery of Access. Since it does what I need it to do with the workaround it isn't worth the effort of trying to figure out why I'm seeing what I see at the present time. Now that I've learned the idiosyncrasies of OnDeactivate maybe some day I'll come back to it and puzzle over it more, but for now, I'm just gonna consider it fixed.

I had no idea that this could not be duplicated by others (or even by me) in a new form, so I feel like I wasted everyone's time and good karma, but I hope you recognize that I wouldn't do that intentionally. Please accept my apologies and believe that it does do what I describe on my active form. I'm not (too) crazy, but hey, I have to accept the reality that it doesn't reproduce what I am seeing even when I do it myself.

I seriously want to thank you and anyone who put even an iota of thought into solving this. If I ever return to this issue and come up with anything new I'll be sure to return to this post with updates of what I find.

I might suggest that if someone needs an event to fire as I've described, you might play with adding other events to the test form. Something is making it behave the way it is, but it's not the OnDeactivate event by itself.

 

[SkipVought]

Chalk it up to OJT.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

TheresAlwaysAWay,
There are reasonable people asking questions here and unreasonable. You fit the reasonable category so it's not a bother.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[strongm]

>I haven't a clue why, although I do have many other events actively running instructionI haven't a clue why, although I do have many other events actively running instruction

You could always stick a break in the OnDeactivate event, and then examine the call stack. That might provide a hint.