[AccessD] Autonumber when?

"I said that the first method did not work, but the second (which should work) also did not work."

And here is why it does not work...

         On Error Resume Next
         mlngLogID = !PLSL_ID
         .Update
         If Err Then
             mlngLogID = !PLSL_ID
         End If

You have on Error resume next. Fair enough, but when the statement mlngLogID = !PLSL_ID is executed you will get an error. Because of the Resume Next all that happens after the error is that the .Update statement executes. The .Update statement will not cause any error so when the next line of code runs, Err = 0 and so the mlngLogID = !PLSL_ID does not execute.

If you use

>       With rs
>         .AddNew
>           !PLSL_IDPLSUSR = mlngIDUser
>           !PLSL_FE = CurrentProject.Name
>           !PLSL_Login = blnLogIn
>           !PLSL_WorkstationID = CurrentMachineName()
>         .Update
>          mlngLogID = !PLSL_ID
>         .Close

That should work fine with *both* SQL server and native Access apps.


Lambert
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, April 06, 2011 10:20 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Autonumber when?

If you go all the way back to my original post, I showed where I modified that first method to a second method where I do what you are discussing...

I said that the first method did not work, but the second (which should work) also did not work.

Look for:

 >>>I thought something simple like doing an on error and then checking as follows would work:

and read what follows...

John W. Colby
www.ColbyConsulting.com

On 4/6/2011 10:01 AM, Heenan, Lambert wrote:
> As I said, look again John.
>
> You have...
>
>        With rs
>           .AddNew
>           !PLSL_IDPLSUSR = mlngIDUser
>           !PLSL_FE = CurrentProject.Name
>           !PLSL_Login = blnLogIn
>           !PLSL_WorkstationID = CurrentMachineName()
>           mlngLogID = !PLSL_ID
>           .Update
>           .Close
>       End With
>
> But Gustav (and me if I'd been quick enough) suggests...
>
>       With rs
>         .AddNew
>           !PLSL_IDPLSUSR = mlngIDUser
>           !PLSL_FE = CurrentProject.Name
>           !PLSL_Login = blnLogIn
>           !PLSL_WorkstationID = CurrentMachineName()
>         .Update
>          mlngLogID = !PLSL_ID
>         .Close
>       End With
>
> i.e. there is no attempt to read the autonumber value until *after* the record has been created by the call to .Update.
>
> Lambert
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com