[AccessD] Autonumber when?

Correct.
The identity column I used has a clustered PK. If that's not the case then you could open the recordset using a sql ordering by PLSL_ID and using the dbOpenDynaset, dbSeeChanges option.
As for another record being inserted before moving last: if that's a risk then you should make the insert calling a sp in SQL Server. In this sp you can use SCOPE_IDENTITY() to get the last identity ("autonumber") value created by the procedure. (Don't use @@IDENTITY for this, since it might eventually return an identity value created by a trigger for your table.)

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af jwcolby
Sendt: 6. april 2011 16:16
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Autonumber when?

It also makes the assumption that no other record is inserted before you do the move last.

John W. Colby
www.ColbyConsulting.com

On 4/6/2011 9:58 AM, Heenan, Lambert wrote:
> Surely using .MoveLast makes the *assumption* that the table is sorted by the order that records were created in, and that is contrary to the fact that tables have *no* defined order.
>
> I am not using SQL server, but perhaps someone might explain why it might be that updating a record would result in that record no longer being current?
>
> Lambert
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
> Sent: Wednesday, April 06, 2011 9:50 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Autonumber when?
>
>>> as after the .Update the record should still be current.
>
> Can't confirm that. You have to do a MoveLast to fetch the inserted value.
>
> Asger
>
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Heenan, Lambert
> Sendt: 6. april 2011 15:25
> Til: Access Developers discussion and problem solving
> Emne: Re: [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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com