Solved Help to avoid duplicate in two tables (1 Viewer)

lookforsmt

Registered User.
Local time
Today, 16:31
Joined
Dec 26, 2011
Messages
663
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
Code:
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

Registered User.
Local time
Today, 04:31
Joined
Apr 13, 2010
Messages
560
Can you please try this:
Code:
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

Immoderate Moderator
Staff member
Local time
Today, 06:31
Joined
Feb 28, 2001
Messages
19,035
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.

Code:
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

AWF VIP
Local time
Today, 12:31
Joined
Jul 26, 2013
Messages
8,021
Why not simply add a unique index on those fields?
 

lookforsmt

Registered User.
Local time
Today, 16:31
Joined
Dec 26, 2011
Messages
663
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

Registered User.
Local time
Today, 04:31
Joined
Apr 13, 2010
Messages
560
Do you have a (short text) field named MICR in both tables?
 

lookforsmt

Registered User.
Local time
Today, 16:31
Joined
Dec 26, 2011
Messages
663
Yes, bastanu, i do have field name MICR in both tables.
i have got this working with the below code.
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

Registered User.
Local time
Today, 04:31
Joined
Apr 13, 2010
Messages
560
Would you please try this:
Code:
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

Registered User.
Local time
Today, 16:31
Joined
Dec 26, 2011
Messages
663
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

Registered User.
Local time
Today, 04:31
Joined
Apr 13, 2010
Messages
560
You're welcome, good luck with your project!
Cheers,
 

Users who are viewing this thread

Top Bottom