MS Access: Why is my error message appearing immediately after entry?

I have created a form where I enter in faculty information and I made error messages appear if certain fields are missing.

However, as soon as I click save, the form saves the entry like I wanted, but also immediately generates the error messages I created, even though I didn’t have a chance to enter anything new into the fields. My code is this…

    ' Click event for Save button
Private Sub cmdSave_Click()

' ToDo fix the labels in this function so they match the function name. Just cosmetic.
On Error GoTo Add_Faculty_Click_Err

  On Error Resume Next
     
    DoCmd.GoToRecord , "", acNewRec
    
    ' Error handling for FirstName
    Dim OKToSave As Boolean
    OKToSave = True

    If Not SomethingIn(Me.FirstName) Then          ' Null
        Beep
        MsgBox "A first name is required", vbOKOnly, "Missing Information"
        OKToSave = False
    End If
    If Not SomethingIn(Me.Combo17) Then
        Beep
        MsgBox "A department is required", vbOKOnly, "Missing Information"
        OKToSave = False
    End If
    
    If Not SomethingIn(Me.LastName) Then
        Beep
        MsgBox "A last name is required", vbOKOnly, "Missing Information"
        OKToSave = False
    End If
    If Not SomethingIn(Me.Text19) Then
        Beep
        MsgBox "A user name is required", vbOKOnly, "Missing Information"
        OKToSave = False
    Else
        Dim myUserName As String
        myUserName = "UserName = " + Chr(34) + Me.Text19 + Chr(34)
        If DLookup("UserName", "tFaculty", myUserName) <> Null Then
            MsgBox "User name already on file", vbOKOnly, "User name 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
        DoCmd.RunCommand acCmdSaveRecord
        ' ToDo refresh and synch combo box
        Me.cbFacultyID = Me.FacultyID
        ' ToDo hide save and cancel buttons
        Me.cmdSave.Visible = False
        Me.cmdCancel.Visible = False
        ' ToDo show Add and delete buttons
        Me.[Add Faculty].Visible = True
        Me.Delete.Visible = True
       
    End If
    
    
Add_Faculty_Click_Exit:
    Exit Sub

Add_Faculty_Click_Err:
    
    Resume Add_Faculty_Click_Exit



End Sub

I thought I fixed things with the OKToSave portion, but its not working. What is causing this?

Answer

The very first thing your handler does is move to a new record:

DoCmd.GoToRecord , "", acNewRec

That saves the record. Nothing after that matters. So remove that line.

Replace the line:

DoCmd.RunCommand acCmdSaveRecord

With this instead:

Me.Dirty = False
DoCmd.GoToRecord , "", acNewRec

This will make it so you won’t save and move the current record until it’s OKToSave

Advertisement

Answer