By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,603 Members | 1,638 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,603 IT Pros & Developers. It's quick & easy.

How to continue Auto Numbering on Access Database after taking it off accidently

P: 2

I'm working with Access 2010 with the primary key set to the first field that generates ID numbers automatically as you go to the next row of the table.

The AutoNumber option has been changed to Number in the Design View and now will not go back to AutoNumber.

The table has many missing lines as some were deleted so it is not sequential +1 increment.

Is it possible to continue auto numbering from a specific number i.e. 3313

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Form_Current()
  2.  If Me.NewRecord Then
  3.  Me!LabNo = DMax("[LabNo]", "Work2019") + 1
  4.  End If
  5.  End Sub
I have tried to use the above script from a thread here but do not know where to insert it. plus I do not have a form.

Best regards
Apr 25 '19 #1
Share this Question
Share on Google+
2 Replies

P: 2
Also when I insert it in VBA as module and run the script using the play button I get "Invalid Use of Me keyword" error
Apr 25 '19 #2

Expert Mod 5K+
P: 5,397
Most likely the easiest way is to create a new table in Access based on your old table, including an autonumber field, and then use an append query to insert the records from the old table into the new table.

>>> I cannot stress strongly enough that you make a backup before attempting anything I suggest below - bad things can, and do, happen to good data! <<<
Never, ever, work on the only copy of the database - especially the in-service/production copy! Always have a recovery option available!

What I have done in the past for SMALL tables is right-click on the old table, select copy, right click in the navigation pane, select paste, select structure only and name the new table "new_?????" where the "?" are the old table name.
Open the new table in design mode and change the field of interest to "Autonumber" datatype save.

Now in query design, create a new select query using your old table and add all of your fields to the grid (or be lazy and use the * ) run to make sure your records show up, In the ribbon change the query to the "append query" type, when prompted, select the new table as the destination... run the query and confirm the record additions.

Verify that all of your records have imported, if so, then rename your old table to "old_????" (you may have to temporarily break table links to do this) and rename your new table to the old table's original name (remove the "new_" :) )

Update/restore any linked references between related tables and the new table, verify that all of your records have properly referenced the related records.

Your autonumber field in the new table will increment with the next highest number in the sequence.

Once you're happy, you can delete the old table.

(for large tables with a lot of data already entered, instead of the copy/paste method, I just create a new table by hand and the follow the remaining steps as given)


As for your error, invalid use... for ME.????. ME.???? refers to the calling access form or report and is only valid within that context - neither modules nor class-modules have this property; thus, the error.

Question: Are you using VBS or the Access VBA for your coding? If you are using the Access VBA-IDE then i'll move your thread to the Access forum.
May 1 '19 #3

Post your reply

Sign in to post your reply or Sign up for a free account.