Indexed record ID if it exists alert

[Hanksor]

How can I create an alert the tells the user that the account ID they have
just entered already exists? I have the field in the table set to "Indexed
Yes, No Duplicates". But I would like to be able to have them alerted when
they leave the text box if the ID exists. Any help will be appreciated.

Thanx,

Hanksor

 

[John Vinson]

How can I create an alert the tells the user that the account ID they have
just entered already exists? I have the field in the table set to "Indexed
Yes, No Duplicates". But I would like to be able to have them alerted when
they leave the text box if the ID exists. Any help will be appreciated.

You can use the BeforeUpdate event of the ID textbox for this purpose:

Private Sub txtID_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[ID]", "

", "[ID] = " & Me!txtID) Then
Cancel = True
MsgBox "This ID already loaded", vbOKOnly
End If
End Sub

John W. Vinson[MVP]

 

[hanksor]

I had some luck with the code, but only with ID's that are of integer in
nature. I can't get it to work with ID's that are alpha/numeric. Any
thought what I might be able to do? Also, I found a parentheses missing in
the Dlookup in case anyone else tries this.

How can I create an alert the tells the user that the account ID they have
just entered already exists? I have the field in the table set to "Indexed
Yes, No Duplicates". But I would like to be able to have them alerted when
they leave the text box if the ID exists. Any help will be appreciated.

You can use the BeforeUpdate event of the ID textbox for this purpose:

Private Sub txtID_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[ID]", "

", "[ID] = " & Me!txtID) Then
Cancel = True
MsgBox "This ID already loaded", vbOKOnly
End If
End Sub

John W. Vinson[MVP]

 

[John Vinson]

I had some luck with the code, but only with ID's that are of integer in
nature. I can't get it to work with ID's that are alpha/numeric. Any
thought what I might be able to do? Also, I found a parentheses missing in
the Dlookup in case anyone else tries this.

Sorry about the missing )! For Text ID's you need the syntactically
required quotes:

If Not IsNull(DLookUp("[ID]", "

", "[ID] = '" & Me!txtID & "'"))
Then


John W. Vinson[MVP]

 

[Hanksor]

Thanks John!!!! Works great. Your help has saved me a lot of
hours..........

I had some luck with the code, but only with ID's that are of integer in
nature. I can't get it to work with ID's that are alpha/numeric. Any
thought what I might be able to do? Also, I found a parentheses missing in
the Dlookup in case anyone else tries this.

Sorry about the missing )! For Text ID's you need the syntactically
required quotes:

If Not IsNull(DLookUp("[ID]", "

", "[ID] = '" & Me!txtID & "'"))
Then


John W. Vinson[MVP]