Autoincrement
May 1, 2005Dears members,
Is there a way in MS Access to keep an autoincrment field counting successivly after deleting and inserting? I want the field having the right counting.
Thanks in advance for your help
ADVERTISEMENT
Autoincrement When Importing
Aug 28, 2006I have a database that a client of mine has started to create. He wants to be able to import data from an excel spreadsheet into a table in Access. I created a macro that works fine but now I am having an issue with importing additional data.
His excel sheets consist of Agency ID numbers, first name, last name, SSN, etc. He also has a column in the table called order number and internal ID. Which I duplicated in the spreadsheet. He wants the internal ID to autonumber. When I first use the macro to import the spreadsheet into Access it numbers the rows accordingly (after I fill the excel sheet using the series option). However, when I try to import an additional spreadsheet (which is a test sheet)I have to fill in the Internal ID number manually and consecutively according to the last record in access.
The sample spreadsheet he sent me has 2079 records which imported fine with the macro and then I made a second spreadsheet with two rows with information in them and if I don't put in the numbers 2080 and 2081 it imports them at the top of the table without any number in the internal ID. I hope this is making sense.
Can anybody help me make it so that it automatically puts a number (consecutively)in the internal ID field when I use the macro to import another spreadsheet.
Thanks in advance
Tracy
P.S. When I go to the order details table in design view it says that field is autonumber. If I do not assign a value to the Internal ID field in the spreadsheet it comes up with an import error saying that the fields had a null value but if I number the internal ID field manually in the spreadsheet it imports just fine. I don't want to manually input numbers into the internal ID field in the spreadsheet, I want access to do that for me but it won't let me.
Autoincrement When Importing
Aug 28, 2006I have a database that a client of mine has started to create. He wants to be able to import data from an excel spreadsheet into a table in Access. I created a macro that works fine but now I am having an issue with importing additional data.
His excel sheets consist of Agency ID numbers, first name, last name, SSN, etc. He also has a column in the table called order number and internal ID. Which I duplicated in the spreadsheet. He wants the internal ID to autonumber. When I first use the macro to import the spreadsheet into Access it numbers the rows accordingly (after I fill the excel sheet using the series option). However, when I try to import an additional spreadsheet (which is a test sheet)I have to fill in the Internal ID number manually and consecutively according to the last record in access.
The sample spreadsheet he sent me has 2079 records which imported fine with the macro and then I made a second spreadsheet with two rows with information in them and if I don't put in the numbers 2080 and 2081 it imports them at the top of the table without any number in the internal ID. I hope this is making sense.
Can anybody help me make it so that it automatically puts a number (consecutively)in the internal ID field when I use the macro to import another spreadsheet.
Thanks in advance
Tracy
P.S. When I go to the order details table in design view it says that field is autonumber. If I do not assign a value to the Internal ID field in the spreadsheet it comes up with an import error saying that the fields had a null value but if I number the internal ID field manually in the spreadsheet it imports just fine. I don't want to manually input numbers into the internal ID field in the spreadsheet, I want access to do that for me but it won't let me.
Me.Undo Leave Autoincrement !?!
Jan 27, 2005Hi there,
I've a button that position the table on a new record:
DoCmd.GoToRecord , , acNewRec
And I create a button to cancel that action:
Me.Undo
But the code field (autoincrement) continues autoincrementing! Explaning:
1. I click on "Add" button
2. The table's positioned on autoincrement code #1
3. I click on "Cancel" button
4. The register is cancelled, it isn't includded on table
5. I click on "Add" button
6. The table's positioned on autoincrement code #2 and the #1 isn't exists...
How it's happen? How I solve it?
Thanks
Modules & VBA :: Hash Deleted And Autoincrement By 2
Sep 15, 2014When I have a record that I copy and paste into a new record with one column having a unique constraint , I display message "Cannot insert duplicate code" then if not all values in the record are inserted , I get a #deleted in all columns
Refreshing , I find that the id is incremented by 2 instead of one.
That is if the previous record has ID 20 , the #deleted record has ID 22 .
How to avoid the #deleted which happens if not all columns are filled.