How to tell what the next AUTONUMBER value will be?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to be able to programmatically (VBA) determine what the next
autonumber value will be for a specific table. I don't want to have to look
at the table itself (which might be empty). I just want to be able to find
out directly (somehow) what the next value will be when a record is added to
the table.

Any ideas?

Thanks!
 
Dennis said:
I need to be able to programmatically (VBA) determine what the next
autonumber value will be for a specific table. I don't want to have to look
at the table itself (which might be empty). I just want to be able to find
out directly (somehow) what the next value will be when a record is added to
the table.
Click to expand...


AFAIK, that value is not available. It wuoldn't be useful
even if you could get it, because it might change between
the time you got it and the time you save a new record that
actually uses the autonumber.
 
The reason I want it is to determine if it's >1. If the database has been
compacted on an empty table, that table's autonumber (key) should be "1". I
want a way to make that determination, in order to tell the user via a
dialogbox to compact the database.
 
Dennis said:
I need to be able to programmatically (VBA) determine what the next
autonumber value will be for a specific table. I don't want to have
to look at the table itself (which might be empty). I just want to be
able to find out directly (somehow) what the next value will be when
a record is added to the table.

Any ideas?

Thanks!
Click to expand...

DMax(field name for auto number) + 1 if sequential. I use this basic method
for transactions, however this is a single person system only. I wouldn't
recommend it on a multi-user system.
 
Joe Cilinceon said:
DMax(field name for auto number) + 1 if sequential. I use this basic method
for transactions, however this is a single person system only. I wouldn't
recommend it on a multi-user system.
Click to expand...

Won't work if the latest record has been added and then deleted. Think the
best way round it would be to copy the table, add a new record, then grab
the ID from that.

Dennis - think you'll be better off just running a scheduled compact. I use
this http://www.fmsinc.com/products/Agent/index.html
 
Dennis, the AutoNumber should not be negative unless:
a) its New Values property is Random, or
b) you have struck a bug described in this article:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

However, if you want to know that the next autonum will be you can include a
reference to this library:
Microsoft ADO Ext 2.x for DDL and Security
and use the code below:

Function GetSeed(strTable As String)
Dim cat As New ADOX.Catalog 'Root object of ADOX.
Dim tbl As ADOX.Table 'Each Table in Tables.
Dim col As ADOX.Column 'Each Column in the Table.

'Point the catalog to the current project's connection.
Set cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTable)

'Loop through the columns to find the AutoNumber.
For Each col In tbl.Columns
If col.Properties("Autoincrement") Then
GetSeed = col.Properties("Seed")
Exit For 'There can be only one AutoNum.
End If
Next

'Clean up
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
End Function
 
Joe Cilinceon said:
DMax(field name for auto number) + 1 if sequential. I use this basic method
for transactions, however this is a single person system only. I wouldn't
recommend it on a multi-user system.
Click to expand...

Won't work if the latest record has been deleted.
 
Back
Top