[AccessD] Return Autonumber Value

Hi Paul

One way to handle this is to write the complete procedure using DAO or ADO only - without DMax or action queries.
Here's how with ADO:

Public Function NextID() As Long

  Const cstrTable As String = "tblYourTable"
  
  Dim cnn         As ADODB.Connection
  Dim rst         As ADODB.Recordset
  
  Dim lngID       As Long
  
  Set cnn = CurrentProject.Connection
  Set rst = New ADODB.Recordset
  With rst
    ' Open table as recordset.
    .ActiveConnection = cnn
    .CursorType = adOpenKeyset
    .Source = cstrTable
    .LockType = adLockOptimistic
    .Open
    .AddNew
    ' Write your field values.
    .Fields(1).Value = <something>
    .Fields(2).Value = <something else>
    .Fields(3).Value = <something more>
    ' Retrieve new ID from Autonumber field.
    lngID = .Fields(0).Value
    .Update
    .Close
  End With
    
  Set rst = Nothing
  Set cnn = Nothing
  
  ' Return new ID.
  NextID = lngID

End Function

/gustav


>>> paul.hartland at fsmail.net 12-01-2007 11:54 >>>
To all,

I have a user log table (tblUserLog) for which I want to store user logon details (Name, LogonDate, LogonTime, LogoffTime, TotalTime), I also have an autonumber field called LogID.  What I need is when I write the initial user log details, I want to return the LogID into a variable.  What I did think of is write the record details and then do a MAX on the LogID field, but what would happen if two users logged on at the same time would it be possible to return the wrong LogID for one of the users ?

What is the best way to go about this ?

Thanks in advance for any help.

Paul Hartland