jwcolby
jwcolby at colbyconsulting.com
Wed Apr 6 09:01:04 CDT 2011
Lambert, I am actually doing *both*. The code needs to function for an MDB BE or an SQL BE. By testing BEFORE the save it works for DAO, the test AFTER the save (is supposed to) works for SQL. For whatever reason it doesn't work for the sql be. John W. Colby www.ColbyConsulting.com On 4/6/2011 9:25 AM, Heenan, Lambert wrote: > 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 >