Solved

Prevent an OnCurrent event from repeating

Posted on 2016-07-19
7
52 Views
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

cboDetailId_AfterUpdate_Error:
    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.
0
Comment
Question by:Rob4077
7 Comments
 
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
0
 

Author Comment

by:Rob4077
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.
0
 
LVL 34

Assisted Solution

by:PatHartman
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.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

0
 
LVL 84

Accepted Solution

by:
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.
0
 

Author Comment

by:Rob4077
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.
0
 

Author Comment

by:Rob4077
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!
0

Featured Post

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).

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

708 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

19 Experts available now in Live!

Get 1:1 Help Now