Some of your problems:
you're missing a space after the
If any of the fields in the table are field type
Date/Time, then there SQL should have
# around the date, instead of
Double-check the number of fields you list in the first half of the query, with the data you're adding (in the last half of the query). One is missing somewhere.
. after a
Option Explicit at the top of your modules (first line) to help find & fix undeclared/mishandled variables, objects, etc.
assume you don't have it all squished together like it appears in your example, but if you do, remove all
_'s except at the end of the line.
CurrentDb.Execute "INSERT INTO convenati(conid, location, surname, firstname, " & _
"middlename, phone, email, dob, sex, mstatus, status, violated, date) " & _
"VALUES(" & Me.txtid & ", '" & Me.txtlocation & "','" & Me.txtsurname & _
"','" & Me.txtmiddlename & "','" & Me.txtfirstname & "','" & Me.txtphone & _
"','" & Me.txtemail & "','" & Me.txtdate & "','" & Me.cbogender & "','" & _
Me.cbomstatus & "','" & Me.cboviolated & "','" & Me.txtdate & ") "
Also, is the last field you listed in your question
...the problem all these things have in common is lack of attention to detail. "Close" doesn't count in coding.
One way to troubleshoot problems with SQL being used in VBA is to test the actual SQL output of your statement, in an actual query.
Using the statement above as an example, change
Debug.Print, and make the next line say
Stop. Run your code, and when it breaks at the
Stop, hit Ctrl+G to open the Immediate Window, and take a look at the complete SQL.
If you still can't find a problem, copy and paste that output into a Blank Query (in SQL view) and try running the query. It it won't run there either, then you need to start removing parts to simplify the query, troubleshooting by the process of elimination.
I don't know what values each of your form controls hold, nor what data type is expected for each of the fields in the table, so I replaced them all with
___ and got:
INSERT INTO convenati (conid, location, surname, firstname, middlename,
phone, email, dob, sex, mstatus, status, violated, date) VALUES(___,
More debugging tips here.