[AccessD] Autonumber when?

jwcolby jwcolby at colbyconsulting.com
Tue Apr 5 22:54:53 CDT 2011


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



More information about the AccessD mailing list