[AccessD] Autonumber when?

Look again John. Gustav beat me to it. What he has suggested is to fill in all your data fields and *then* execute the .Update method. 

That should create the record, and which point PLSL_ID will have a value, and then you should simply be able to read the value, as after the .Update the record should still be current.

You were originally trying to get the value of PLSL_ID before the .Update, which generates an error.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, April 06, 2011 7:58 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Autonumber when?

Gustav,

That is what I used in the second code example, surrounded by error check so that when going against a normal mdb be it would not do that line.

John W. Colby
www.ColbyConsulting.com

On 4/6/2011 7:06 AM, Gustav Brock wrote:
> 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?
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com