We help IT Professionals succeed at work.
Get Started

Prevent an OnCurrent event from repeating

Rob4077
Rob4077 asked
on
362 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.
Comment
Watch Question
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This problem has been solved!
Unlock 4 Answers and 7 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE