Getting the last autoIncremented number with ADO

[rewdee]

For simplicity, I have two tables (e.g. tblCustomer and tblOrders). Say tblCustomer primary key is an autoIncrement field (CustID) and tblOrders related field is CustID.

If I want to create a new order for a new customer, I first need to create a new customer, retrieve the new CustID, and then make an entry into tblOrders. I know how to write an append query (INSERT INTO ...) but how do I retrieve the last new CustID I created to use in the append query for tblOrders?

If I was using DAO, I could write something like:

Dim lngCustID  as Long

With currentdb.OpenRecordset("tblCustomers")
  .AddNew
  !Name="some Customer
  blah, blah
  .Update
  .move 0, .LastModified
  lngCustID=!CustID
end With

With currentdb.OpenRecordset("tblOrders")
  .AddNew
  !CustID=lngCustID
  blah, blah
  .Update
End With

How do you do the equivalent with ADO?

Thanks,
Rewdee

 

[Craig0201]

Look up DMax. Doesn't need DAO or ADO.....

Craig

 

[Mute101]

DMAX has a few problems, if this is a muliuser DB it is conceivable that another new Customer could be added before you call the dmax function.

Looking at your example the best method I can think of is to scrap the AutoIncrement ID and use a manual Id field instead. Store a number in a table and just call the number add one save the number and dump into a variable then use this variable through your recordsets.

I can post code if you need, I know its not an easy answer but autoincrement fields are notoriously unstable and I dont touch em with a 40ft barge pole!

Regards
Simon ----------------------------------------
Of all the things I have lost in my life, the thing I miss the MOST is my mind!
----------------------------------------

 

[Mute101]

Also,

If you really want to use an autoincrement field try this;

Dim Rs As ADODB.Recordset
Dim lngID As Long

Set Rs = New ADODB.Recordset
Rs.Open "tblCustomers", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
With Rs
    .AddNew
    !Name = "Simon"
    lngID = !custID
    .Update
End With
Rs.Close
Set Rs = Nothing

MsgBox lngID

This example takes advantage of the way ADO uses recordsets, as soon as you write to the new record the ID will be available.

Sim ----------------------------------------
Of all the things I have lost in my life, the thing I miss the MOST is my mind!
----------------------------------------

 

[rewdee]

Thanks Sim,

I will remove the AutoIncrement Field because it makes migration to databases that doesn't have AutoIncrement fields easier (Interbase, Oracle).

Rewdee

 

[trueharted]

Mute101,

I like your manual ID idea. If you wouldn't mind posting some example code for it, I'd appreciate the time you'd save me. Thanks ahead of time.

 

[MichaelRed]

Mute101,

I liked the approach - even though others appear to dis the "AutoNumber" Scheme. I minimally modified the "Hard Coded" variables to be references to input arguments and return the value to the caller.

Public Function basNewAutoNum(tblName As String, _
                              AutoFldName As String, _
                              FldName As String, _
                              FldVal As Variant) As Long

    'Mute101; Tek-Tips thread705-277210
    'Adapted for general Use by Michael Red     8/29/02

    '? basGetAutoNum("tblTestExpFixed", "Id", "Name_Desc", "Choclate")
    '17


    Dim Rs As ADODB.Recordset
    Dim lngID As Long

    Set Rs = New ADODB.Recordset
    Rs.Open tblName, CurrentProject.Connection, adOpenKeyset, adLockPessimistic

    Rs.AddNew
        Rs(FldName) = FldVal
        lngID = Rs(AutoFldName)
    Rs.Update

    Rs.Close
    Set Rs = Nothing

    basNewAutoNum = lngID

End Function

With some further minor modification, the approach should be useable for most db engines, to set a minimal amount of information into a "parent" record to enable entering some details into a "child" record. In most cases, I prefer to just use a modal form to get the necessary info fromt he user before permitting the entry of dependent records.

The example cited in the original post is a good example. If an order is accepted, the customer needs to be identified by more than just the name an AutoNumber. You cannot either bill or deliver products to a customer until you enter the proper information, so placing (entering) the order is useless (at best) until the customer info is available, so just add a command button to the orders forf, and if there is a new customer, click it and open the Customer form in data entry mode. It can (should) have verification and validation code for the fields, so you will not get ordering authourity / approval until the necessary info is entered here.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Unclejedtyree]

I'd like to add my own question to this discussion. I'm not into programming VBA.........I'm just trying to set default values for blanks on a form I'm creating to add to a table. I want the default values to always be what the value of the most recent added record was before it.
Here's the formula I have in the "default value" area of the properties.

=DLookUp("[MONTH OF:]","tbl_Action_List",[ID]=DMax("[ID]","tbl_Action_List&quot)

I should get the month of the last entered record as the value, but instead it's giving me the value in the very first record. Am I making sense?