Re: autonumber

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Why do you need to know the AutoNumber before the adding of the record?

You can find out what the last AutoNumber value was by using the SELECT
@@Identity statement. This will return the autonumber value of the last
entered record...

'-----------------------Start Code------------------------------
Dim RS As Recordset
Dim CNN As Connection
Set CNN = New Connection
Set RS = New Recordset

'Build SQL Statement
Dim strSQL
strSQL = "INSERT INTO Customer (Name) " & _
"VALUES ('(Customer1)')"

With CNN
.Open ConnectionString

.Execute strSQL

'Get the assigned autonumber
Dim lngID As Long
Set RS = .Execute("SELECT @@Identity FROM Customer")
lngID = RS.Fields(0).Value

If RS.State = adStateOpen Then RS.Close
Set RS = Nothing

.Close
End With

Set CNN = Nothing
'---------------------------------------------------------------------

--
Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
http://www.veign.com/vrc_main.asp
--


"Dan P" <dp55@xxxxxxxxx> wrote in message
news:el%23qybooFHA.2156@xxxxxxxxxxxxxxxxxxxxxxx
> rs.addnew will assign an autonumber for you.
>
> Is there a way to know what the next autonumber to be assigned will be
> before the rs.addnew by using a command instead of something like
> rs.movelast
>
>
> VB and ADO/Access 97 database
> Thanks
>
>


.

Relevant Pages

  • Re: ADO AddNew Autonumber field problem
    ... Use SELECT @@Identity to retrieve the value of the Autonumber field. ... Dim CNN As Connection ... Set CNN = New Connection ...
    (microsoft.public.vb.database.ado)
  • Re: autonumber
    ... My database is single user only. ... > Why do you need to know the AutoNumber before the adding of the record? ... > Dim CNN As Connection ... > Set CNN = New Connection ...
    (microsoft.public.vb.database.ado)
  • Re: autonumber
    ... I don't think you will be able to use the @@Identity statement and rely ... How To Return Record's Autonumber Value Inserted into Access DB: ... >> Dim CNN As Connection ... >> Set CNN = New Connection ...
    (microsoft.public.vb.database.ado)
  • Re: DAO Recordset Help
    ... the autonumber on the table should not advance." ... Autonumber fields would all be changed from sequential to random. ... to a recordset). ... Set the form's recordsource to a query that will give you the desired ...
    (microsoft.public.access.modulesdaovba)
  • RE: Open table exclusive
    ... Until this recordset is closed, any other attempts to open it produce error ... 'Autonumber table must contain only one field and one record or less ... 'GetAutoNum returns string datatype, as all custom autonumers are stored ... Case dsAutoNumPurchaseOrder ...
    (microsoft.public.access.modulesdaovba)