Solved How to check duplicate on a form on two fields (1 Viewer)

[arnelgp]

i told you to Add the variable?

Option Compare Database
Option Explicit

Dim thisID As Long

Private Sub DocumentNo_BeforeUpdate(Cancel As Integer)
Cancel = withDup()
If Cancel Then
    If MsgBox("Document and Revision already exists!" & vbCrLf & vbCrLf & _
            "Do you want to go to the record?", vbInformation + vbYesNo + vbDefaultButton2) = vbYes Then
        Me.Undo
        Me.TimerInterval = 100
    End If
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = Len(Me!DocumentNo & "") = 0 Or Len(Me!Rev & "") = 0
If Cancel Then
    MsgBox "Document and/or Revision is missing!"
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
Me.CPPCategory.ColumnHidden = True
End Sub

Private Sub Form_Timer()
Dim rs As DAO.Recordset
'immediately kill the timer
Me.TimerInterval = 0
Set rs = Me.RecordsetClone
Set rs = rs.Clone
rs.FindFirst "ID = " & thisID
Me.Bookmark = rs.Bookmark
Set rs = Nothing
End Sub

Private Sub Rev_BeforeUpdate(Cancel As Integer)
Call DocumentNo_BeforeUpdate(Cancel)
End Sub

Private Function withDup() As Boolean
    Dim retBool As Boolean
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    Set rs = rs.Clone
    thisID = 0
    If rs.RecordCount < 1 Then
        GoTo exit_function
    End If
    If Len(Me!DocumentNo & "") <> 0 And Len(Me!Rev & "") <> 0 Then
        With rs
            .FindFirst "[DocumentNo] = '" & Me![DocumentNo] & "' And " & _
                        "[Rev] = '" & Me![Rev] & "' And " & _
                        "Nz([CPPCategory],'@') = '" & Nz(Me![CPPCategory], "@") & "' And " & _
                        "Nz([MainUnit], '@') = '" & Nz(Me![MainUnit], "@") & "' And " & _
                        "Nz([SECTIONUNIT], '@') = '" & Nz(Me![SectionUnit], "@") & "' And " & _
                        "Nz([SUBSECTIONUNIT], '@') = '" & Nz(Me![SubSectionUnit], "@") & "' And " & _
                        "Nz([DOCUMENTTYPE], '@') = '" & Nz(Me![DocumentType], "@") & "'"
            If Not .NoMatch Then
                thisID = !ID
                retBool = True
            End If
        End With
    End If
exit_function:
    Set rs = Nothing
    withDup = retBool
End Function

 

[amjadinsaudia]

I added the following variable in module but still facing with same error.

Dim thisID As Long

 

[arnelgp]

you add it on the Form. see my Last post (#21), those codes are from the Form.

 

[amjadinsaudia]

@arnelgp

I have attached my db, if you please can define the variable thisID at an appropriate level?

I have tried to adjust it various times on different levels but not successful.

Second thing, this duplicate check only works for the data displaying in form currently and do not check for any other existing record in the table if entered drawing no. is already available in other section / unit.

Thank you so much in advance.

 

[arnelgp]

see Hint subform code.
you have a lot of mistake on your combobox rowsource.

 

[amjadinsaudia]

@arnelgp
I must admit the support you have given to me. Thanks a lot.
for defining the variable I was doing it at main hint from and this did not thought to put it on a sub form.
Warm regards,