Here is the misbehaving code, again, different database:
    If IsNull(AddFName) Or IsNull(AddLName) Or IsNull(AddEmpID) Or IsNull(AddAlias) Or IsNull(AddEmail) Then
        MsgBox "Missing Data"
        Exit Sub
    End If
    
    CurrentDb.Execute "Insert into RecipientT (FirstName, PreferredName, LastName, EmpID, ADID, Email, Note) " & _
        "values (""" & AddFName & """, """ & AddPName & """, """ & AddLName & """, """ & AddEmpID & """, """ & AddAlias & """, """ & AddEmail & """, """ & AddNote & """)"
    
    AddFName = Null
    AddPName = Null
    AddLName = Null
    AddEmpID = Null
    AddAlias = Null
    AddEmail = Null
    AddNote = Null
    DoCmd.GoToControl "AddFName"

Was it this one?

New Record on Top

Monica What is the error message(s) you are getting.  Also, does the Note field have any strings with a " in it?

Sam is on the right track asking about the exact error and embedded quotes.

One issue I can see immediately is that AddPName and AddNote are not required, so either one can be Null. When you concatenate a Null into the SQL string, the entire SQL expression can become Null and CurrentDb.Execute will fail.

One other minor point: IsNull only catches actual Null values. If a user leaves a text box blank, it may contain a zero-length string instead. I usually test required text boxes like this:

If Nz(AddFName, "") = "" Or Nz(AddLName, "") = "" Or Nz(AddEmpID, "") = "" Or Nz(AddAlias, "") = "" Or Nz(AddEmail, "") = "" Then

That catches both Null and blank entries.

Kevin, yes, it's the extended cut thank you so much!

It says, "Syntax error in INSERT INTO statement."

What Data Types are EmpID and ADID?
If they are numbers, you don't need to enclose those values in quotes.
Also, it is hard to tell from your screenshot as the fields don't match the code.

Put this in a Global Module:

Public Function SQLText(ByVal Value As String) As String

    SQLText = """" & Replace(Value, """", """""") & """"

End Function

Then your statement becomes:

CurrentDb.Execute "INSERT INTO RecipientT (FirstName, PreferredName, LastName, EmpID, ADID, Email, Note) " & _
        "VALUES (" & SQLText(AddFName) & ", " & SQLText(AddPName) & ", " & SQLText(AddLName) & ", " & AddEmpID & ", " & _
        AddAlias & ", " & SQLText(AddEmail) & ", " & SQLText(AddNote) & ")"

Kevin's SQLText function is a good way to make an INSERT statement safer because it handles embedded quotes correctly. You'd still need to account for optional Null values, and make sure EmpID and ADID are treated correctly based on their actual field data types.

That said, this would probably be cleaner and easier to do with a Recordset. It's much more tolerant of quotes and other text issues because you're assigning values directly to fields instead of building one big SQL string. I think I used an INSERT statement in the video because I was only inserting a few fields, but once you're working with several text fields, you're much better off with a Recordset.

All fields are ShortText and it's now giving an "Invalid use of NULL" error. Thankfully this isn't a high priority as I have a bunch more classes to get thru before delving into RecordSets. Thank you all for the help. Here's my updated code:

    If Nz(AddFName, "") = "" Or Nz(AddLName, "") = "" Or Nz(AddEmpID, "") = "" Or Nz(AddAlias, "") = "" Or Nz(AddEmail, "") = "" Then
        MsgBox "Missing Data"
        Exit Sub
    End If
    
    CurrentDb.Execute "INSERT INTO RecipientT (FirstName, PreferredName, LastName, EmpID, ADID, Email, Note) " & _
        "VALUES (" & SQLText(AddFName) & ", " & SQLText(AddPName) & ", " & SQLText(AddLName) & ", " & AddEmpID & ", " & _
        AddAlias & ", " & SQLText(AddEmail) & ", " & SQLText(AddNote) & ")"
    
    AddFName = Null
    AddPName = Null
    AddLName = Null
    AddEmpID = Null
    AddAlias = Null
    AddEmail = Null
    AddNote = Null
    DoCmd.GoToControl "AddFName"

The "Invalid use of Null" error is because SQLText expects a String, but AddPName and AddNote can be Null. Since all of your fields are ShortText, I'd switch to a Recordset here. It is actually simpler than building the SQL statement, and you don't need SQLText at all.

Replace the CurrentDb.Execute line with this:

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("RecipientT")
With rs
    .AddNew
    !FirstName = AddFName
    !PreferredName = Nz(AddPName, "")
    !LastName = AddLName
    !EmpID = AddEmpID
    !ADID = AddAlias
    !Email = AddEmail
    !Note = Nz(AddNote, "")
    .Update
End With
rs.Close
Set rs = Nothing

Your required-field check can stay exactly as you have it. The Nz functions on PreferredName and Note convert a Null entry into an empty string before saving it. Also, because this is a Recordset, apostrophes and quotation marks in someone's name or note will not break anything.

Monica   In the line with CurrentDb.Execute, AddAlias is missing the SQLText function.

String concatenation for SQL is often troublesome, and can be avoided by using a parameter query, which would be especially helpful if you have many fields in your SQL, such as in your case.  That's a different subject entirely, of course.