E
esn
Please help if you can, I've spent way too much time battling this
error.
I have a main form with a subform designed to help users associate
records when typos or other issues prevent the records from being
associated automatically. Subform records are suggested matches for
the record in the parent form, and there are buttons to insert the
"Individual ID" from the subform, or to assign a new ID. There are a
couple other buttons that open copies of the same main form, but with
different buttons and different criteria for the suggested matches.
After the ID is filled in for the record in the main form, a query
runs to check all the other records that need IDs, and if they are a
perfect match for the current record, assign the same ID. This way
users don't have to deal with multiple instances of the same
individual over and over again.
In order to make the process faster I've added code to all of the
buttons that pushes the user forward to the next record once they've
assigned an ID to the current one. If the query has already populated
the "Individual ID" field for the new current record, I want the user
to be pushed forward again. So I put some code in the main form's
OnCurrent event that checks if the Individual ID is null, and if not
moves to the next record. Here is where the problem arises. Any time
the user is pushed through multiple records, the code executes
perfectly, advancing through the records until it reaches one that
needs an Individual ID. But once it gets there, it pops up a message
box for error 2105 - can't go to selected record. If I hit the debug
button, it will take me to the DoCmd.GotoRecord line of the code that
first began the series of events - either the oncurrent event or one
of the command buttons. No matter what error handling I try to
implement I seem to get the error, even though everything is working
as I want it to.
This behavior seems odd for many reasons, but the things that are
bothering me the most are:
When a button causes the chain of events, why does the error refer to
the first code that was triggered, when the DoCmd.GotoRecord line must
have been executed successfully for the oncurrent event to have fired?
Why is Access still trying to move to the next record when the current
record doesn't satisfy the criteria in the code?
Assuming for some reason Access is ignoring the criteria in the code
and trying to move to a new record, what's stopping that operation?
An important thing to note - I'm not at the end of the recordset, i.e.
there are still more records left to move on to.
Here's the code:
Private Sub Form_Current()
On Error GoTo 0
' Cancel move to next if shift key is pressed
If IsShiftKeyDown() Then
Exit Sub
End If
' If ID is assigned and current record isn't the last one,
' move to the next record
If Not IsNull(Me.Individual_ID) Then
If Me.CurrentRecord < Me.RecordsetClone.RecordCount Then
DoCmd.GoToRecord , , acNext
Exit Sub
Else
' If ID is assigned and this is the last record, display
msg
MsgBox "There are no more records that need IDs.", , "The
End"
Exit Sub
End If
End If
End Sub
Private Sub InsertID_Click()
' Make sure there's a record selected and insert ID
If DCount("*", "1MarkMatch") = 0 Then
MsgBox "No Record Selected", vbOKOnly, "Can't Insert ID"
Exit Sub
Else
Me.Individual_ID = Me.IDSubform.Individual_ID
Me.Dirty = False
End If
' Update matching records that still need IDs
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateMatching"
DoCmd.SetWarnings True
' If more records remain move to next, otherwise display message
If Me.CurrentRecord < Me.RecordsetClone.RecordCount Then
DoCmd.GoToRecord
Exit Sub
Else
MsgBox "There are no more records that need IDs.", , "The End"
End If
End Sub
Thanks so much to anyone brave enough to take a look at this for me.
error.
I have a main form with a subform designed to help users associate
records when typos or other issues prevent the records from being
associated automatically. Subform records are suggested matches for
the record in the parent form, and there are buttons to insert the
"Individual ID" from the subform, or to assign a new ID. There are a
couple other buttons that open copies of the same main form, but with
different buttons and different criteria for the suggested matches.
After the ID is filled in for the record in the main form, a query
runs to check all the other records that need IDs, and if they are a
perfect match for the current record, assign the same ID. This way
users don't have to deal with multiple instances of the same
individual over and over again.
In order to make the process faster I've added code to all of the
buttons that pushes the user forward to the next record once they've
assigned an ID to the current one. If the query has already populated
the "Individual ID" field for the new current record, I want the user
to be pushed forward again. So I put some code in the main form's
OnCurrent event that checks if the Individual ID is null, and if not
moves to the next record. Here is where the problem arises. Any time
the user is pushed through multiple records, the code executes
perfectly, advancing through the records until it reaches one that
needs an Individual ID. But once it gets there, it pops up a message
box for error 2105 - can't go to selected record. If I hit the debug
button, it will take me to the DoCmd.GotoRecord line of the code that
first began the series of events - either the oncurrent event or one
of the command buttons. No matter what error handling I try to
implement I seem to get the error, even though everything is working
as I want it to.
This behavior seems odd for many reasons, but the things that are
bothering me the most are:
When a button causes the chain of events, why does the error refer to
the first code that was triggered, when the DoCmd.GotoRecord line must
have been executed successfully for the oncurrent event to have fired?
Why is Access still trying to move to the next record when the current
record doesn't satisfy the criteria in the code?
Assuming for some reason Access is ignoring the criteria in the code
and trying to move to a new record, what's stopping that operation?
An important thing to note - I'm not at the end of the recordset, i.e.
there are still more records left to move on to.
Here's the code:
Private Sub Form_Current()
On Error GoTo 0
' Cancel move to next if shift key is pressed
If IsShiftKeyDown() Then
Exit Sub
End If
' If ID is assigned and current record isn't the last one,
' move to the next record
If Not IsNull(Me.Individual_ID) Then
If Me.CurrentRecord < Me.RecordsetClone.RecordCount Then
DoCmd.GoToRecord , , acNext
Exit Sub
Else
' If ID is assigned and this is the last record, display
msg
MsgBox "There are no more records that need IDs.", , "The
End"
Exit Sub
End If
End If
End Sub
Private Sub InsertID_Click()
' Make sure there's a record selected and insert ID
If DCount("*", "1MarkMatch") = 0 Then
MsgBox "No Record Selected", vbOKOnly, "Can't Insert ID"
Exit Sub
Else
Me.Individual_ID = Me.IDSubform.Individual_ID
Me.Dirty = False
End If
' Update matching records that still need IDs
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateMatching"
DoCmd.SetWarnings True
' If more records remain move to next, otherwise display message
If Me.CurrentRecord < Me.RecordsetClone.RecordCount Then
DoCmd.GoToRecord
Exit Sub
Else
MsgBox "There are no more records that need IDs.", , "The End"
End If
End Sub
Thanks so much to anyone brave enough to take a look at this for me.