Duplicate validation in VBA SQL

[foxy]

Hi,

I have an unbound form in Access 2007, where the users have to enter person details and then also an alternate ID from another source system in a free text box.

When they click save it runs a DoCmd.RunSQL INSERT INTO statement and commits the data to the table.

How can I get it to check if the alt_ID that has been entered already exists in the person table? I want to display a helpful error message if the alt_ID already exists. The standard access error message would not be helpful to users.

Cheers
Foxy

 

[khawar]

You can use dlookup to check whether this id already exist

 

[foxy]

How would I go about doing that?

Cheers

 

[DCrake]

If the form is a bound form use the Before update, if not use the after update event of the control

Dim Exists As Long

Exists = Nz(DLookup("[KeyFieldHere]","TableNameHere","[SearchField]=" & AltID),0)

Check to see if Exists has a value. If no value was found then Exists will equal 0

If Exists <> 0 Then
   MsgBox AltID & " already exists in the database"
End If

Or

If Nz(DLookup("[KeyFieldHere]","TableNameHere","[SearchField]=" & AltID),0) <> 0 Then
   MsgBox AltID & " already exists in the database"
End If

David

 

[foxy]

That worked perfectly. Thanks very much!