How to prevent to enter duplicate record?

[MP]

I need a code in the Before Update event procedure, which prevents to
enter duplicate record in a form.

I have the main formX, which takes values from the table named tblX and
a subformY, which takes values form the table named tblY. Both forms
are mutually related with a key CustomerID.

I need a code, which makes:
If is field named txtX in formX AND the field named txtY in the
subformY are equal, or entered before, then generate a massage. The
message has to be generated when the field named txtY in the subformY
is already entered.

Thanks for helping me, Mojca

[MP]

Example:

If the first filed named txtX = 789/05 - formX And
If the secon field named txtY = 07.09.2005 - subformY, then generate a
massage "The record you have entered is a duplicate".

[dkintheuk]

You make no sense, sorry but the example you post seems meaningless to
me, anyone else got it?

Are you saying that you want to make it illegal for the data in afield
in a related table to have the same value as a similar named field in
the parent table?

If so you could code this...

private sub block_txtY()

if me.txtY = [forms].[frmX].[txtX] then
msgbox "That value is not allowed", vbokonly
me.txtY = ""
end if

end sub

[MP]

Unfortunately not working.
Next time when I entered the same data in a form:
in the first field txtX = 789/05 - formX And
in the secon field txtY = 07.09.2005 - subformY, then
generate a massage "The record you have entered is a duplicate".

[MP]

And SQL for find duplicare record is:
SELECT First(FrmX.TxtX) AS Patient, First(FrmY.TxtY) AS Date,
Count(FrmX.TxtX) AS NumberOFduplicateTxtX, FrmX.TxtX AS FrmX, FrmY.TxtY
AS Dates, Count(FrmY.TxtY) AS NumberOFduplicateTxtY
FROM FrmX INNER JOIN FrmY ON FrmX.ID_Patient = FrmY.ID_Patient
GROUP BY FrmX.TxtX, FrmY.TxtY
HAVING (((Count(FrmX.TxtX))>1) AND ((Count(FrmY.TxtY))>1));