MS Access AutoNumber Issue

mattyg_mb used Ask the Experts™
I've got a DB issue. I created a table, tblRollTypes that has two fields: id (AutoNumber) and name (text). I've made a form for working with this table, and adding a record works. However, when the record is added, I tried using DoCmd.RunCmd acCmdRecordsGotoNew and that resulted in an error that said it wasn't available. I managed to get it working with Me.Recordset.AddNew, but now I've got an issue where the id field increments by more than 1 (which is what it is set to). I'll have record 3 and then click the "Add" button and end up on record 5!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
My First Question is for Field "Name" was this set to allow duplicates? as this could be why it will not let you increase?

use this
 DoCmd.GoToRecord , ,acNewRec

I handle this by running a quick query for the data.  This only works, of course, if you've verified the record was added to the table.
'Get the highest ID
Private Function GetMax() As Integer
    Dim sSQL As String
    Dim rsTemp As DAO.Recordset
    sSQL = "SELECT Max(YOUR_ID_COLUMN) as Max_ID " & _
            "FROM YOUR_TABLE_NAME"
    Set rsTemp = GetRecordset(sSQL)
    If rsTemp.RecordCount = 1 Then
        GetMax = rsTemp("Max_ID")
        GetMax = -1
    End If
    Set rsTemp = Nothing
End Function

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

If you're already at a new record, you cannot go to a new record ... you must save that record:

Me.Dirty = False

Then use this, if you need to go to ANOTHER new record:

DoCmd.RunCommand acRecordsGoToNew

That said: Why does it matter if your Autonumber field increases by more than one? This field can contain somewhere around 5 billion records, IIRC, thus it wouldn't make any difference what your value will be. Your autonumber field should have absolutely no value to the record - that is, it shouldn't matter whether Access assigns a value of 5 or 50 to a particular record. If you're concerned with sequencing, then you should use a different method.


bboswell - that did the trick for forms with existing data. However, if there were no forms already, then it still said not available. Why might that be?

I'll give the points there, because it's got me developing again.


It worked, though still had some problems when there's no data on the table.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial