[AccessD] Autonumber when?

Rusty Hammond rusty.hammond at cpiqpc.com
Tue Apr 5 23:02:58 CDT 2011


Hi John, 
Do the following:
.Update
.BookMark = .LastModified
mlngLogID = !PLSL_ID


HTH 

Rusty

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, April 05, 2011 10: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
**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************




More information about the AccessD mailing list