Get record number of newly created record.

[IfButOnly]

I have an unbound form in which I need to update two tables (site & Contact) with new records, and create an entry in an index file - I use "INSERT INTO" to update the tables. The two tables are both keyed with an autonumber (they have a many to many relationship).

Can anyone help me with how I get the record numbers of the new records that I added to the 'site' and 'contact' tables, so I can create the index file record?

 

[llkhoutx]

Why can't you add an autonumber index field to your table?

 

[VBAhole22]

Might work?

One thing you might try is assing a datetime field (date entered) and then after you submit the record query the data base to get the TOP 1 id value with a sort ordered by date entered

 

[IfButOnly]

The autonumber index field doesn't help as I need to get the keys of the site and contact records to populate the index field.

The datetime field may work in this particular case, but was hoping there was some method that was more certain to be unique and more elequant.

Thanks guys.

 

[Travis]

Public Function SaveData() As Variant
    Dim rstContact As ADODB.Recordset
    Dim rstSite As ADODB.Recordset
    Dim cnnCur As ADODB.Connection
    Dim ContactID As Long
    Dim SiteID As Long
    
        Set cnnCur = CurrentProject.Connection
        Set rstContact = New ADODB.Recordset
        Set rstSite = New ADODB.Recordset
        
        rstContact.Open "Select * from tblContact", cnnCur, adOpenDynamic, adLockOptimistic
        rstSite.Open "Select * from tblSite", cnnCur, adOpenDynamic, adLockOptimistic
        
        With rstContact
            .AddNew
            'List each field and set it equal to its value
            !ContactName = "Data"
            !stuff = "Other fields Data"
            .Update
            ContactID = !Id
        End With
        
        With rstSite
            .AddNew
            'List each field and set it equal to its value
            !Site = "Data"
            !stuff = "Other fields Data"
            .Update
            SiteID = !Id
        End With
            
        SaveData = Array(ContactID, SiteID)

End Function

 

[IfButOnly]

Travis, tried the procedure you provided but it comes up with the following error on the .Update statement for rstContact:-
----------------------------------------------------
Run time error '-2147217887(80040e21)':

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data, remove the index, or redefine the index to permit duplicate entries and try again.
-----------------------------------------------------

The only differences to the code you provide are :- ran it as a sub (from the Save button), and not a function; inserted update fields for each field in the record (eg !Street = Me.Street).

Other than 'Do you know what the problem is with the above?', I have a couple of other questions-

'ContactId = !Id' will return the new key?

Why the array function?

Many thanks...............Peter R.

 

[Travis]

Peter,

Your runtime error is caused by setting a field equal to a value that you already have in the table for that field (Which has a no duplicate rule set). This could be the setting of a Primary Key or another field that you have set up as index-No Duplicates.

ID was the name of my AutoNumber Field. Yours may be different. What happens is that after the Update the record is saved but you are still on this newly entered record. This is what allows you to retreive this AutoNumber Field.

The Array was to return both values One from the Contact table the other from the Site Table.

With you adding it to a sub you don't need to do this.

 

[IfButOnly]

Thanks Travis, that is working great now.

I originally put in ContactID = !Id, thinking !Id was a generic variable for the returned id. On changing this to the primary key of the table, the error no longer appeared.

Many thanks.

ps. I have no idea of the differences between DAO, ADO, VBA - when, where and why to use either, etc. Can you, or anyone else out there, point me to some reference material which may clear it up for me?