Prevent duplicate entry & Spill Checker

[Alhakeem1977]

Need your help to fix the below code I got Compile error Else without if.

Actually, the code prevents any duplicate entry and Spill Checker too.
I don't know how to fix it.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 On Error GoTo ErrorHandler

Dim strLinkCriteria As String
    strLinkCriteria = "[DocumentName] = '" & Me!DocumentName & "'"
    
    If DCount("*", "tblDocumentName", strLinkCriteria) > 0 Then
        MsgBox "This document Name already exists in the database.", vbCritical, "Duplicate Entry"
            
        Cancel = True
        Me.Undo
     
     Else
     
     With Me![DocumentName]
  If Len(.Value) > 0 Then
       DoCmd.SetWarnings False
    .SetFocus
    .SelStart = 1
    .SelLength = Len(.Value)
    DoCmd.RunCommand acCmdSpelling
    End If
End With
  Cancel = True
  End If
  
Else
Me.DocID = Nz(DMax("DocID", "tblDocumentName"), 0) + 1
    End If
    End If
  
Cleanup:
  Exit Sub
ErrorHandler:
   MsgBox Err.Number & ": " & Err.Description
  Resume Cleanup
  
End Sub

Thanks in advance

 

[jdraw]

Here is your code with indentation via Smart Indenter. It may help you track down the missing IF.

Note: Each IF should be matched with an End IF

Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error GoTo ErrorHandler

    Dim strLinkCriteria As String
    strLinkCriteria = "[DocumentName] = '" & Me!DocumentName & "'"

    If DCount("*", "tblDocumentName", strLinkCriteria) > 0 Then
        MsgBox "This document Name already exists in the database.", vbCritical, "Duplicate Entry"

        Cancel = True
        Me.Undo

    Else

        With Me![DocumentName]
            If Len(.value) > 0 Then
                DoCmd.SetWarnings False
                .SetFocus
                .SelStart = 1
                .SelLength = Len(.value)
                DoCmd.RunCommand acCmdSpelling
            End If
        End With
        Cancel = True
    End If

Else
    Me.DocID = Nz(DMax("DocID", "tblDocumentName"), 0) + 1
End If
End If

Cleanup:
Exit Sub
ErrorHandler:
MsgBox err.number & ": " & err.Description
Resume Cleanup

End Sub

Did you write this code?

Good luck.

 

[isladogs]

Adding to what Jack supplied, you should be able to see the errors now.

You have 1 IF statement but 2 ELSE and 2 END IF
So you either need another IF or you need to delete one of the ELSE sections and one END IF

 

[Alhakeem1977]

Thanks for the fast response.

I got highlighted the else before DMax.

Actually, I am new in Access I don't know how to fix it, I need both functions before update the record I tried many ways to fix it but I couldn't.

 

[jdraw]

Did you write this code?

You have an orphan Else and 2 orphan End if as Ridders said.

What did you intend this code to do? and under what condition(s)?

Else
Me.DocID = Nz(DMax("DocID", "tblDocumentName"), 0) + 1
End If
End If

 

[isladogs]

Then you'll need a structure like this

    If DCount("*", "tblDocumentName", strLinkCriteria) > 0 Then
        MsgBox "This document Name already exists in the database.", vbCritical, "Duplicate Entry"

        Cancel = True
        Me.Undo

    Else[COLOR="Red"]If SOME OTHER CONDITION Then[/COLOR]

        With Me![DocumentName]
            If Len(.value) > 0 Then
                DoCmd.SetWarnings False
                .SetFocus
                .SelStart = 1
                .SelLength = Len(.value)
                DoCmd.RunCommand acCmdSpelling
            End If
        End With
        Cancel = True
    [COLOR="red"]'End If '<=== REMOVE THIS LINE[/COLOR]

     Else
            Me.DocID = Nz(DMax("DocID", "tblDocumentName"), 0) + 1
    End If

In the final line you want the value in the DocID control on the form to be 1 more than the max value in the table OR if the table is EMPTY to start at the value 1.
The obvious question is why not just make the DocID field an autonumber field then it will happen automatically

BUT
Either there is a record ... so the IF condition applies
OR there isn't ...
In which case, your code structure just needs to be

IF...

ELSE

END IF

===========================
Are you confused yet?

 

[Alhakeem1977]

Yes, I wrote the code you provided.

What did you intend this code to do? and under what condition(s)?

DMax it adding a sequential number (instead of Ms Access Autonumber) for the document name if the spelling is correct and not available in the database to prevent duplicate entry.