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.

Thank you Richard, I just put it in and it worked perfectly!

Get to know and love recordsets. Become one with recordsets. LOL.

Monica

I also noticed your initial If Statement is missing the Preferred name.  (this also be an issue if it's null)

     If IsNull(AddFName) Or IsNull(AddLName) Or IsNull(AddEmpID) Or IsNull(AddAlias) Or IsNull(AddEmail) Then

So I would include Nz(AddPName)

I'm also curious why you make the variable Null after running your code?

-Raymond

Raymond The nulls clear out the unbound fields after the record has been added

Monica

Why Null as opposed to ""

-Raymond

Both work. Remember, Null says, "I don't know what this value is," whereas an empty string says, "I know what this value is: it's nothing." At least that's how I look at it now.

I didn't always look at it that way, and some of my older classes may not say that consistently. I'll be the first to admit that I usually take the shortcut and set text values to empty strings myself. For unbound text boxes that you're simply clearing after an insert, either approach is fine. A lot of the time it just comes down to preference.

I understand they will both work... I feel like nulls are harder to work with, so I default to empty strings.

-Raymond

I'm with Richard. There is value in many cases of having fields that can be null and then using nz(Fieldname,"") or Nz(Fieldname,0). On the surface it is often nice to know if there's no entry or if the answer is no data/no value. I believe its easier to ensure your code is evaluating the right things and sometimes it's nice to just see how many times a field is blank for consideration if its even needed.

I use the code below to handle values that could be null, a zero-length string, a non-zero-length string of spaces that looks like an empty string, etc.:

     If Len(Trim(s & "")) > 0 Then ...

Btw, when a user empties out a textbox on a form or a field in a table, the resulting value is a null.  So we have to deal with nulls regularly.

Kevin is correct. For a bound text field, if the user deletes the contents and leaves it blank, Access normally stores Null, not a zero-length string. Zero-length strings can still exist, especially if they were assigned in code or allowed by the field settings, so it is good to be aware of both.

Len(Trim(Nz(FieldName, ""))) > 0 is a solid test when you want to consider Null, "", and spaces-only entries as blank. The s & "" variation does the same kind of Null-to-empty-string conversion, although I tend to teach Nz because it makes the intent a little clearer to newer VBA students.

And yes, Raymond, Nulls can definitely make string-building SQL more annoying. That is one of the many reasons recordsets are so nice.