CODEWITHSUNDEEP
vbams-access
KScott
KScott

Reputation: 21

Find the last record in Access Table

I have an items table to which I am going to add a form to search and do maintenance on the records in the table. One thing I must have missed was making the ItemNumber (the PK) a number datatype instead of an autonumber. I now have thousands of records in other tables referencing these ItemNumbers but I need a way to essentially make the number a "fake" autonumber. So when the user goes to create a new item, the textbox that will contain the itemnumber will need to look at if it is a new record if so, see what the next number needs to be. Can someone show a sample of the VBA or macro that needs to run for this to work?

Note: I do have the field indexed so that it will notify the user if the number has already been used. Access 2016

Upvotes: 0

Views: 2410

Answers (1)

Sergey S.
Sergey S.

Reputation: 6336

As I understand, you have a table with unique PK, regular Number data type and you want to have auto numbering feature. If so, it's easy to change data type to Autonumber. You cannot insert any desired value into autonumber field in Table Designer, but you can do it using SQL INSERT. So, just create new table with Autonumber data type, copy all records from old table to new one using query, including PK data, re-link existing 13 tables to new table, drop old table and rename new one. No other changes will be required in other tables, they will keep the same references to the table with auto numbering. After this operation compact and repair the database, it will reset auto numbering to maximum inserted value.

Now in the form you'll be able to see and read new autonumber value immediately after changing any other field in the new record.

If you still want to create your own sequence and your database is multiuser, I'd recommend to implement it as recommended by Microsoft: https://support.microsoft.com/en-us/kb/191253

But it would be better to use Autonumber as PK, you will have less problems in the future.

Upvotes: 2

Related Questions