Here's a broad question.
What are you trying to do and why?
Here's why I'm asking:
Generally, you do not use the methods you've chosen to append records.
Also, Access tends to not like it when you use the same table as the source/destination for results. In fact, I'm completely confused why you'd wish to do that at all. What you're attempting to use sounds an awful lot like what you'd have to do within Excel.
As a basic technique suggestion:
If you're trying to create new records...what you might do is:
1) Create your main table(s)
2) Copy the main table and use the copy as a 'temporary' table
3) Create a form that uses the 'temporary' table as it's recordsource
4) When ready to update the main database, use an Append query to Insert the records created in the temporary table to the Main table.
5) After Appending, delete everything in the temporary table.
The fun thing about this is, an append query uses SQL. SQL doesn't require that you specify to append 1 or 5000 records in a single action.
Try this out:
Create a table with data:
Go to the query view and begin using the QBE (query by example wizard interface) and go ahead and select everything in the first table. Save it.
Reopen it in design mode and then change it to a 'Make-Table' query (menu or buttons at top of Access). Type in a brand new table name at the prompt. Run it.
You now have two identical tables.
Return to your (now) Make Table query in design mode. Remove a couple fields. Save and Re-execute. Now the table has fewer fields (the ones you didn't delete)
Finally - purge all the contents in the 2nd table (open table, select all - tap delete key on keyboard works well). Return to your Make-Table query. Convert it to an append query. It shouldn't prompt you for a new table name. Because your field names are identical Access will "guess" that you intend to bring over the field names to the same name in the 2nd table.
Run it - it should look identical but its actually an 'Append' query.
Lastly - after saving the query. Reopen it in design mode and set a parameter. In the criteria row, under one of the fields (string or number work best for reasons best unexplained at the moment for clarity) - type in a value that matches only a few of the records in the test table. Aka, if you have 10 entries, 3 of which are all the same thing ("hi") type in the word "hi"). If you're cleared the destination table - and re-run this query, your destination table will only append those three records with a matching value.
What I'm really getting at with all this commentary is the strength behind using Access. SQL (queries use SQL) and how easy it actually is using the Wizard interfaces to build them so they do a lot of work for you, quickly.
Mike
