Highlighting duplicate records in a form

[zdjbel]

Hello,

I have a form called fBookings which pulls data from 2 tables:

· field [Guest] in table tGuest, and
· field [ArrDate] in table tBookings

How can I highlight the field [Guest] in form fBookings if there are multiple instances of the same guest [Guest] arriving on the same date [ArrDate]?

Thanks much!



ZDJBEL

[Bob Fitz]

Perhaps you could use DCount() function with criteria to determine if there are any duplicate records in the forms On Current event.
IMHO it might be better to use the wizard to create a Find Duplicates query and then use some validation on the form or at table level to prevent duplicates in the first place.

[orange]

Why do you have duplicates? I suggest you adjust your logic to identify and prevent duplicates.
As Bob said, you could try using a DCount function. If the result is >0, then such a record exists---don't add another.

Another option is to use prudent choice for composite unique index on the fields involved. Access will give a trappable error if you try to create a duplicate entry.

Good luck.

[zdjbel]

Hello Orange,

Thank you for suggesting looking at composite indexes, reviewing them made some queries work better. Unfortunately, I have to be able to see duplicate records because I import an Excel spreadsheet containing several hundred records on a twice daily basis which several people work on. Duplication happens occasionally, and I need to catch them when they happen to prevent issues down the "production" line. Being able to highlight them would save me a lot of time time and aggravation, so I'll look into DCount and see if I can figure out how to use it with text and date criteria.

Thanks,

Zdjbel

[zdjbel]

Hello Bob,

The Find Duplicates query works of course, but I'm looking for a solution that will work in the form I have on the screen. Thanks for the suggestion where to put the DCount expression, I'll try it as soon as I figure out how to combine text and date criteria.

Many thanks,

Zdjbel

[ItsMe]

I would avoid importing duplicate data. In other words, use a temp table and validate your data before updating your production tables(s) from the temp table.

[zdjbel]

That's a good strategy and probably the best way to go about this, thanks for the suggestion.

[ItsMe]

Maybe check out the Query Wizard and try the Unmatched option. After you run the wizard, you can create multiple joins on multiple columns, duplicating the criteria for each joined column that checks for Null.

[zdjbel]

Hello ItsMe,

I tried the Unmatched Query Wizard and it worked fine. I also found a very informative and well written article on the subject here which helped me solve my problem of finding not only how to identify daily additions to the table but also deletions. Thank you for your suggestions and help.

Zdjbel

[ItsMe]

Great, glad you were able to accomplish the task and thank you for posting how you got there.