duplicate Records

[Afsaneh]

Hi,
I have a special kind of key that is :
I wanna prevent duplicate value for my key .in addition I wanna allow null
values too. so I couldn't use a unique field in my table. I decided to
prevent duplicate values through my data entry form . first I check my value
with a record set to see If I had it before ,If not the record is ok,else way
the record won't be added. but some times this process fails to detect
duplicate values and even the procedure won't run.
unfortunately I couldn't detect the circumstances that this occers . I
appreciate any comment. tnx in advance. here is my code

Dim rst As DAO.Recordset

Set rst =
CurrentDb.QueryDefs("queryName").OpenRecordset(dbOpenDynaset)

'Search for a matching record
rst.FindFirst "trim([MyKey]) = " & Trim(Me![MyKey])
If Not rst.NoMatch Then
'If yes then
it is duplicted
Set rst = Nothing
GoTo Exit_cmdNewPersonel_Click
Else
The record is ok and it will be in the table
endif

 

[Ryan]

The best what to look for duplicate values is the DLookup or DCount function.
Here is a link that may help.
http://support.microsoft.com/kb/285866

 

[Afsaneh]

Dear Ryan
thank u so much for your care and support. I haven't test it in my program
yet, but as it looks it simplifies the procedure, and the article you
suggested has a thorough description.

The best what to look for duplicate values is the DLookup or DCount function.
Here is a link that may help.
http://support.microsoft.com/kb/285866

--
Please remember to mark this post as answered if this solves your problem.

Hi,
I have a special kind of key that is :
I wanna prevent duplicate value for my key .in addition I wanna allow null
values too. so I couldn't use a unique field in my table. I decided to
prevent duplicate values through my data entry form . first I check my value
with a record set to see If I had it before ,If not the record is ok,else way
the record won't be added. but some times this process fails to detect
duplicate values and even the procedure won't run.
unfortunately I couldn't detect the circumstances that this occers . I
appreciate any comment. tnx in advance. here is my code

Dim rst As DAO.Recordset

Set rst =
CurrentDb.QueryDefs("queryName").OpenRecordset(dbOpenDynaset)

'Search for a matching record
rst.FindFirst "trim([MyKey]) = " & Trim(Me![MyKey])
If Not rst.NoMatch Then
'If yes then
it is duplicted
Set rst = Nothing
GoTo Exit_cmdNewPersonel_Click
Else
The record is ok and it will be in the table
endif