How to top duplicate records? (1 Viewer)

[joner89]

How to stop duplicate records?

I have a subform with a button that transfers the data to another subform. At present if i attempt to transfer the same data across it appears with the standard access message.

I need to add functionality so that the button notices a duplicate record is being added. I also need an error message. Here is my code for the button.

Private Sub AddtoOrder_Click()
On Error GoTo Err_AddtoOrder_Click
 Me.Parent.F_Order_line_subform.Form.ProductID = Me.ProductID
 Me.Parent.F_Order_line_subform.SetFocus
 RunCommand acCmdRecordsGoToNew
 
Exit_AddtoOrder_Click:
    Exit Sub
Err_AddtoOrder_Click:
    MsgBox Err.Description
    Resume Exit_AddtoOrder_Click
 
End Sub

I guess i need to add the code for the error in my Err_AddtoOrder_Click section. Does anyone know how to do this. Any help would be great.

Thanks in advance!

 

[lcook1974]

Try this thread and see if it helps point you in the right direction.

http://www.access-programmers.co.uk/forums/showthread.php?t=200376&highlight=Duplicate+Record

 

[joner89]

Re: How to stop duplicate records?

Hey look1974,

Thanks for the thread link, the thing is, like most examples i have found, they are on tables. I need the code as part of a button. I dont understand how to add the code to my button code. Can i do a 'BeforeUpdate' thing in my button code?

Thanks again!

 

[lcook1974]

Could you post a sample of your DB?

 

[joner89]

Re: How to stop duplicate records?

Ye i can do that, but the file size is too big i cant upload it all. Could i email it to you?

 

[lcook1974]

Try going to compact and repair then put it in a zip file....if that don't work I'll message you my email.

 

[joner89]

Ok i have attatched a zip file of the database. Let me know if you can open it,

Cheers

Jay

 

[lcook1974]

Try this...I think it is what you are looking for.

 

[joner89]

Thankyou so much lcook1974!

Works exactly how i needed it to! Think i better look up some of the command and functions so i understand what they do.

How come you created a seperate query to compare the ProductID too?

Is that the only way you could do it?

Thanks again you have been a great help!

 

[lcook1974]

Based on the query of the form you wanted to make sure that the product ID didn't "duplicate" for the customer. Some how you have to put those records together in order to make sure the customerID and productID were on the same orderline. I did that with your queries...they basically created themselves because of your relationships are already defined.

probably not the only way to do it...but it's one way.

glad it works!!