Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Return the ID of a generated record

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
JamesAStewart
Asked:
JamesAStewart
1 Solution
 
SweatCommented:
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
 
jjardineCommented:
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
 
luiscanteroCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
JamesAStewartAuthor Commented:
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
 
JamesAStewartAuthor Commented:
DB is SQL Server 2000
0
 
JamesAStewartAuthor Commented:
DB is SQL Server 2000
0
 
JamesAStewartAuthor Commented:
DB is SQL Server 2000
0
 
luiscanteroCommented:
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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now