Data Integrity Violation

[Guest]

Hi All
I have a relation 1:N between tables and I use a form and a subfor
to insert new data for those tables (it is like a relation betwee
a book title and its N editors); subform is used to insert editor'
name, selected from a combobox (cbx) (fixed list of value)

The following piece of code is used to check if an editor name ha
been already associated to a book title.
The code check for duplicated values on the "N-side" of the relation

The code I wrote works fine for existing records: if I try to chang
an editor name and it is already associated to the book, it catches i
and undo the operation (see the statement: Me.cbxName.Undo)

It does NOT work well when I insert NEW records: I mean that
code catch the duplicated value but, after that, Access prompts m
for a data integrity violation; it seems to me that the undo operatio
does not work properly (i.e., the statement: Me.cbxName.Undo) because
there is not an "oldValue" for the field

Can anyone suggest me an hint/workaround to solve this problem
Thanks in advance
Robert

Private Sub FiltroSpecialita_BeforeUpdate(Cancel As Integer
Dim res As Intege

'Check if new value changed or old value is null (only for addRecord)..
If IsNull(Me.cbxName.OldValue) Or Me.cbxName.OldValue <> Me.cbxName The

'check if new value is duplicated..
res = DCount("IDAtlSpec", "AtletixSpecialita", "IDSpecialita='" & Me.cbxName & "' and IDAtleta=" & Me.IDAtleta

If res > 0 The
MsgBox "Duplicated value", vbCritical + vbOKOnl

Me.cbxName.Undo 'undo modificatio

Cancel = Tru

End I
End I

 

[Guest]

Well, I answer to myself since I did not get any help: I hop
it will help someone else
Here is the solution

In case a newly created record is duplicated
the simple command "Me.cbxName.Undo" is not enough
since it does not delete the newly created record: then
the following piece of code is required

If Me.NewRecord The
Me.Undo /* Undo record creation *
Els
Me.cbxName.Undo /* Undo record value change *
End I

Roberto