how do i determine what the next record number (PK) is?

Question

im trying to get the next number in the autonumber sequence for the primary key programatically. For instance, if the last number in the table was 10, i need it to return 11. Before, I would use something like:

docmd.RunCommand acCmdRecordsGoToNew

in order to tell the database to go to the next record, and then i'd assign it to a control on the form to show the user what record they are currently entering. The problem is, this function ceased to work when I disabled the navigation buttons by setting it's property to "No" in the properties window. How do I get the next record in vba without the nav bar being enabled?

You can use ADOX to determine the next autonumber value (see http://stackoverflow.com/questions/6498221/retreve-next-autonumber-for-access-table/6499393#6499393) *if* that's what you really want. However your question suggests what you may really want is to move to the "new" record. That's a different question. — HansUp, Aug 02 '11 at 15:35
Just removing the controls is generally not enough, since the user could press Ctrl and + , or page down, and still add a record(s). Did you also disable the Allow Addition property ? That would explain why your RunCommand does not work anymore. — iDevlop, Aug 02 '11 at 15:46
@hans, actually moving to a new record wouldn't have been necessary. Simply executing an insert command would have done the same thing in this scenario. — Sinaesthetic, Aug 03 '11 at 01:17

David-W-Fenton · Accepted Answer · 2011-08-05T20:59:00.603

To know what the real next value is, you have to look up the SeedValue for your Autonumber column. This code does that:

  Public Function GetSeedValue(strTable As String, strColumn As String) As Long
    Dim cnn As Object 'ADODB.Connection
    Dim cat As Object ' New ADOX.Catalog
    Dim col As Object ' ADOX.Column

    Set cnn = CurrentProject.Connection
    Set cat = CreateObject("ADOX.Catalog")
    cat.ActiveConnection = cnn

    Set col = cat.Tables(strTable).Columns(strColumn)
    GetSeedValue = col.Properties("Seed")

    Set col = Nothing
    Set cat = Nothing
    Set cnn = Nothing
  End Function

If you're going to call it a lot, you'd likely want to cache the ADOX Catalog object variable, rather than re-initialize it each time you call this function.

Note that in a multiuser environment, this may or may not be accurate, since by the time you use it, it may have been updated by another user. However, it doesn't have the problem with skipping Autonumber values that Max()+1 can have.

Keep in mind, though, that if you care about the next Autonumber value, it means YOU'RE USING IT WRONG. Autonumber values are surrogate keys and you should never, ever care what the values are.

score 0 · Answer 2 · answered Aug 03 '11 at 01:22

0

Turns out that there is a VBA function that will interact with the database and actually return a value. This is what I ended up doing to get the next record number:

Dim nextRecord As Integer
nextRecord = DMax("ID", "my_table") + 1

Sloppy, but effective for my single client situation. There is also a where clause that can be applied:

Dim nextRecord As Integer
nextRecord = DMax("ID", "my_table", "field =  value")

answered Aug 03 '11 at 01:22

Sinaesthetic

11,426
28
107
176

2

It won't actually produce the correct value. Consider if your last value is 10. You insert a new record, 11, and then delete it. If you now add a new record, its value will be 12, not 11, as your DMax() would return. – David-W-Fenton Aug 03 '11 at 20:56
Well i still have yet to see another solution. I know in oracle, this is really easy, as you can just use nexval on a sequence object. I don't know why I'm having such trouble finding the answer for access... – Sinaesthetic Aug 04 '11 at 16:50
There is a solution -- you look up the SeedValue. I'll post an answer with the code for this. – David-W-Fenton Aug 05 '11 at 20:07

how do i determine what the next record number (PK) is?

2 Answers2