Determine Next Primary Key from VBA

[shamaor]

Greetings,

I am building an unbound form that will be used to collect data that will be appended to a table whose primary key is not an autonumber yet is incremented by one with each new record.

How can I determine the next valid Primary Key from VBA just prior to running an append query, or from within the append query? Up to 10 users could be performing this action, so the time difference between determining the correct Primary Key and actually appending the record would have to be quite small.

Any help will be gratefully appreciated.

Shamaor

 

[ssecca]

The following sql statement gives the last used id number from a table.

>>>>>>>>>

SELECT TOP 1 Table1.ID
FROM Table1
GROUP BY Table1.ID
ORDER BY Table1.ID DESC;

End code

But this is at best a dangerous prospect a better approach is to create a one field one record table.

tbl_NextREcID
LastUsed

Seed this with what ever number you want to.

Then use and the above to get the next number and imediatly preform an update query to increment the number.

Now it won't matter how long that pending ID is waiting to be used you will be ensured it is always unique.

This may be a little criptic but I have to go do some real work. Ask back for more info if interested.

Good Luck
ssecca

 

[oharab]

I do this a lot:
In the database, create a table called AbsenceID with a single field called LastID. Now use this code to get the next ID.
I've extended it to store mulitple ID's for multiple tables, generate ID's incorporating user/date/etc, but this is the basic code.

It's worked fine for me with 50+ concurrent users all updating.

Have Fun!!

Ben

Function GetNextID() As Long
Dim sTable As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngCntr As Long
Dim intRetry As Integer
Dim intNum As Integer, intA As Integer, intB As Integer
Dim strANum As String
On Error GoTo ErrorGetNextID
sTable = "AbsenceID"
Set db = CurrentDb()
Set rst = db.OpenRecordset(sTable, dbOpenDynaset) 'Open table with the counter
rst.MoveFirst
rst.Edit
rst!LastID = rst!LastID + 1
rst.Update
lngCntr = CLng(rst!LastID) - 1
GetNextID = lngCntr
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
ExitGetNextID:
Exit Function
ErrorGetNextID: 'If someone is editing this record trap the error
If Err = 3188 Then
intRetry = intRetry + 1
If intRetry < 100 Then
Resume
Else 'Time out retries
MsgBox Error$, 48, &quot;Another user editing this number&quot;
Resume ExitGetNextID
End If
Else 'Handle other errors
MsgBox Str$(Err) & &quot; &quot; & Error$, 48, &quot;Problem Generating Number&quot;
Resume ExitGetNextID
End If
End Function
----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[shamaor]

ssecca & Ben O'Hara,

Such an elegant solution! You broke my mindblock.

Thank-you so much!

Shamaor

 

[rdmartin]

Here is another method:

SELECT MAX(Table1.intIDNum) + 1 AS NewNum
FROM Table1;