jwcolby
jwcolby at colbyconsulting.com
Wed Apr 6 09:15:41 CDT 2011
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 > >