Prevent an OnCurrent event from repeating

Posted on 2016-07-19
Last Modified: 2016-07-27
Sorry about the lengthy question. I will summarize the question first and then provide details of how I am getting there.

I have a form with two subforms. The main form displays Job details, one subform is a datasheet view of associated activities (DetailId's), the third displays all the data for the selected DetailId. When the user points to different records on the datasheetview the link works fine and the required detailId record is displayed on the other subform. However if the user selects a DetailId from a combo box on the main form, the associated Job is found, the datasheet view displays correctly, but I can't get the pointer to point to the selected DetailId. It just goes back to the first record in the list

If it's something you can help, here are the more technical details

The main form displays the Job address related details from tblJobs, It also has 3 combo boxes used to search either a JobNum, Address or DetailId. It has the following OnCurrent event that merely serves as an alert
Private Sub Form_Current()
    If IsNull(Me.Customer) And Me.NewRecord = False Then
        MsgBox "Please note this Job is not assigned to a customer", vbCritical, "Customer Should Be Nominated"
    End If
End Sub

One of the subforms is a datasheet view of a related table called tblJobDetails listing all activities required for the selected job displayed on the main form. It is linked to the mainform with the Link Master/Child link on the JobNumber.
When a new record is selected from this list the following OnCurrent Event is triggered:
Private Sub Form_Current()
    Dim lRequiredDetailId As Long
    If Not IsNull(Me.Parent.cboDetailId) Then  'i.e. this has been triggered because the user has selected a DetailId in the combo box
        lRequiredDetailId = Me.Parent.cboDetailId
    Else  'This is being triggered because the user has clicked on a differen Detail record in the datasheet of the other subform
        lRequiredDetailId = Nz(Me.DetailId, 0)
    End If
    Me.Parent.txtDetailid = lRequiredDetailId   'Record the currently selected DetailId on the main form so the link to the subform works
End Sub

The other subform displays all the details of the selected Activity and is linked to the main form on a field called Detail Id. It has the following OnCurrent event whose function is simply to clear an unbound field whenever a new record is selected

Private Sub Form_Current()
    Me.txtSMS = Null 'SMS is an unbound field
End Sub

when the user enters a new DetailId in the combo box on the form the following event is triggered.
Public Sub cboDetailId_AfterUpdate()
   On Error GoTo cboDetailId_AfterUpdate_Error
    Me.Recordset.FindFirst "JobNum = '" & Me.cboDetailId.Column(1) & "'"   'Find the JobNum associated with the selected DetailId
    If Me.Recordset.NoMatch = True Then
        MsgBox "Can't find a job that matches your DetailId", , "Can't Find"
    End If
    Me.cboFindJob = Null   'clear the value of the selection combo box
    Me.cboFindStreet = Null    'clear the value of the selection combo box
    Me.cboDetailId = Null    'clear the value of the selection combo box
    'if the DetailId selected in the combo box is associated with a valid jobnum the main form should now be displaying the record
    'So now I need to move the record pointer on the subform with the datasheet view to the selected DetailId
    Forms("frmJobs").Form("sfrmJobDetails").Form.Recordset.FindFirst "DetailId = " & Me.txtDetailid
    'Having found the record, the Link should make sure the required activity is being displayed on the other subform      
   Exit Sub

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboDetailId_AfterUpdate of VBA Document Form_frmJobs"
End Sub

This works fine when I move the record pointer on the datasheet view subform. However the problem I am having is that, theOnCurrent event on the datasheet view subform is being activated multiple times when I enter a DetailId in the combo box. the first 3 times it recognises the link and goes to the right record but after the combo box AfterUpdate event has finished running the datasheet subform fires another two times and since the combobox has been set to null by the now finished AfterUpdate event, the pointer changes back to the first record in the list instead of the selected one.

I don't understand why that event is being triggered so often, but more to the point, how to make sure the record pointer stops at the desired record. Any ideas would be welcome.
Question by:Rob4077
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 41720907
You don't really have any control over when Access fires an event, so there's not much you can do about that other than to be sure your logic is sound.

If your trouble is that an event should NOT fire if the value in txtDetailID is Null, then just put a check in the code that calls for that box:

If Not Nz(Me.txtDetailID, "") = "" Then
   Forms("frmJobs").Form("sfrmJobDetails").Form.Recordset.FindFirst "DetailId = " & Me.txtDetailid
End If

Author Comment

ID: 41721151
Thanks Scott. I am satisfied that the logic works because after the cboDetailId_AfterUpdate() event has finished everything is where it should be. The problem is that the datasheet view subform current event fires twice after that and since the after update event has already reset the combo box, the second execution of the current event moves the pointer back to the first record instead of leaving it where I want it.

What I need is some way of stopping the Current even firing again after the After update event has finished its execution.
LVL 36

Assisted Solution

PatHartman earned 125 total points
ID: 41721313
Access forms work the way they work and you cannot change that as Scott said.  I didn't examine your code in detail since the procedure was too lengthy but moving focus back and forth between the subform(s) and main form can result in the current events firing multiple times.  Rather than relying on the combo box, perhaps you should store the value in a hidden field that doesn't get cleared and use that to control positioning.
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 total points
ID: 41721449
another way to prevent code in the current event from firing is to add a static variable to that event.

Static MyID as long

Assuming you have an ID field that is an autonumber field or is the PK of the table which populates the form.  Then you would simply test to see whether MyID = me.txt_ID and if it is, you would simply exit the event.
Private Sub Form_Current()

    Static MyID as long

    if NZ(me.txt_ID, 0) = MyID Then Exit Sub
    MyID = NZ(me.txt_ID, 0)

'the reset of your current code goes here.

End Sub

Open in new window

LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 41721962
I am satisfied that the logic works
But it doesn't, else you wouldn't have to be concerned about the Current event firing multiple times.

In almost every case, this is caused by trying to force Access to do something it does not normally do. When dealing with Subforms, Access will always save the mainform record when you move into the Subform, and it will always save both records when you move out of the subform. you can cause these actions to fire with code, of course, and you can do so inadvertently.

When I run across these sorts of things, I find it a good idea to set a breakpoint as early in the process as possible, and then execute the actions that cause the issue. I then follow through the code line by line to determine if it actually fires in the order I expect it to. As often as not, I find that I've missed something, or that I've made an incorrect assumption about the way things should work.

Author Comment

ID: 41726486
Sorry, definitely not abandoned. Just got caught up in the time differences between Australia and USA as well as a long weekend away.

I have taken all the comments on board and wanted to try a few options that are contemplated by the suggestions in case more clarification is needed. I will definitely get back to this, close it and award the points.

Author Comment

ID: 41730950
Thanks to everyone who posted, especially Scott who reminded me that it doesn't matter how logical I think my code is, if it doesn't work it means I've "made an incorrect assumption about the way things should work".

I tried several different options incorporating the many suggestions offered but in the end, the only way I could get it to work was change the link to my Detail view subform then apply a further filter to display the record I wanted. Slows down the form a bit and makes it jerky but at least it works. If I get time when I go back and tidy up I may revisit this and see if I can find a better way.

Thanks again to all!

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2010 change CurrentUser 5 37
Importing Excel file into Access 5 31
Email Populated Report as PDF 2 15
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

820 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