want to create a code to add one to a exsisting number

[Cozzy]

Good morning people, I have a exsisting database lets call this "old asset db" I am creating a new database "new asset db" I have exported the data fine but what I want to achive is I have a table in the old database called "assets" with a field called "asset number" which is a number and I want the new database to be able to add 1 to the last number in the table , I know in theory (auto number) does this for you but the numbers that have been exported doesn't start from 1. any help will be a great help.

 

[Timtropolis]

Hi Cozzy,

There are a couple different ways to approach this. Assuming you are using a data entry form to enter in the new data, you could write some vba code in the BeforeInsert event, something like this (you'll have to edit it to suit your needs):

Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim i As Integer
    Dim strSQL As String
    
    Set dbs = CurrentDb
    
    strSQL = "Select * from assets"
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    
    rst.MoveLast
    i = rst![asset number]
    Forms!frm_Assets_dataentry!txt_assetnum = i + 1
    

    Set rst = Nothing
    Set dbs = Nothing

You may also want to lock (property of the txt_assetnum field) the so that way nobody changes the number after the fact.

HTH
Tim