Problem Pasting Multiple New Records

[tdezwaan]

I have a subform in datasheet view that I need to copy and paste multiple records into from an excel spreadsheet. The pasted data goes to a table that has an autonumber field as the primary key. I want to change the autonumber field to one that enters values incrementally based on VBA code to maintain incrementation when records are deleted (the autonumber field creates a gap in the incrementation when records are deleted).

The problem is this - If I simply enter records one at a time, the code that I am using for creating incremental values works just fine. If I try to copy/paste multiple records from excel the data pastes just fine but no incremental values are entered into the table. In contrast, if I use autonumber the values are entered as I want when I copy/paste multiple records.

How can I get my new "incrementation" code to enter values into the pasted records the way autonumbering does? I have tried running the code "after update", "on change" and "on enter" and none of these worked.

Thanks!

Todd

 

[paron]

Could you modify your code so that the last thing that it does is assign the new number to a new (otherwise empty)record? Then that record would keep your "start point" for subsequent additions.

Ron

 

[tdezwaan]

Ron

Thanks for the idea. I did what you suggested (using code described in thread702-24405, actually). Though a new record was added, I was still unable to paste multiple records into the datasheet view of the subform. Again, I can add the records one at a time and the appropriate incrementation of the numeric (primary) key takes place. When I paste multiple records though, I get the error that "data cannot be entered because of duplicate values in primary key field" or something like that. Apparently, pasting multiple records at once causes the same value to be applied to all of the records. Any other ideas?

Todd

 

[JAXDBase]

The autonumber feature does not reassign values to rows after deletion of 1 or more. This is designed to maintain referential integrity. By experience, the Jet DB is only able to handle autoincrementing for each row coming. It appears that a multiple row append is treated as a single unit and the DB engine will autoincrement only 1 row the others are blank. Sorry, but unless you really need this feature you can turn it off or it if is really needed, you may want to preassign the numbers in the Excel B4 multi-pasting. Another way to do it is to import the rows from the Excel using the import wizard.

Randy