Return the ID of a generated record

Posted on 2003-02-25
Medium Priority
Last Modified: 2010-05-01
I have a function that duplicates a record in a table.
The table name and the ID to use for duplication are passed into the function.

I need to return the new ID (the one that has just been created)

Normally I would use:

......code to add record
    returnID = !theID

However, theID is a variable... theID is passed into the function as the identity column to duplicate.

How can I return the newly created value for the field theID?


Question by:JamesAStewart
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8018070

The simplest way is to have the function return a value.

Public Function SomeFunc(TheID As String) As String
     SomeFunc = returnID
End Function

This type structure will allow you to pass the function the value of "TheID" and then your code does the update and obtains the ID to return.  Set the function name equal to that value:   SomeFunc = returnID

Your calling of the function would be:

    NewReturnID = SomeFunc(OrigID)

The value, NewReturnID, would then hold the value returned from the function.

Ask if you need more clarity.


LVL 14

Expert Comment

ID: 8018442
An alternative to returning the value would be to pass your theID into the function or Sub ByRef  and then it will change for you.  See example below

Dim theID as String

theID = "5"

Update theID

'theID now equals 7 here.

Private Sub Update(ByRef theID as String)
   theID = "7"  'This will put 7 in the ID outside this function.
End Sub

I am not saying this is the better way of doing this, just an alternative.

Expert Comment

ID: 8019345
Sorry, are you having trouble 1) finding a way to return the new ID or 2) finding out what the new ID is?

I'm assuming it is a DB, access or sql server?

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 8023865
The problem is not how to return a variable from a function, it is how to find out what the new ID is.

Here is a simplified example or what I'm trying to do:

results = aFunction("Table1","anID")

Function aFunction(Table, IDRecordName)

Set rst = New ADODB.Recordset
rst.Open "Select * From " & Table, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        With rst
            !afield = Null
            newID = !IDRecordName
        End With


End Function

The problem lies with this line:
newID = !IDRecordName
IDRecordName is a variable (it is actually equal to "anID".

So for this example, newID = !anID

Author Comment

ID: 8023873
DB is SQL Server 2000

Author Comment

ID: 8023947
DB is SQL Server 2000

Author Comment

ID: 8024216
DB is SQL Server 2000

Accepted Solution

luiscantero earned 150 total points
ID: 8024712
For SQL Server you need to use "@@IDENTITY", you will find more information in your help file.

Here's sample code:

Dim loConn, lsSQL, loRs
Set loConn = CreateObject("ADODB.Connection")

' Open a connection to the database

' Insert a new record into the table
        "INSERT INTO tMembers (MemberName) VALUES ('Manohar');" &_

' Execute the SQL statement
Set loRs = loConn.Execute(lsSQL)

' Get the inserted ID
llID = loRs.Fields("NewID").value

' Close the connection
Set loConn = Nothing

This code was borrowed from here, where you will find more details about it:

Just for the record, I don't think there is a function that works in the same way in MS Access. For access you would need to SELECT the item using some of the known values that you just inserted.


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question