Re: Problem determining new autonum value after UPDATE before UPDATEBATCH

From: Val Mazur (group51a_at_hotmail.com)
Date: 07/29/04

  • Next message: Val Mazur: "Re: recordset.sort Problems with data types" 
    Date: Wed, 28 Jul 2004 21:36:25 -0400

    Hi Scott,

    You could use SELECT @@IDENTITY SQL statement to get latest identity value
    or you could use Requery method.
    Check next KBs about how to work with identity in ADO

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;232144
    http://support.microsoft.com/default.aspx?scid=kb;en-us;Q195910

    http://support.microsoft.com/default.aspx?scid=kb;en-us;Q221931 

    -- 
Val Mazur
Microsoft MVP
"Scott M" <scottm1100@yahoo.com> wrote in message 
news:uKSJmECdEHA.1184@TK2MSFTNGP11.phx.gbl...
> Using ADO 2.6 in VBA (Access 2002):
>
> I need to know how to determine the value of the autonum field after 
> issuing
> an update statement within a batch transaction.  Ex:
>
> Dim An = Integer ' Autonumber value
> Rst.open sql, cnn, adOpenDynamic, adLockBatchOptimistic
> Rst.Addnew
> Rst.Fields(1)=bla
> Rst.Fields(2)=bla
> Rst.Update
> An=Rst.Fields(0)  <----  returns a blank or null value
> Rst.Addnew
> Rst.Fields(1)=bla
> Rst.Fields(2)=bla
> Rst.Update
> An=Rst.Fields(0)  <----  returns a blank or null value
> Rst.UpdateBatch
> An=Rst.Fields(0)  <----  returns autonum value of last record updated
>
> How can I read the value of fields(0) (my autonum) prior to issuing the
> UpdateBatch?
> Your help is greatly appreciated.
> Scott
>
>
  • Next message: Val Mazur: "Re: recordset.sort Problems with data types"