Heenan, Lambert
Lambert.Heenan at chartisinsurance.com
Wed Apr 6 08:58:42 CDT 2011
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