asked on

MS Access AutoNumber Issue

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!

ASKER CERTIFIED SOLUTION

bboswell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Yadtrt

use this
DoCmd.GoToRecord , ,acNewRec

Torrwin

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")
    Else
        GetMax = -1
    End If
    
    Set rsTemp = Nothing
End Function

Open in new window

Scott McDaniel (EE MVE )

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.

mattyg_mb

ASKER

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.

mattyg_mb

ASKER

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