How to look for duplicate

[cecil24tn]

I agree. But the funny thing about your form/table is you are asking for a value in that field. For example, open the New InHouse Ticket form and then enter something in the Transaction# Textbox. But, instead of hitting the Enter key, highlight what you just entered and hit the Delete key, then hit the Enter key. You will get an error message.

So, if we try to "clear" the duplicate number, we are "forced" to replace it with something else. Your Table settings doesn't allow to just clear that one field.

At least, that's what I think is happening.

Ok in the table I have the Field Properties "Required" set to Yes. Is this the best way to handle this?

 

[theDBguy]

Ok in the table I have the Field Properties "Required" set to Yes. Is this the best way to handle this?

You will have to decide. Sometimes, there's a trade off. You'll have to go with what you feel comfortable.

Like I said earlier, if you want the field required, you can leave that settings in, but the code will have to be adjusted to accommodate. If you take the required settings out, you can then change the code to assign a Null value to the control. Then, you could just use a validation code in the Form's BeforeUpdate event, so you can tell the user to fill in any "required" input before saving and closing the form.

 

[isladogs]

Have you tried the Find duplicates query that is supplied with Access?
Create ribbon...Query wizard...Find Duplicates query wizard.
It makes the process very easy.

 

[cecil24tn]

You will have to decide. Sometimes, there's a trade off. You'll have to go with what you feel comfortable.

Like I said earlier, if you want the field required, you can leave that settings in, but the code will have to be adjusted to accommodate. If you take the required settings out, you can then change the code to assign a Null value to the control. Then, you could just use a validation code in the Form's BeforeUpdate event, so you can tell the user to fill in any "required" input before saving and closing the form.

Let's set it to no and do the code that way. When we are done I would like for you to explain all of the code to me to help me learn this better. Again Thanks

 

[cecil24tn]

Have you tried the Find duplicates query that is supplied with Access?
Create ribbon...Query wizard...Find Duplicates query wizard.
It makes the process very easy.

No i have not. But I will look at it. I like what he is doing with this code now I want to learn it. Thanks

 

[theDBguy]

Let's set it to no and do the code that way. When we are done I would like for you to explain all of the code to me to help me learn this better. Again Thanks

Okay, this is not complete yet, but I have to leave the office now.

 

[cecil24tn]

Okay, this is not complete yet, but I have to leave the office now.

 

[cecil24tn]

I will look at what you have done. Thanks again

 

[arnelgp]

i made some changes to your uploaded db.