Go Premium for a chance to win a PS4. Enter to Win


Prevent an OnCurrent event from repeating

Posted on 2016-07-19
Medium Priority
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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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 40

Assisted Solution

PatHartman earned 500 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.
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 500 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 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

963 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