Prevent an OnCurrent event from repeating

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.
Who is Participating?
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Rob4077Author Commented:
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.
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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Dale FyeOwner, Dev-Soln LLCCommented:
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

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

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
Rob4077Author Commented:
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.
Rob4077Author Commented:
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!
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.