If you copied that from your database, you're missing a space between the DELETE and the *. But you don't need the * with delete queries. Just say "DELETE FROM..."

Also, you may have a non-updateable recordset. Not Updateable

You're best off ALWAYS running delete query operations (and updates) directly from TABLES, not queries.

My issue is that my WHERE condition is not on the table, hence the query. I can de-normalize the Table and store the YMyear in the ChapT Table.

This now the exact code that still gives the same error:

Private Sub DeleteBtn_Click()
    Dim S As String
    If IsNull(YearCombo) Then
        MsgBox "must enter year"
        Exit Sub
    End If
    StatusBox = ""
    If MsgBox("You are about to delete the ChapT tables. Are you sure? ", vbYesNoCancel + vbCritical) <> vbYes Then
        Exit Sub
    Else
        S = "DELETE FROM ChapDeleteQ WHERE YMYear = """ & YearCombo & """"
        DoCmd.RunSQL S
        'CurrentDb.Execute S
    End If
End Sub

The Query is updatable. I can delete manually from it.

Yeah queries will sometimes let you get away with things that you can't in SQL alone. Access can be "smart" like that sometimes. Your join is likely what's causing the problem. Can I see a small sample of your data? Why two tables?

I'm still having trouble understanding why you have the need for the second table. It looks like that's all information that can be derived from the date in the first table. Help me understand why you're doing it that way.

The Department operates on Year/Month reporting periods. everything is Drevin buy that, not a specific date. The date in tables is just the day the report was completed. I could have stored year and month in the table instead of an YearMonthID.
The App is finished but not yet implemented. If you have a better table design, I will rewrite the app as needed. I will post some screen shouts

Hi Sami, you need to join the tables in the delete query itself, so that you can choose which table to delete from.  For instance, if you join Table 1 with Table 2 and want to delete from Table1, specify DELETE FROM Table1.* in the query:

     DELETE Table1.* FROM Table1 INNER JOIN Table2 ON (Table1.field1 = Table2.field1)

In your original method, you have no way to specify which table to delete from, and that is why it doesn't work.  You can manually delete from the query because Access can make the decision for you there.  But not when you run only an SQL statement.

There are other caveats involved in doing this.  So the best way is to delete from a single table only, as it is usually recommended.  If you need to reference another table to form a criteria, you may need to work around it, such as with DLookup(), in order to keep it a single-table SQL.  E.g.:

     DELETE FROM Table1 WHERE DLookup("field1", "Table2", "ID=" & Table1.ForeignID) = SomeValue

Thank you Kevin
Every time you respond to my question, I learn something new. on top of fixing my issues.
First method worked like a charm.
I will try the DLookup method next.

Again, many thanks

this the code that is now working:

Private Sub DeleteBtn_Click()
    Dim S As String
    If IsNull(YearCombo) Then
        MsgBox "must enter year"
        Exit Sub
    End If
    StatusBox = ""
    If MsgBox("You are about to delete the ChapT tables. Are you sure? ", vbYesNoCancel + vbCritical) <> vbYes Then
        Exit Sub
    Else
        S = "DELETE ChapT.* FROM ChapT INNER JOIN S_YearMonthT ON (ChapT.YearMonthID = S_YearMonthT.YearMonthID) " & _
        " WHERE S_YearMonthT.YMYear = """ & YearCombo & """"
        DoCmd.RunSQL S
    End If
End Sub