CUSTOM "duplicates" ? (1 Viewer)

[jamie_b]

Hi,

On the form there are 15 fields and a custom "SAVE" button for which the OnClick Event checks various things before storing the record.

There are 3 specific [textbox] fields which must not be duplicated in the table
For example:

PK=personnel_id
Field1: John
Field2: Doe
Field3: Logistics

(For my purposes), there can never be another record where these 3 fields matches an existing record.

I would like to add code to the OnClick Event that will check for this "duplicate".

Then I can Msgbox the user to do what ever is needed in such a case.

Any help is appreciated.

Thank you kindly.
Jamie.

 

[dbDamo]

So you could never have another John Doe working in Logistics?

 

[jamie_b]

So you could never have another John Doe working in Logistics?

CORRECT!

I was just using an example. My DB has nothing to do with personnel.

 

[DCrake]

If you go into the design of your table and highlight the three fields in question then press the Key button (Primary Key) you should see a key symbol next to each field. This will ensure no duplicate are encountered.

David

 

[dbDamo]

If you do really want to do this (I wouldn't, but you may have a valid reason), then open your table in design view, highlight the 3 field rows, right click and select the Primary Key icon

 

[georgedwilkinson]

Or better yet, make the 3 fields in question a unique key and use a surrogate (or artificial) primary key.

 

[jamie_b]

If you do really want to do this (I wouldn't, but you may have a valid reason), then open your table in design view, highlight the 3 field rows, right click and select the Primary Key icon

Thanks everyone.

I'm aware of the PK option for fields in table design, but I don't want to use that. As dbDamo said: "I wouldn't" (either).

As an interim measure (for my own valid reasons), I WANT to do it with code in the button's OnClick.

OK, from code, with a Msgbox, let's say I just want to "warn" the user that he "might" be capturing a duplicate record. (Is that better dbDamo ?)

My point is I want to (check for "duplicates"), (on those 3 fields only) and I want to do it with my own custom code, I don't want the Table Design to check it for me.

Any ideas ?
Thanks again.

 

[Atomic Shrimp]

In my experience, unless it is actually the case that the table defines a set of unique values that by definition must not include duplicates (i.e. where the real-world situation being modelled dictates it must not happen, as opposed to it merely not being expected)...

...the situation that everybody always says will never occur does occur shortly after the point where the system is established and data structure changes are no longer easily possible.

 

[dbDamo]

I think I get it now.

You do not want to prevent duplicate data being added based on these three fields, as it may be a valid entry, but you do want to warn the user that the same data already exists in the table and to ask them if they wish to continue with the save?

So you would like to code a button that on click looks at your table to see if the 3 fields entered have matching values in a single existing record in the table. If the table does contain this data, ask question asking the user if they wish to continue with the save (upon selecting yes the record will save, selecting no will undo all unsaved changes) and if the data does not exist then skip the message and save?

 

[jamie_b]

...the situation that everybody always says will never occur does occur shortly after the point where the system is established and data structure changes are no longer easily possible.

That is exactly the reason why I don't want to do it in table design.

If we restrict he duplicates in table design, then it means this:
- There can NEVER be more than 1 [John] [Doe] that works in [Logistics].

If we can use code to somehow check for > when the user captures a 2nd John Doe in Logistics then it means:
- There CAN be 2 records with same data for those fields.

I just want to "alert" the user that he is "possibly" capturing a duplicate record and that he should double-check his input. There are other fields on the form such as [amount]s that can have a huge impact on statistical reports.

Thanks

 

[georgedwilkinson]

Look at the Form's Before Update event. Set the Cancel flag to true if the user says (via the proposed Msgbox) they don't want to do the update.

 

[jamie_b]

I think I get it now.

You do not want to prevent duplicate data being added based on these three fields, as it may be a valid entry, but you do want to warn the user that the same data already exists in the table and to ask them if they wish to continue with the save?

So you would like to code a button that on click looks at your table to see if the 3 fields entered have matching values in a single existing record in the table. If the table does contain this data, ask question asking the user if they wish to continue with the save (upon selecting yes the record will save, selecting no will undo all unsaved changes) and if the data does not exist then skip the message and save?

Yes dbDamo, thanks for understanding. That is EXACTLY what I want to do.
Now we need to start looking at what code to behind the OnClick event, and that's where I draw a blank. I will play with some ideas and post here, but any input from your side is appreciated.

@georgedwilkinson : My db is locked down as far as closing forms/windows/app is concerned. Only custom navigation buttons everywhere. So my custom SAVE button is where I do all the error checking etc. The important part for me is USER FRIENDLINESS; the main reason why I want to prompt the user of a "possible" incorrect (duplicate) record.

Thanks for all your input.

 

[georgedwilkinson]

My db is locked down as far as closing forms/windows/app is concerned. Only custom navigation buttons everywhere. So my custom SAVE button is where I do all the error checking etc. The important part for me is USER FRIENDLINESS; the main reason why I want to prompt the user of a "possible" incorrect (duplicate) record.

That would have been an interesting thing to point out in the original post. I'm sure you would have gotten more focused answers.

 

[jamie_b]

That would have been an interesting thing to point out in the original post. I'm sure you would have gotten more focused answers.

@georgedwilkinson:
In the very first sentence of my original post i wrote:
"On the form there are 15 fields and a custom "SAVE" button for which the OnClick Event checks various things before storing the record."

I do sincerely apologize if I was not clear enough in the approach to my question.

Thanks for pointing it out.

 

[dbDamo]

Yes dbDamo, thanks for understanding. That is EXACTLY what I want to do.
Now we need to start looking at what code to behind the OnClick event, and that's where I draw a blank. I will play with some ideas and post here, but any input from your side is appreciated.

@georgedwilkinson : My db is locked down as far as closing forms/windows/app is concerned. Only custom navigation buttons everywhere. So my custom SAVE button is where I do all the error checking etc. The important part for me is USER FRIENDLINESS; the main reason why I want to prompt the user of a "possible" incorrect (duplicate) record.

Thanks for all your input.

I will have a play around and see if I can come up with anything. I can't see it being very straight forward but hopefully between a few of us we can come up with something. Is there any chance you can upload a copy of your db for me to play with?

 

[jamie_b]

I will have a play around and see if I can come up with anything. I can't see it being very straight forward but hopefully between a few of us we can come up with something. Is there any chance you can upload a copy of your db for me to play with?

my db is huge! as in many many tbls,frms,reports,qrys etc,etc.
and since i work for a gov. dept. i will have to strip just about everything. -they get very tense about the smallest of things

but i will create a new db tomorrow and post it for us to play with.
we only need 1 table with 5 fields...

tbl_pers
---------
pers_id (PK)
pers_name (text)
pers_lastname (text)
pers_dept(text)
remarks(memo)

thanks for showing interest in my little dilemma.

 

[dbDamo]

Thats fine, just pop some dummy data in and we can give it a go. I only take interest in the questions I think I can help in, most are way above my level of expertise!!!

 

[georgedwilkinson]

In your On Click event, why not just do a DCount with your 3 criteria in the "Criteria" parameter? Compare the results of DCount to zero and if not zero, put up your Msgbox(). This is not the best method but is certainly one of the easiest and best documented.

Does that help?

In the very first sentence of my original post i wrote:
"On the form there are 15 fields and a custom "SAVE" button for which the OnClick Event checks various things before storing the record."

But you also said:

(For my purposes), there can never be another record where these 3 fields matches an existing record.

Not knowing that you have no control over the back end is a pretty decisive factor in what approach to take. And since nobody on these forums know what your level of expertise is, you were getting answers based on what we were able to read and perceive about said level of expertise. And many of the experts that assist people here have to make assumptions that you might have overlooked an obvious approach, unless you provide information to the contrary, just because you're asking the question.

 

[jamie_b]

@georgedwilkinson

Thank you for putting me on the right path with Dcount.

nz(Dcount("*","tbl_Personnel", "pers_name = '" & Me.txtpersname & etc, etc, etc, "'"),0)

Having that at the end of my custom SAVE button's onclick, together with a vbYesNo works perfectly.

Thanks again.

 

[georgedwilkinson]

Excellent. Glad we were able to help you get it going.

Like I said, there are more elegant ways to get it done but if DCount is working for you, you should be OK sticking with it.

Yell if you need anything else.