Search code examples
sqlvbams-accessdebugging

MS Access: Why is my code not being reached?

Im trying to do some error handling for my code and I want my custom error message to appear if the user is trying to enter an already existing record. Access gives its own standard error message indicating a duplicate record, but I want mine displayed instead. The issue is the part of the code where I have my custom error message isn't being reached, therefore giving me the default message.

The name of the textbox is "DepartmentCode", the name of the table its being drawn from is "tDepartment" and the column name is "DepartmentCode"

My code is this...

Private Sub bAddDepartment_Click()
On Error GoTo bAddDepartment_Click_Err

   Dim OKToSave As Boolean
    OKToSave = True

    If Not SomethingIn(Me.DepartmentCode) Then          ' Null
        Beep
        MsgBox "A department code is required", vbOKOnly, "Missing Information"
        OKToSave = False
   
    Else
        Dim myDepartmentCode As String
        myDepartmentCode = "DepartmentCode = " + Chr(34) + Me.DepartmentCode + Chr(34)
        If DLookup("DepartmentCode", "tDepartment", myDepartmentCode) <> Null Then
            MsgBox "Department already on file", vbOKOnly, "Department already on file."
            OKToSave = False
        End If
    End If
    If OKToSave Then
        ' If we get this far, all data is valid and it's time to save
        Me.Dirty = False
        DoCmd.GoToRecord , "", acNewRec
        
       
    End If
bAddDepartment_Click_Exit:
    Exit Sub

bAddDepartment_Click_Err:
   
    Resume bAddDepartment_Click_Exit

End Sub

The part not being reached is If DLookup("DepartmentCode", "tDepartment", myDepartmentCode) <> Null Then

Why is this happening?

Solution

  • Debugging VBA Code <-- to see which lines are actually executed.

    If DLookup("DepartmentCode", "tDepartment", myDepartmentCode) <> Null Then

    You can't compare to Null like that. Try this in the Immediate Window:

    ? ("foo" <> Null)
Null

    Use IsNull()

    If Not IsNull(DLookup("DepartmentCode", "tDepartment", myDepartmentCode)) Then

    or if empty strings are also possible, use Nz()

    If Nz(DLookup("DepartmentCode", "tDepartment", myDepartmentCode), "") <> "" Then