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

 

[Guest]

You are on the right track, but you can simplify the code. Firstly though,
your code is ambiguous about the data type of ID; you declare the Tid
variable as String data type, but the lack of delimiting quotes around the ID
values in the code suggests it’s a number data type. If it’s a number try
this:

Private Sub ID_BeforeUpdate(Cancel As Integer)

Dim rst As Object
Dim Tid As Long

Set rst = Me.Recordset.Clone

Tid = Me.ID

If Not IsNull(DLookup("ID", "Contacts", "ID = " & Tid)) Then
MsgBox "ID Number Already Exists", vbExclamation, "Warning"
Cancel = True
Me.Undo

rst.FindFirst "ID = " & Tid
Me.Bookmark = rst.Bookmark
End If

End Sub

If it’s a string data type try this:

Private Sub ID_BeforeUpdate(Cancel As Integer)

Dim rst As Object
Dim Tid As Sring

Set rst = Me.Recordset.Clone

Tid = Me.ID

If Not IsNull(DLookup("ID", "Contacts", "ID = """ & Tid & """")) Then
MsgBox "ID Number Already Exists", vbExclamation, "Warning"
Cancel = True
Me.Undo

rst.FindFirst "ID = """ & Tid & """"
Me.Bookmark = rst.Bookmark
End If

End Sub

One thing to be aware of is that in a multi-user environment this is not
bullet-proof as if two users enter the same ID simultaneously it won't be
picked up as a duplicate, so you should index the field uniquely. This will
raise a data error when the second user attempts to save the new record,
which you can either leave the system to deal with by popping up an error
message, or you can handle it yourself in the form's Error event procedure.

Is there some reason why you can't simply use an autonumber ID BTW?

Ken Sheridan
Stafford, England

 

[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.

 

[Guest]

Marsh:

I think you are misreading the post; it’s a validation routine, not a search
routine. The navigation is to take the user to the existing row with the
existing ID if its duplicated when attempting to insert a new row.

Ken Sheridan
Stafford, England

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]

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]

I think you are right. Thanks for clarifying it for me.