?
Solved

Return the ID of a generated record

Posted on 2003-02-25
8
Medium Priority
?
203 Views
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
.Update
    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?

TIA

James.
0
Comment
Question by:JamesAStewart
[X]
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
8 Comments
 
LVL 2

Expert Comment

by:Sweat
ID: 8018070
JamesAStewart,

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.

Sweat



0
 
LVL 14

Expert Comment

by:jjardine
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.
0
 

Expert Comment

by:luiscantero
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?


Luis
0
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

by:JamesAStewart
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
            .AddNew
            !afield = Null
            .Update
            newID = !IDRecordName
        End With

aFunction=newID

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
0
 

Author Comment

by:JamesAStewart
ID: 8023873
DB is SQL Server 2000
0
 

Author Comment

by:JamesAStewart
ID: 8023947
DB is SQL Server 2000
0
 

Author Comment

by:JamesAStewart
ID: 8024216
DB is SQL Server 2000
0
 

Accepted Solution

by:
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
loConn.Open("DSN=myDSN;UID=something;PWD=Something;")

' Insert a new record into the table
lsSQL = "SET NOCOUNT ON;" &_
        "INSERT INTO tMembers (MemberName) VALUES ('Manohar');" &_
        "SELECT @@IDENTITY AS NewID;"

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

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

' Close the connection
loConn.Close()
Set loConn = Nothing


This code was borrowed from here, where you will find more details about it:
http://www.kamath.com/tutorials/tut007_identity.asp

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.


Luis
0

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