This is only general info for others reading this thread, since the original poster already found a solution to their problem.
SQL allows you to insert a record without specify the list of fields ONLY when you insert data for all the fields. Since you cannot insert a value for an identity/auto increment you cannot insert all the fields, and the SQL will fail.
There are generally 2 ways of inserting data in to a table.
Insert Into TableName(Field1, Field2, Field3)
Values (Value1, Value2, Value3)
Or
Insert Into TableName(Field1, Field2, Field3)
Select Field1, Field2, Field3
From SomeOtherTable
Where FieldX = Condition
For example, let's say we have a table named People with fields of PersonId, Name, FavoriteFood. You can do either of the following.
Insert Into People
Values(1, 'George', 'Philly Cheese Steak')
Insert Into People(PersonId, Name, FavoriteFood)
Values(2, 'Tracy', 'Ice Cream')
Or Even...
Insert Into People(PersonId, Name, FavoriteFood)
Select 3, 'Mom', 'Spaghetti'
Here's the problem.... When you decide to use the first method (without the field names listed), you are opening yourself to potential problems later. When you first write the app, test it, it seems to work flawlessly. Then, 6 months later, your boss says, "Let's store shoe size for everyone." Being a good programmer, you decide to put the field in to the people table. The first Method will fail with "Insert Error: Column name or number of supplied values does not match table definition."
When you don't list the fields, SQL assumes that you are inserting every record and the order or the field matches the ordinal value of the fields.
I noticed that you are using Access. The following script is designed for SQL Server. You could run it on Access but you will have to change the data types. (integer to long and VarChar to text).
Code:
Create Table TestData(PersonId Integer, Name VarChar(100), FavoriteFood VarChar(100))
Insert Into TestData
Values(1, 'George', 'Philly Cheese Steak')
Insert Into TestData(PersonId, Name, FavoriteFood)
Values(2, 'Tracy', 'Ice Cream')
Insert Into TestData(PersonId, Name, FavoriteFood)
Select 3, 'Mom', 'Spaghetti'
select * from TestData
Alter table TestData Add ShoeSize Decimal(3,1)
go
Insert Into TestData
Values(4, 'Dad', 'Apple Pie')
Insert Into TestData(PersonId, Name, FavoriteFood)
Values(5, 'Bob', 'Donut')
Drop Table TestData
In my opinion, it is poor programming practice to
NOT specify the field list in an insert into.
-George
Strong and bitter words indicate a weak cause. - Fortune cookie wisdom