[AccessD] Autonumber when?

Although that theory may get blown out of the water as Err should in fact retain its value even after the .Update statement. I just checked with this


Sub Err_Test()
Dim n As Long
    On Error Resume Next
    n = 10 / 0
    n = 10
    Debug.Print n, Err.Number
End Sub

Which outputs 10, 11 (as error 11 is Div by zero).

But I'd still like to hear what results you actually do get by using .Update and then grabbing the autonumber value.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Wednesday, April 06, 2011 11:13 AM
To: Access Developers discussion and problem solving
Subject: Re: [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