Martin Reid
mwp.reid at qub.ac.uk
Wed Apr 6 08:50:21 CDT 2011
Might help http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/3507c845-66a6-4e13-8876-f6e5faae8dba/ Martin -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: 06 April 2011 14:34 To: Access Developers discussion and problem solving Subject: Re: [AccessD] Autonumber when? Gustav, The code needs to work whether going to an MDB or SQL BE. The code works fine for an MDB BE but fails for a SQL BE. The only difference between you code and mine is that I place an "On Error Resume Next" in front of the code that works for an MDB BE but fails for an SQL BE. When the code fails (against a SQL BE), I do the save and then I check the error. The error is > 0 so it falls in and does the "mlngLogID = !PLSL_ID". As far as I can see (other than the lack of the error handling in your code) our code is identical. The code works just fine for an MDB BE but it does fail for a SQL BE. I have stepped through the code and !PLSL_ID is a null value after the update when going against an SQL BE. Yours: >> With rs >> .AddNew >> !PLSL_IDPLSUSR = mlngIDUser >> !PLSL_FE = CurrentProject.Name >> !PLSL_Login = blnLogIn >> !PLSL_WorkstationID = CurrentMachineName() >> .Update >> mlngLogID = !PLSL_ID >> .Close >> End With Mine: >> 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 John W. Colby www.ColbyConsulting.com On 4/6/2011 8:44 AM, Gustav Brock wrote: > Hi John > > No, it was not the same. It works! > > /gustav > > >>>> jwcolby at colbyconsulting.com 06-04-2011 13:57>>> > 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