Preventing duplicate record

[wpryan]

Hi All, the database I am working on is structured as such:


frmClinicInfo starts everything off, which contains demographic information about a customer. It contains a control to open frmProductInfo_Active

frmProductInfo_Active contains filtered information about the customers' products. The data source is a query from tblProductInfo. This form also contains a control which opens frmProductInfo, which is the unfiltered listing of the customers' products. The data source is also tblProductInfo. Both of these forms contain a control to open frmServiceInfo.

frmServiceInfo has a control top open frmComponents, and here is the source of my question.

frmComponents contains information about the sub-components contained in a customer's product. There are about 20 items. If a component is replaced, then that needs to be noted in this form. In order to facilitate data entry, I put in some code which copies all data from the current record to a new one, and locks the old record. Code is here:

Private Sub cmdEditComponents_Click()
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdCopy
    DoCmd.GoToRecord , , acNewRec
    DoCmd.RunCommand acCmdPasteAppend
    Me.AllowEdits = True
    If CurrentProject.AllForms("frmProductInfo_Active").IsLoaded Then
        Me.TeamviewerID = [Forms]![frmProductInfo_Active]![TeamviewerID]
        Me.MAC_Address = [Forms]![frmProductInfo_Active]![MAC_Address]
        Me.ServiceID = [Forms]![frmServiceInfo Subform]![ServiceID]
        Me.ClinicName = [Forms]![frmProductInfo_Active]![ClinicName]
        Me.FLM_Dev = [Forms]![frmProductInfo_Active]![SN]
    ElseIf CurrentProject.AllForms("frmProductInfo").IsLoaded Then
        Me.TeamviewerID = [Forms]![frmProductInfo]![TeamviewerID]
        Me.MAC_Address = [Forms]![frmProductInfo]![MAC_Address]
        Me.ServiceID = [Forms]![frmServiceInfo Subform]![ServiceID]
        Me.ClinicName = [Forms]![frmProductInfo]![ClinicName]
        Me.FLM_Dev = [Forms]![frmProductInfo]![SN]
    End If
    Me!ProductID = Forms![frmServiceInfo Subform]![ProductID]
 End Sub

The person can then just change the one component which was exchanged. However, if a person hits the control by mistake or by just playing around, a new duplicate record will have been created. I want to prevent this from happening. I was thinking on putting some code in the After_Update event which will compare the forms' data with the previous record, and if both are equal, to not save the current record, but this is beyond my capabilities. If anyone can help me with this, or suggest another way to tackle this problem, I'd appreciate it.

[orange]

AfterUpdate is just that -AFTER the UPDATE.
You could try Before UPDATE. See if the record already exists eg if DCount(your test) >0 Then the record exists,
Cancel the update and prevent a duplicate.

Can you post a jpg of your tables and relationships? That may give readers more ideas about your set up.
Good luck.

[wpryan]

Thanks for your response. I've uploaded a picture of the relationships of those four tables. I guess where I want to do with the Before_Update event (thanks for your correction) is to put some If...Then code where
if this.value1 = that.value1 and this.value2=that.value2 then
exit form without saving
where this = the current form and that = the previous record
...I hope this all makes sense!