locked
Compile Error: End If without block If RRS feed

  • Question

  • I need some help with this code as it is giving me this error message.

    I have made a contact database for which I want to prevent duplicate entries in my database.  I am asking for it check Mobile Phone numbers.  If the number is exists then to go to that record to show its details.

        

    Private Sub Mobile_Phone_AfterUpdate()

    Dim NewMobile_Phone As String
    Dim stLinkCriteria As String
    Dim ClientID As Integer
    NewMobile_Phone = Me.Mobile_Phone.Value
    stLinkCriteria = "[Mobile_Phone] = " & "'" & NewMobile_Phone & "'"

    If Me.Mobile_Phone = DLookup("[Mobile_Phone]", "tbl_Client", stLinkCriteria) Then _
        MsgBox "This client " & NewMobile_Phone & ", has already been entered in database." _
                & vbCr & vbCr & "Please check client details again.", vbInformation, "Duplicate Information"
        Me.Undo

    End If

        ClientID = DLookup("[Client_ID]", "tbl_Client", stLinkCriteria)
        Me.DataEntry = False
        DoCmd.FindRecord ClientID, , , , , acCurrent

    End Sub

    This is my code but it is coming back with an error saying: Compile Error End If without block If

    Please help me

    Wednesday, January 21, 2015 12:06 PM

Answers

  • If Me.Mobile_Phone = DLookup("[Mobile_Phone]", "tbl_Client", stLinkCriteria) Then _  <=== underscore !!!
        MsgBox "This client " & NewMobile_Phone & ", has already been entered in database." _
                & vbCr & vbCr & "Please check client details again.", vbInformation, "Duplicate Information"
       

    Please note, this is a Forum for SQL Server - Database Design, not for VBA!

    It's because of the underscore after the THEN, it continous the line.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, January 21, 2015 12:17 PM
    Answerer
  • I can compile this without any issue:

    Private Sub Mobile_Phone_AfterUpdate()
        Dim NewMobile_Phone As String
        Dim stLinkCriteria As String
        Dim ClientID As Integer
        
        NewMobile_Phone = Me.Mobile_Phone.Value
        stLinkCriteria = "[Mobile_Phone] = " & "'" & NewMobile_Phone & "'"
        
        If Me.Mobile_Phone = DLookup("[Mobile_Phone]", "tbl_Client", stLinkCriteria) Then
            MsgBox "This client " & NewMobile_Phone & ", has already been entered in database." _
                    & vbCr & vbCr & "Please check client details again.", vbInformation, "Duplicate Information"
            Me.Undo
        End If
        
        ClientID = DLookup("[Client_ID]", "tbl_Client", stLinkCriteria)
        Me.DataEntry = False
        DoCmd.FindRecord ClientID, , , , , acCurrent
    
    End Sub


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, January 21, 2015 1:24 PM
    Answerer

All replies

  • If Me.Mobile_Phone = DLookup("[Mobile_Phone]", "tbl_Client", stLinkCriteria) Then _  <=== underscore !!!
        MsgBox "This client " & NewMobile_Phone & ", has already been entered in database." _
                & vbCr & vbCr & "Please check client details again.", vbInformation, "Duplicate Information"
       

    Please note, this is a Forum for SQL Server - Database Design, not for VBA!

    It's because of the underscore after the THEN, it continous the line.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, January 21, 2015 12:17 PM
    Answerer
  • I am sorry I didn't know where to post it as there was no option for VBA.

    I removed the underscore but its still coming as an error

    Wednesday, January 21, 2015 1:16 PM
  • I am sorry I couldn't find the option for VBA. I removed the underscore but I still get an error.
    Wednesday, January 21, 2015 1:22 PM
  • I can compile this without any issue:

    Private Sub Mobile_Phone_AfterUpdate()
        Dim NewMobile_Phone As String
        Dim stLinkCriteria As String
        Dim ClientID As Integer
        
        NewMobile_Phone = Me.Mobile_Phone.Value
        stLinkCriteria = "[Mobile_Phone] = " & "'" & NewMobile_Phone & "'"
        
        If Me.Mobile_Phone = DLookup("[Mobile_Phone]", "tbl_Client", stLinkCriteria) Then
            MsgBox "This client " & NewMobile_Phone & ", has already been entered in database." _
                    & vbCr & vbCr & "Please check client details again.", vbInformation, "Duplicate Information"
            Me.Undo
        End If
        
        ClientID = DLookup("[Client_ID]", "tbl_Client", stLinkCriteria)
        Me.DataEntry = False
        DoCmd.FindRecord ClientID, , , , , acCurrent
    
    End Sub


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, January 21, 2015 1:24 PM
    Answerer