Hello,

I have this program that assigns unique number (JEVNO) to each record
added in a table (JEVS). it's not an auto-increment field, its a
varchar type assigned by the client program, defined unique in the
table. When more than one users are using the program at the same
time, it is always possible that they generate identical JEVNO
numbers, so the program throws an EIB_ISCError ERRCODE 335544665. The
user beaten to using the contested number can manually change the
number, then save. It works, but rather inconvenient. I was trying to
use tib_query.OnError event, but it seems I am not permitted to do a
Post inside the onerror event. My OnError code is shown below. What I
am trying to do is autoresolve the number conflict then save without
the user having to change it manually. What is the better way to do this?

TIA
Jeff

[code starts here]
procedure Tdm_jevs.qrJevsError(Sender: TObject; const ERRCODE: Integer;
ErrorMessage, ErrorCodes: TStringList; const SQLCODE: Integer;
SQLMessage, SQL: TStringList; var RaiseException: Boolean);
begin
if e.ERRCODE = 335544665 then
// violation of PRIMARY or UNIQUE KEY
if MessageBox(0, 'Duplicate JEV Number found.'+#13+#10+
'Would you like the program to automatically resolve the problem?',
'Duplicate JEV Number',
MB_ICONQUESTION or MB_YESNO or MB_DEFBUTTON1) = idYes then
begin
with TIB_DSQL.Create(nil) do
try
sql.Add('select max(JEVNO) JEVNO from jevs where jevperiod='+
quotedstr(GetCurrentPeriod));
try
Execute;
newnumber := FieldValues['JEVNO'];
IncJevNo(newnumber);
qrJevs.FieldByName('JEVNO').asstring := newnumber;
qrjevs.Post;
// the Post never gets executed
// Shows message 'Record is not inserted.' instead
except
showmessage('Auto-resolve failed.'#13#10'Please change JEV
number manually.');
abort;
end
finally
free;
end;
end;
RaiseException := false;
end;
[code ends here]


[table DDL, just in case]
CREATE TABLE JEVS(
ID Integer NOT NULL,
JOURNALTYPE Varchar(4) NOT NULL,
JEVNO Varchar(11) NOT NULL,
JEVDATE Date DEFAULT 'now' NOT NULL,
JEVPERIOD Varchar(6) NOT NULL,
REFNO Varchar(20),
REFNO2 Varchar(15),
TOTALDR DM_MONEY DEFAULT 0.00,
TOTALCR DM_MONEY DEFAULT 0.00,
PARTICULARS Varchar(240),
ENTRYDATE Date DEFAULT 'now',
VOUCHERNO Varchar(11),
CHECKNO Varchar(10),
POSTED Char(1) DEFAULT '0' NOT NULL,
CONSTRAINT PK_JEVS PRIMARY KEY (ID),
CONSTRAINT UNQ_JEVS_JEVNO UNIQUE (JEVNO)
);