[AccessD] Autonumber when?

Hi John

How about:

     With rs
       .AddNew
         !PLSL_IDPLSUSR = mlngIDUser
         !PLSL_FE = CurrentProject.Name
         !PLSL_Login = blnLogIn
         !PLSL_WorkstationID = CurrentMachineName()
       .Update
        mlngLogID = !PLSL_ID
       .Close
     End With

/gustav


>>> jwcolby at colbyconsulting.com 06-04-2011 05:54 >>>
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