Indentifying pressed Navigation Button

I'm using the inbuilt Navigation buttons on an Access97 form.

I have an Audit trail class module. I create an instance of this class when the form opens and reuse the same instance until the form closes.

This class basically iterates through all the controls on the form (by setting the focus unfortunately as it's the only way to get some of the properties I want) and basically stores the most pertinent properties I want in arrays private to the class.

When the user navigates to a new record in the form, the Form_Current event is used to take a snapshot of the control values using the class (i.e. it refreshes my arrays in the class).

In the Form_AfterUpdate() event, I call the class again and take a snapshot of the new values. I then compare the new values with the old values and, where different, store the array values and other properties into an Audit table.

This all works perfectly fine.

PROBLEM: The problem is that, when the Navigate Forward/Back/other buttons are pressed after a change has been made in a record, the Form_AfterUpdate() event triggers ok but, after logging the differences in my Audit table, the form does not navigate away from the changed record.

If I click the same Navigation button again, it will navigate away from the current record ok (because no change has been made and therefore the Form_AfterUpdate() event is not been triggered).

Even trying my own navigation button (Docmd.GoToRecord ,,acNext), I get the error "You can't go to the specified record").

PROBABLE CAUSE: Iterating through the form during the Form_AfterUpdate() event (well, in the class actually), setting the focus to each control.

Is there a way to identify which of the in-built Navigation Buttons was pressed? If I can do this, then I can use this in the Form_AfterUpdate event to force the Navigation myself after the update has been completed.

Or are there any other suggestions?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I think your problem is the OnCurrent event code.  It fires a lot more often then you would think.  Try disabling the code you have there and see if that corrects the navigation problem.  If so, there are two solutions:

1. Use a flag variable in OnCurrent to protect the code (so it only gets executed once).

2. Instead of using OnCurrent, check the .Oldvalue property of each control in to determine if the control has changed.  This is the method I would use as it will elimanate all the class overhead.  The only trick here is that you will need to move your audit code to the BeforeUpdate event.  I believe in AfterUpdate that the .Oldvalue properties will no longer be valid (might want to double check though).

NoggyAuthor Commented:
Hi JDettman,

Thanks for your reply. I haven't had a chance to try out your suggestions yet but there are already some answers that I can provide:

0. I won't be able to disable all the code in the OnCurrent event as it's there that I make my first call to the class. I know that the problem doesn't occur with my class calls commented out. Hence why I said that it seems to be the SetFocus calls in the class that are at fault: these are the only actions that the class makes that actually "interferes" with the normal operation of the form. But I guess that you already suspected that. No harm in clarifying it anyway :-) .

1. <<Will try this out later>> It might work but, from what I recall, the OnCurrent event wasn't firing at all when I had a breakpoint there (and elsewhere) to see which events were firing and how often.

However, I could try expanding this idea a bit further and actually setting a module level flag in the form which I'll use as a wrapper for all the control/form events. The reason for this is that many of the ComboBox controls have OnGotFocus, OnLostFocus and OnDblClick events that perform the following:
    OnDblClick - Opens the underlying RowSource of the ComboBox so that new reference data records can be added.
    OnGotFocus - Requeries the combobox so that any new reference data records appear in the dropdown.
    OnLostFocus - Same as OnGotFocus
Maybe it's a multitude of these events firing that are impeding it somehow too.
If this does solve my problem, it won't be the best solution as I wanted an Audit class module that I could quite painlessly apply to any form to provide audit functionality. Having to put an If Flag Then...End If wrapper around every event in the form would be a pain....and much more fallible.

2. My initial foray into providing an audit trail did actually use the OldValue property....and it worked. However, it didn't meet my requirements because, on many of my forms, I have ComboBox lookups (normally 2 columns) that lookup the value against a reference table.

The BoundColumn for the ComboBox is the ID field (ColumnWidth set to 0 in the ComboBox so that the user just sees the pertinent lookup field) in the reference table to provide a degree of separation.

Therefore, as the form control is only storing the ID value, the OldValue property is not much good. What I really needed was to know the text that was in the unbound column that the user can see. And you can't get the value of the text unless the control has focus.

I know it's not very neat but that was about the best solution that I could think of. The other solution that I considered was to use DLookup in my class to lookup the unbound column that is in view. However, I disregarded this because the RowSources for some of the ComboBoxes are queries where the unbound column in view is actually from a related table to the table that has the bound column ID. I would need to navigate through the queries (and maybe even parent queries of that query) to be able to locate the text value that the user sees. Rather ugly, I know. And the fact that DLookup is very slow too.

So, you've got the overall situation now and the reasons why I have done what I have. Hence why I was wondering if there was any way on getting a handle on what the navigation button action was. I presume that, as you did not comment on this, there either isn't a way to get this....or, hopefully, that you are looking it up somewhere (though I do have MSDN Univeral myself and couldn't find anything).

Anyhow, I shall look forward to your next comment.


Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<So, you've got the overall situation now and the reasons why I have done what I have. Hence why I was
wondering if there was any way on getting a handle on what the navigation button action was. >>

 No you can't hook the standard buttons, so you'd have to do your own buttons.

But if your only problem with using .oldvalue was combo's, you can go that route.  

<<Therefore, as the form control is only storing the ID value, the OldValue property is not much good.
What I really needed was to know the text that was in the unbound column that the user can see. And
you can't get the value of the text unless the control has focus.>>

  Ah, but you can.  Checkout the ItemData and column properties.

Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

NoggyAuthor Commented:
Thanks for your prompt reply, Jim.

Don't like my own buttons. What's the point when there are perfectly good ones already available? And I haven't got enough room really to put them on every form (as a subform).

Re ItemData etc:  <Homer-style> D'oh!! I actually use those all the time. I did actually think of using them but couldn't really be bothered to parse out the ColumnWidths to know which field is actually visible to the user (i.e. the value that the user sees in the text box and not in the multi-column dropdown).

******I'm sure that there must be a simple way to do this (besides writing your own parser). Is there one in-built or a property/collection that I can use? That would make it much better.******

It looks like this experience for me is one of those "hit a wall, find a road but you find out later that it just takes you down a cul-de-sac when someone points out that the original 'wall' was just a door whose key you had lost". I can get very blinkered at times....but so can't everyone :-) ?

Thanks again for your help. Can you just hopefully answer the *'d question above. You can then post as an answer and I'll award you the points.

NoggyAuthor Commented:
Hi JDettman,

Sorry for not replying earlier. I have now modified my class to use the .OldValue and it all works a dream. Thanks for your help. All it needed I think was for me to persevere. I actually completed it a few weeks ago but had forgot to tell you.

If you post an answer, I will give you the points.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Not a problem.  Glad to hear everything is working fine.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NoggyAuthor Commented:
I will post the code up here in a little while for those that are paying to view this question in the future....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.