[AccessD] Autonumber when?

Because the record isn't actually saved to the table until you do
.Update.  It's like creating a new record on a data entry form or
directly in the table.  You can click the AddNew button and enter data
in the fields but until you move to another record or hit the Save
Record button, the record isn't saved yet.

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

It turns out that I have some other problem.  If I remove the On Error
Resume Next the .Update fails with an "odbc call failed" error message
returned by Jet and in fact the record never stores into the table, thus
there is no PK to pull back out.

So why does it fail on the update but not on the addnew?

:(

John W. Colby
www.ColbyConsulting.com

On 4/6/2011 10:21 AM, Rusty Hammond wrote:
> Yeah, there's something about linked SQL tables you have to set the 
> recordset bookmark to the lastmodified record, after the update, 
> before you can read the autonumber value when creating new records.  
> It should work for both linked SQL tables and tables in an mdb so 
> trying to read the value before the update isn't really necessary.
>
> Martin sent this link earlier that talks about it:
>
> http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/3507c84
> 5-
> 66a6-4e13-8876-f6e5faae8dba/
>
> I've had this code running in an application for several years in 
> Access
> 97 and 2003.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Wednesday, April 06, 2011 9:07 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Autonumber when?
>
> Rusty,
>
> The following is the actual code.
>
> '
> 'Adds a record to the table saying that a specific user logged in at a

> specific time '
> '*+ Private class functions
> Private Function mPLSLogin(blnLogIn As Boolean) Dim rs As 
> DAO.Recordset Dim db As DAO.Database
>
> On Error GoTo Err_mPLSLogin
>
>       Set db = dbDAOCurr
>       Set rs = db.OpenRecordset("usystblPLSLog", dbOpenDynaset,
> dbSeeChanges)
>       If mlngIDUser = 0 Then Exit Function
>       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
>
> Exit_mPLSLogin:
>       On Error Resume Next
>       Set rs = Nothing
>       If Not (rs Is Nothing) Then rs.Close: Set rs = Nothing
>       Exit Function
> Err_mPLSLogin:
>       Select Case Err
>       Case 0      '.insert Errors you wish to ignore here
>           Resume Next
>       Case Else   '.All other errors will trap
>           Beep
>           PLSLogErr Err.Number, Err.Description, Erl, cstrModule, 
> "mPLSLogin"
>           Resume Exit_mPLSLogin
>       End Select
>       Resume 0    '.FOR TROUBLESHOOTING
> End Function
>
>
> John W. Colby
> www.ColbyConsulting.com
>
> On 4/6/2011 9:47 AM, Rusty Hammond wrote:
>> When you set your recordset are you using the dbSeeChanges option?
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> **********************************************************************
> WARNING: All e-mail sent to and from this address will be received, 
> scanned or otherwise recorded by the CPI Qualified Plan Consultants,
Inc.
> corporate e-mail system and is subject to archival, monitoring or 
> review by, and/or disclosure to, someone other than the recipient.
> **********************************************************************
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************