Avatar of Rob4077
Flag for Australia

asked on 

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.
Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon