[AccessD] Autonumber when?

David McAfee davidmcafee at gmail.com
Wed Apr 6 08:17:29 CDT 2011


I don't know if this helps, but I always call @@IDENTITY in SQL to get the
last PKID.

Sent from my Droid phone.
On Apr 5, 2011 8:56 PM, "jwcolby" <jwcolby at colbyconsulting.com> wrote:
> In Access an autonumber is created as you start filling in the record.
>
> In SQL Server the autonumber is created after you save the record.
>
> Thus, the following code, good in Access:
>
> With rs
> .AddNew
> !PLSL_IDPLSUSR = mlngIDUser
> !PLSL_FE = CurrentProject.Name
> !PLSL_Login = blnLogIn
> !PLSL_WorkstationID = CurrentMachineName()
> mlngLogID = !PLSL_ID
> .Update
> .Close
> End With
>
> Fails in SQL Server because !PLSL_ID isn't created yet.
>
> I thought something simple like doing an on error and then checking as
follows would work:
>
> With rs
> .AddNew
> !PLSL_IDPLSUSR = mlngIDUser
> !PLSL_FE = CurrentProject.Name
> !PLSL_Login = blnLogIn
> !PLSL_WorkstationID = CurrentMachineName()
> On Error Resume Next
> mlngLogID = !PLSL_ID
> .Update
> If Err Then
> mlngLogID = !PLSL_ID
> End If
> .Close
> End With
>
> It doesn't.
>
> This is with a DAO recordset object to a linked table (trying to keep
things simple).
>
> Any suggestions how to get the PKID from the SQL Server table?
>
> --
> John W. Colby
> www.ColbyConsulting.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



More information about the AccessD mailing list