Asger Blond
ab-mi at post3.tele.dk
Wed Apr 6 07:52:21 CDT 2011
Ok, I see.
Then try a MoveLast after the Update and before reading the new autonumber, i.e.
With rs
.AddNew
!PLSL_IDPLSUSR = mlngIDUser
!PLSL_FE = CurrentProject.Name
!PLSL_Login = blnLogIn
!PLSL_WorkstationID = CurrentMachineName()
.Update
.MoveLast
mlngLogID = !PLSL_ID
.Close
End With
Works for me.
Asger
-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af jwcolby
Sendt: 6. april 2011 13:58
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Autonumber when?
Asger,
The ! replaces the .
John W. Colby
www.ColbyConsulting.com
On 4/6/2011 6:52 AM, Asger Blond wrote:
> Not sure, but in your With...End With construction I suppose you need a period in front of each line implying rs, i.e.
> With rs
> .AddNew
> .!PLSL_IDPLSUSR = mlngIDUser
> .!PLSL_FE = CurrentProject.Name
> .!PLSL_Login = blnLogIn
> .!PLSL_WorkstationID = CurrentMachineName()
> On Error Resume Next
> .Update
> mlngLogID = .!PLSL_ID
> If Err Then
> mlngLogID = .!PLSL_ID
> End If
> .Close
> End With
>
> Asger
>
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af jwcolby
> Sendt: 6. april 2011 12:10
> Til: Access Developers discussion and problem solving
> Emne: Re: [AccessD] Autonumber when?
>
> Thanks Rusty, but that didn't seem to do anything either. This was a new record created.
>
> John W. Colby
> www.ColbyConsulting.com
>
> On 4/6/2011 12:02 AM, Rusty Hammond wrote:
>> 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.
>> **********************************************************************
>>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com