[AccessD] Return Autonumber Value

LOL thanks gustav, no idea why I never thought of that....Friday blonde moment...





Message Received: Jan 12 2007, 11:46 AM
From: "Gustav Brock" 
To: accessd at databaseadvisors.com
Cc: 
Subject: Re: [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 = 
.Fields(2).Value = 
.Fields(3).Value = 
' 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

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





Paul Hartland
paul.hartland at fsmail.net
07730 523179