Return the ID of a generated record

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

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.

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

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

DB is SQL Server 2000

DB is SQL Server 2000

DB is SQL Server 2000