Indexed record ID if it exists alert

  • Thread starter Thread starter Hanksor
  • Start date Start date
H

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
 
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.
Click to expand...

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]
 
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.
John Vinson said:
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.
Click to expand...

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]
Click to expand...
 
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.
Click to expand...

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]
 
Thanks John!!!! Works great. Your help has saved me a lot of
hours..........
John Vinson said:
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.
Click to expand...

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]
Click to expand...
 
Back
Top