Hello Jason, (and all other users, long time no see)
The kind of problem you will encounter very often.
What you could do is :
Fortunatly, this is something easily done because Delphi is awesome !!
You'll have to check BEFORE the insert is triggered.
To load the color abbreviation into a dataset, simply do something like :
procedure Form1_Button1_OnClick (Sender: TObject; var Cancel: boolean);
var
ColorList : TDataset; //to store the abreviation list
ColorAbrev : String; //to get user input
begin
//get the user input from the edit field on the form
ColorAbrev := Form1.edit1.text;
//load the list of existing abreviation to the ColorList dataset
SQLQuery('SELECT color_name FROM dbcolors',ColorList);
//try and match the database field (named color_name here), with the input variable
if ColorList.Locate('color_Name',ColorAbrev,0) then
begin
//warn the user with your own message
end else
begin
//execute the query to add the new record to the color table
end;
end;
The "locate" function will return -1 if no match is found but with the if / else structure, you are good as well.
I believe the 0 flag at the end corresponds to a case insensitive search, there might be other flags like 'partial find', but you'll have to ask Dmitry.
Hope this helps.
Cheers
Mathias
I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor