[AccessD] Autonumber when?

John,

  You may find the following helpful:

http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/3507c845-66a6
-4e13-8876-f6e5faae8dba/

  It pretty much covers what has already been said, but goes through some of
the in's and out's of why one approach works or doesn't.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, April 05, 2011 11:55 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Autonumber when?

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