Generate Error Message if 2 records match (save for the record ID number)

[Kiley]

We are recording new insurance business written with our company in the database I'm building. In this database I have put together a continuous form for them to select customer name, sales rep, account rep, line of business written, etc. from combo boxes. Is there a way to have a notification pop up if all fields match (except for the record ID) to have to user verify that they're sure they want to put this entry in? to avoid entering 2 identical completed sales through this form? there are some instances where a user will need to enter two sales that have the same customer name, sales rep, account rep, lines of business, etc, but other times when they should not, and are just accidentally duplicating something, and so i would like a notification to pop up for them to check before completing their entry. is this possible?

[orange]

there are some instances where a user will need to enter two sales that have the same customer name, sales rep, account rep, lines of business, etc, but other times when they should not, and are just acci........

What is it that determines which time this is?

[Kiley]

After the message came up, the user would need to actually look through the log to see if they've already logged this in the past. So it would be the actual user visually verifying by looking through past records. We have a 'search log' button that brings up the 'find' box so if they clicked 'no' out of the message, that they didn't want to continue, and then went to the search log button to find a past entry they could see where they've already entered a record for that and then discard changes to their current record.

[Bulzie]

Are all the combo box values required or can some be blank? If you have an Add Record button, you can use IF, Then Else statements, build a Where clause on a Select statement to check if the record will be a duplicate or use DLookUp Function, then pop up a messagebox to ask if they want to continue or not. Look up Msgbox Yes No for the code.

Just read your post above, why have user search manually for the record when you are doing that, just bring back the record(s) you found that match their entries. You can then have them select one, hit a duplicae button and copy that record if that is what you want them to do, or back out and not add a new record, etc. Lots of ways to do this but having user manually look up the same record seems like overkill.

[orange]

Just to say I agree with Bulzie -- you are building some automation, so don't design in a manual search process without looking at some options.

You need to sort out what exactly is the situation that "allows duplicates" to be stored.
There must be some logic in your business that necessitates this. Most systems would avoid duplication of records.
I think there is more analysis required. But it's your system and environment, so you know requirements better than the readers.

[Micron]

use DLookUp Function

You can only return one value at a time with this function, though you could put a lot of messy criteria into it for your purpose. Since you are referring to several fields that could contain the same values yet the new record could be a valid entry, I'd suggest you create a Find Duplicates Query and use form control references for criteria on as many fields as you think are required in determining whether or not to present a message. For example, and using my own names, the query field CustID (probably not customer name as you suggest) would contain criteria such as Forms!frmMain.txtCustID. You run the query at a point of your choosing; probably a button click or BeforeUpdate event (the latter will allow the creation of the record to be halted, the former will require a temp table setup) and if the returned record count is greater than zero, you present the warning. I agree with the notion that the similar records should immediately become available, although I'd present that as a choice in the message. If yes, the records could open in a data sheet view.

As for duplicate records, I see no real issue as long as something is different about the record AND the tables are properly normalized. However if you're storing the sales rep name, account rep name, product id along with customer name/address/phone etc. and repeating that information, I'd say your database is not set up right. The following is an overly simplistic example of what I'm talking about, but you can note there are no names in this table yet the same sales rep and account rep were involved in two sales to the same customer, yet the product is not the same. So you decide at what point there should be a warning.

CustID	ProdID	SalesRep	AccountRep	SaleDate
159	25	15	7	8/13/2016
258	111	15	4	8/14/2016
12	33	22	9	8/15/2016
159	30	15	7	8/16/2016

[Kiley]

I did not know how DLOOKUP worked. Yes that will be a much more automated process and I'll look into figuring out how to use that here.

That is what I was thinking was needed was an IF function, just trying to figure out how to build it. And i have a 'Save' button, but this is a continuous form, so if the user goes onto enter another record, it will save whether or not they've click 'Save' (that's just an extra safeguard to fire off my validation rules i have in the tables). No, not all fields are required to be filled in. If not used on a 'save' button - would beforeupdate be the next best spot for this code? that is before the record actually saves that this event will fire off, correct?

Thanks again for the help!!

[Kiley]

Just to say I agree with Bulzie -- you are building some automation, so don't design in a manual search process without looking at some options.

You need to sort out what exactly is the situation that "allows duplicates" to be stored.
There must be some logic in your business that necessitates this. Most systems would avoid duplication of records.
I think there is more analysis required. But it's your system and environment, so you know requirements better than the readers.

There are times when two policies will be exactly the same for a specific type of business (bonds usually) except for a job number, in which case we have a notes field to capture that since no other policies require a job number. So that really is when this will be accepted the most, having 'duplicates'. They're not truly duplicates, but if somebody forgets to put a job number in there, it will appear that there are duplicates.

[Kiley]

@Micron - yes i have normalized my tables. There is a separate sales rep table, account rep table, line of business table, etc. that are all pulled onto a 'new business log' table, where the salesrepid, accountrepid, etc are recorded.

[davegri]

but if somebody forgets to put a job number in there, it will appear that there are duplicates

There's your key point. Make that a required field and nobody can 'forget'.

[Kiley]

There's your key point. Make that a required field and nobody can 'forget'.

That will only ever be for somebody inputting new business for a Bond policy they've written. There are auto policies, package policies, general liability policies, etc that will never have a job number associated with it. So that is why I cannot make that field required, it is not applicable to about 90% of any of the data entry. I found how to run a find duplicate query, now just trying to figure out how to apply it to this situation - to run if certain requirements are met on the form in the beforeupdate event. Its record source is the new business log table.

[orange]

Kiley,
How about posting a jpg of your tables and relationships.

[Kiley]

I have a commercial lines new business log for example. And also a commercial lines cancellation log that has a lot of similar information, but not the same.

I have no relationships built in globally, I have used foreign keys on other tables. I thought about running a find duplicate query before running reports each month. Talked to department heads here and apparently this really doesn't happen that often. So that is my fallback option.