Rob4077
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 Id) 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_Er ror
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 mJobDetail s").Form.R ecordset.F indFirst "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_Er ror:
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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!
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!
ASKER
What I need is some way of stopping the Current even firing again after the After update event has finished its execution.