The above works well for a new record but it blocks any update on existing records considering an edit as a systemic duplicate.

My form has a double function of inserting new entries and displaying existing records so, the "show record" and "new record" do not behave as expected, especially the "show record" that requires to double-clic twice (4 clicks) to retrieve the current id/generated id.

If someone has the same kind of issue, here is the fruition of a long battle and some headaches.

The key is in the operator != which compares the values left and right and returns a false instead of the <> which returns a true and with SQL all conditions must be true to get a true result. If at least one condition is false then the result could be either false or incomplete, This is why it did not worked for updates since the result was always true, hence duplicate alert. In a new record, the ids old (-1) and new (next id number) are different while when updating, the ids are the same. SQLite creates internally an "old.id" and a "new.id" before each transaction, those can also be used in triggers and are used for internal rollback.

procedure Form_SaveButtonName_OnClick (Sender: string; var Cancel: boolean);
var id: integer;
begin

  if  (SQLExecute ('SELECT COUNT(id) FROM table WHERE (column COLLATE NOCASE ="'+Form.TextBox.Text+'") and (id!="'+inttostr(Form.SaveButtonName.dbGeneralTableId)+'")')) > 0 then
     begin  // Replace <Record> by proper descriptive field
          MessageDlg('This <Record> already exists:  ' +Form.TextBox.Text +#13+'Entry canceled', mtError, mbOk, 0); // Displays a red 'X', header is: Error
          Cancel := True;
          end
          else
          begin
           MessageDlg('This <Record> has been saved:  ' +Administration.Administr_WinID.Text, mtInformation, mbOk, 0); // Displays an Info icon header is: Information
          Cancel := False;
     end;

end;

That is what the collate nocase does!

Search through LIKE in the program is case-insensitive.
And searching without "%" at the end and at the beginning will allow you to search for a complete match.
For spaces at the end and beginning of text, use TRIM. It’s better if it’s still at the stage of entering into EDIT.
Unfortunately, nothing will protect against extra spaces inside the text, commas, periods, apostrophes, etc.
Of course, all this can be processed.
As for COLLATE, there are some restrictions that do not always allow its use. And you only forcefully turn it on.
There are other options for limiting duplicate entries.

It worked the other way around, I tested it but you are right, it looks more logical your way.

Now, I have to find a way to check on the vicious duplicates, the one mixing lower case and uppercase like: HR ..... Hr ..... hr ....  hR ...., Human Resources..... human resources  .... HUMAN RESOURCES ....  those are all duplicates!!!

procedure Form1_Button1_OnClick (Sender: TObject; var Cancel: boolean);
begin
  if DupliChk(Form1.Edit1.dbTable,Form1.Edit1.dbField,Form1.Edit1.Text) then
  begin   //replace object by self-explanatory text
    MessageDlg('This object already exists:  ' +Form1.Edit1.Text +#13+'Entry cancelled', mtError, mbOk, 0); // Displays a red 'X', header is: Error
    Cancel := True;
    Form1.Edit1.Clear;
  end;
end;

function DupliChk (sTable, sField, sValue: string;): boolean;
begin
  if (SQLExecute ('SELECT Count(*) FROM '+sTable+' WHERE upper('+sField+') = upper("' + sValue + '")') = 0) then
      result := False else result := True;
end;

function DupliChk (sTable, sField, sValue: string;): boolean;
var d: string;
begin

    d := SQLExecute ('SELECT Count(*) FROM '+sTable+' WHERE '+sField+' COLLATE NOCASE = "' + sValue + '"');
    if StrToInt(d) > 0 then result := True else result := False;
end;

It works until someone tries to cheat it with a space before or after the entry...

Now, I have to find a way to check on the vicious duplicates, the one mixing lower case and uppercase like: HR ..... Hr ..... hr ....  hR ...., Human Resources..... human resources  .... HUMAN RESOURCES ....  those are all duplicates!!!

I transformed your solution into a function as it is easier when used repeatedly. It works perfectly when using a form that was not called by a "New Record" button.

function DupliChk (sTable, sField, sValue: string;): boolean;
var d: string;
begin
    result := False;
    d := SQLExecute ('SELECT Count(*) FROM '+sTable+' WHERE '+sField+' = "' + sValue + '"');
    if StrToInt(d) > 0 then result := True;
end;

If I understand correctly, then you instantly changed the answer (False)
 
Do it in this way

function DupliChk (sTable, sField, sValue: string;): boolean;
var d: string;
begin
    d := SQLExecute ('SELECT Count(*) FROM '+sTable+' WHERE '+sField+' = "' + sValue + '"');
    if StrToInt(d) > 0 then result := True else result := False;
end;

function DupliChk (sTable, sField, sValue: string;): boolean;
var d: string;
begin
    result := False;
    d := SQLExecute ('SELECT Count(*) FROM '+sTable+' WHERE '+sField+' = "' + sValue + '"');
    if StrToInt(d) > 0 then result := True;
end;

And the usage is:

procedure Form_ButtonSave_OnClick (Sender: string; var Cancel: boolean);
begin

   if DupliChk('Table','Column',Form.EditBox.Text) then
     begin   //replace object by self-explanatory text
          MessageDlg('This object already exists:  ' +Form.EditBox.Text +#13+'Entry cancelled', mtError, mbOk, 0); // Displays a red 'X', header is: Error
          Cancel := True;
          Form.EditBox.Clear;  
     end;
end;

It actually does not work with the action 'SaveRecord'. I have attached an example of what I am trying to achieve.

I want a very simple way of checking duplicates in a handful of tables in the same way as the example.

procedure Form1_Button1_OnClick (Sender: string; var Cancel: boolean);
begin
  if (SQLExecute ('SELECT COUNT(id) FROM HardWDType WHERE Kind="'+Form1.Edit1.Text+'"') > 0) then
  begin
    MessageDlg('This Kind is already in use: ' +Form1.Edit1.Text, mtError, mbOk, 0); // Displays a red 'X', header is: Error
    Cancel := True;
  end;
end;

I want a very simple way of checking duplicates in a handful of tables in the same way as the example.

I am using Dmitry example up in the post.

Action='SaveRecord'  -  occurs when saving

tcoton wrote:

This method works only when using a "New Record" button, it does not work with a "Save Record" button.

I tried to add the Action='SaveRecord' but it might be another string sent when using a "Save Record" button:

..[snip]....if (Action = 'NewRecord') or (Action='SaveRecord') then....[snip]....

Does anyone knows what the string sent is?

?????

I am using Dmitry example up in the post.

This method works only when using a "New Record" button, it does not work with a "Save Record" button.

I tried to add the Action='SaveRecord' but it might be another string sent when using a "Save Record" button:

..[snip]....if (Action = 'NewRecord') or (Action='SaveRecord') then....[snip]....

Does anyone knows what the string sent is?

?????