Prevent Duplicate ID, Then go to the record that has that ID

[dee]

I can prevent a duplicate ID from being entered, but can't figure out
how to go to the record with that already existing ID. Could someone
help? Thanks in advance.

Private Sub ID_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("ID", "contacts", _
"ID = " & Me!ID)) Then

MsgBox "ID Number Already Exists"
Dim Tid As String
Tid = ID
Me.Undo
Cancel = True

DoCmd.GoToRecord , , acFirst
With Forms!FmLeads.RecordsetClone
.FindFirst "ID = " & Tid 'Me!ID

If Not .NoMatch Then

'Bookmark = .Bookmark
End If

End With

'DoCmd.GoToRecord , , acFirst
'Cancel = True

'Me.Undo
'Cancel = True

End If

On Error GoTo Err_ID_BeforeUpdate
Exit_ID_BeforeUpdate:
Exit Sub

Err_ID_BeforeUpdate:

Resume Exit_ID_BeforeUpdate
End Sub

[Marshall Barton]

It sounds like you are using a bound text box for this
purpose. If so, the current record is being modified,
surely not something you intended. A search text box really
should be unbound and in a different section from the data
bound controls (the form header is commonly used).

Your code should include an else block in the check for
NoMatch. If a match is not found then go to a new record.
--
Marsh
MVP [MS Access]
dee wrote:

>I can prevent a duplicate ID from being entered, but can't figure out
how to go to the record with that already existing ID. Could someone
help? Thanks in advance.

Private Sub ID_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("ID", "contacts", _
"ID = " & Me!ID)) Then

MsgBox "ID Number Already Exists"
Dim Tid As String
Tid = ID
Me.Undo
Cancel = True

DoCmd.GoToRecord , , acFirst
With Forms!FmLeads.RecordsetClone
.FindFirst "ID = " & Tid 'Me!ID

If Not .NoMatch Then

'Bookmark = .Bookmark
End If

End With

'DoCmd.GoToRecord , , acFirst
'Cancel = True

'Me.Undo
'Cancel = True

End If

On Error GoTo Err_ID_BeforeUpdate
Exit_ID_BeforeUpdate:
Exit Sub

Err_ID_BeforeUpdate:

Resume Exit_ID_BeforeUpdate
End Sub