Solved Help to avoid duplicate in two tables

[lookforsmt]

HI! all
I am trying to avoid duplicate data entry in two tables.
I have tables called tbl_Incident and tbl_Master

Below is the before update code on field MICR in tbl_Incident

Private Sub MICR_BeforeUpdate(Cancel As Integer)
    Dim strMICR As String
    strMICR = Me.MICR
   
    If DCount("MICR", "tbl_Incident", strMICR) > 0 Then
    'Undo duplicate entry
        Me.Undo
    'Warning msgbox for duplication
        MsgBox "Duplicate MICR in tlb_Incident " & "" _
        & vbCr & strMICR, vbInformation _
        , " Duplicate MICR"
   
    Exit Sub
    Else
    End If
   
    If DCount("MICR", "tbl_Master", MICR) > 0 Then      ' Help required
    'Undo duplicate entry
        Me.Undo
    'Warning msgbox for duplication
        MsgBox "Duplicate MICR in tbl_Master " & "" _
        & vbCr & MICR, vbInformation _
        , " Duplicate MICR"
    End If
End Sub

while i try to add the same MICR again in tbl_Incident it popups msg and avoid input, The 1st code is fine,

But if i try adding MICR which is already in tbl_Master, it should popup msg and avoid input. I am not able to write this code. ' Help Required
Thanks

 

[bastanu]

Can you please try this:

Private Sub MICR_BeforeUpdate(Cancel As Integer)
    Dim strMICR As String
    strMICR = Me.MICR
  
    'If DCount("MICR", "tbl_Incident", strMICR) > 0 Then
    If DCount("*", "tbl_Incident", "[MICR]='" & strMICR & "'") > 0 Then
    'Undo duplicate entry
        Me.Undo
    'Warning msgbox for duplication
        MsgBox "Duplicate MICR in tlb_Incident " & "" _
        & vbCr & strMICR, vbInformation _
        , " Duplicate MICR"
  
    Exit Sub
    Else
    End If
  
    If DCount("*", "tbl_Master", "[MICR]='" & strMICR & "'") ) > 0 Then      ' Help required does it work

    'Undo duplicate entry
        Me.Undo
    'Warning msgbox for duplication
        MsgBox "Duplicate MICR in tbl_Master " & "" _
        & vbCr & MICR, vbInformation _
        , " Duplicate MICR"
    End If
End Sub

 

[The_Doc_Man]

What is not clear is where this code exists. However, the key to understanding this is that you should have a Form_BeforeUpdate(Cancel As Integer) entry point for each separate form, one for the form that is used for tbl_Incident and one for the form that is used for tbl_Master. In each event handler for the BeforeUpdate event, you test for the duplication pretty much as shown. But you only test for it in ONE table, the table associated with that one form. I have to assume that the field name is MICR in both tables.

Private Sub Form_BeforeUpdate( Cancel as Integer)
    If DCount( "[MICR]", "tbl_Master", "[MICR]='" & Me.Micr & "'" )
        Me.Undo
        MsgBox "Duplicate MICR in tbl_Master '" & Me.MICR & "'", vbOKOnly, "Duplicate MICR"
        Cancel = -1
    End If
End Sub

Put that in the BeforeUpdate event of the form that updates tbl_Master. Then change the "tbl_Master" to "tbl_Incident" and put the edited routine in the BeforeUpdate event of the form that updates tbl_Incident.

 

[Minty]

Why not simply add a unique index on those fields?

 

[lookforsmt]

Thanks bastanu, i replaced your code, however i am getting complie error on the seconf dCount.

thanks for the reply The Doc Man in post #4. Yes MICR name is same in both tables.
I want to avoid duplicate from table tbl_Incident for table tbl_Master. It means if the record is already in tbl_Master, it should not allow me to input in tbl_Incident
My input table will only be tbl_Incident. So i can check here itself for duplicate in tbl_Master.
Hope this clarifies.

Thanks Minty for your response too, but how do i create unique index. need to understand more

 

[bastanu]

Do you have a (short text) field named MICR in both tables?

 

[lookforsmt]

Yes, bastanu, i do have field name MICR in both tables.
i have got this working with the below code.

Private Sub MICR_BeforeUpdate(Cancel As Integer)
    Dim strMICR As String
    strMICR = Me.MICR

    If DCount("*", "tbl_Incident", "[MICR]='" & strMICR & "'") > 0 Then
    'Undo duplicate entry
        Me.Undo
    'Warning msgbox for duplication
        MsgBox "Duplicate MICR in tlb_Incident " & "" _
        & vbCr & strMICR, vbInformation _
        , " Duplicate MICR"

    Exit Sub
    Else
    End If

    If DCount("[MICR]", "tbl_Master", "[MICR]='" & Me.MICR & "'") > 0 Then
    'Undo duplicate entry
        Me.Undo
    'Warning msgbox for duplication
        MsgBox "Duplicate MICR in tbl_Master " & "" _
        & vbCr & strMICR, vbInformation _
        , " Duplicate MICR"
    End If
End Sub

However, i want to add one more information to the popup msgbox to tell the user that this is record number.
tbl_Incident as field name DocRef and tbl_Master has field name MasterRef
Any suggestion pls

 

[bastanu]

Would you please try this:

Private Sub MICR_BeforeUpdate(Cancel As Integer)
    Dim strMICR As String
    Dim sRefNumber as string

    strMICR = Me.MICR
    sRefNumber =Nz(DLookup("[DocRef]", "tbl_Incident", "[MICR]='" & strMICR & "'"),"N\A")
    'If DCount("*", "tbl_Incident", "[MICR]='" & strMICR & "'") > 0 Then
    If sRefNumber<>"N\A" Then
       'Undo duplicate entry
          Me.Undo
       'Warning msgbox for duplication
          MsgBox "Duplicate MICR in tlb_Incident " & "" _
          & vbCr & strMICR & " Existing DocRef: " & sRefNumber , vbInformation _
          , " Duplicate MICR"

        Exit Sub   
    End If

    sRefNumber =Nz(DLookup("[MasterRef]", "tbl_Master", "[MICR]='" & strMICR & "'"),"N\A")
    'If DCount("[MICR]", "tbl_Master", "[MICR]='" & Me.MICR & "'") > 0 Then
    If sRefNumber<>"N\A" Then
    'Undo duplicate entry
        Me.Undo
    'Warning msgbox for duplication
        MsgBox "Duplicate MICR in tbl_Master " & "" _
        & vbCr & strMICR, vbInformation _
        , " Duplicate MICR"
    End If
End Sub

Cheers,
Vlad

 

[lookforsmt]

Thank bastanu, absolutely amazing, This is what i wanted. Thank you once again
Want to thank all those who reviewed this post and helped me to understand this thread.
I will close this thread as Solved.
God bless

 

[bastanu]

You're welcome, good luck with your project!
Cheers,