Solved

Indentifying pressed Navigation Button

Posted on 2002-03-20
7
289 Views
Last Modified: 2012-05-04
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.

WHAT I WANT:
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?
0
Comment
Question by:Noggy
  • 4
  • 3
7 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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).

HTH,
Jim.
0
 
LVL 4

Author Comment

by:Noggy
Comment Utility
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.

Regards

Noggy
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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.

Jim.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 4

Author Comment

by:Noggy
Comment Utility
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.

Noggy
0
 
LVL 4

Author Comment

by:Noggy
Comment Utility
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.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
Comment Utility
Not a problem.  Glad to hear everything is working fine.

Jim.
0
 
LVL 4

Author Comment

by:Noggy
Comment Utility
I will post the code up here in a little while for those that are paying to view this question in the future....
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now