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.cboDetail
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_Er
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("sfr
'Having found the record, the Link should make sure the required activity is being displayed on the other subform
Exit Sub
cboDetailId_AfterUpdate_Er
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.
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.cboDetail
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_Er
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("sfr
'Having found the record, the Link should make sure the required activity is being displayed on the other subform
Exit Sub
cboDetailId_AfterUpdate_Er
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
Last Comment
Rob4077