I have seen the same problem when migrating the MS Access Northwind (NWind.mdb) sample database to MySQL. The problem occurs with the Orders form, which uses exactly the same approach as you describe. I've figured out the problem, but not (yet) the solution. Here's what I've found so far by inspecting the MySQL general query log:
The problem occurs when one or more of the fields in the main form contain a NULL value. The data from the main form is inserted correctly into the database, including the NULL values. Next, a select query is issued to retrieve the auto_increment value for row just inserted. This query uses a syntax similar to SELECT id (or whatever the auto_increment field is called) FROM table WHERE ..... In the WHERE clause, the query contains all the fields from the main form and the associated values. The problem comes in the syntax of the WHERE clause - it reads WHERE field1=value1 AND field2=value2 AND field3=NULL AND field4=value4, etc. The error is with the NULL syntax - it should be field3 IS NULL, rather than field3=NULL. Running the query with field3=NULL *always* returns no rows (see
http://dev.mysql.com/doc/mysql/en/problems-with-null.html). Since the query returns no rows, Access shows #Deleted in the form.
There are a couple of ways to verify that this is the problem:-
1) Fill in *all* the fields with data on the main form in Access and verify that it works OK (it should), and
2) Switch on the general query log in MySQL and inspect the SQL statements.
Now the issue - where does the bug lie? Can anyone tell me whether this is a MyODBC bug (I'm using 4.51.11-2 on Win XP SP2), or is it an MS Access bug (I'm using Office XP)? If I knew where the bug lay, I'd know where to report it....
Martin