Check for duplicate in one part of a table

[Khalil Handal]

Hi to all,
I have a query qryPersoneels based on table tblPersonnels.
A form frmPersonnels is based on the query as a Record Source with Filter [DateDeparted] IS NULL (Only part of the table rows are slelected). When I have a new person entered on the form I know if it is a duplicate or not. How can I check if this new person has a duplicate in the rows of the table where [DateDeparted] IS NOT NULL ?

Khalil

[ranman256]

iF isnull(DLookup(...) ) then ‘ no duplicate

[CJ_London]

use the dcount function to look up the name. If it returns 0, there is no duplicate, otherwise it will return 1 or more depending on number of duplicates. Perhaps use the control beforeupdate event

if dcount("*","tblPersonnel","[DateDeparted] IS NOT NULL AND personname='" & txtPersonName & "'")>0 then
    msgbox "duplicate exists"
else
    'carry on
end if

[Khalil Handal]

Hi,
I am having error 2471 because a duplicate is defined by a card number. Each person has a unique card number
What adjustments need to be done ?
I tried this

If DCount("*", "tblPersonnel", "[DateDeparted] IS NOT NULL AND CardNumber=" & txtyCardNumber) > 0 Then

Khalil

[CJ_London]

names need to match what you have - would also help if you provided the text to the error as this usually tells you exactly what the problem is

"tblPersonnel", "[DateDeparted] IS NOT NULL AND CardNumber=" & txtyCardNumber


it may be you need to use

not isnull([DateDeparted])

[Khalil Handal]

Hi,
I used the following and it worked.

If DCount("*", "tblPersonnel", "[CardNumber]=" & txtCardNumber & "AND [DateDeparted] IS NOT NULL") > 0 Then

Thank you all

[Welshgasman]

If each card number is unique, how can you have a dupe?
Plus that does not take into account someone withe the same card number that has not left?

[Khalil Handal]

Hi Welshgasman
The table has all records for all persons: those departed (left) and those who did not. Originally the card number was not unique which allowed having duplicates and data was added to the table.

The card number field property was indexed= Yes , duplicates OK. So I have to remove the duplicates first, then, change the indexed: Yes, no duplicates. When this is done, no need for the DCount function to be used because in this case when a new record is added it should be saved to the table (having all persons even if the query has only persons with DateDeparted is null) and the default access error message will be shown. Is my analyses correct?

Khalil

[Welshgasman]

Yes, you should get an error message if No duplicates are chosen.
However I would have thought that you would need to re-number any cardnumber duplicates, else the index could not be created?

[CJ_London]

I would also add the standard access message is not very user friendly so you may want to keep the code for a more friendly message