"Gunnar Liknes" <g_liknes.Tabor tunderscores@g_ lobal-satcom.com> wrote in
message news:40******** @news.broadpark .no...
"DFS" wrote "Gunnar Liknes" wrote in
Is it possible to make a stored procedure or function to generate a return value?
I have a stored procedure which inserts a new row in a table. I would like that proceudre to return the ID (autogenerated field) of the row after it has
inserted it.
Try this:
Public Function (newRecordID) as Long
'place your stored proc code to add new row here
'return latest ID
newRecordID = DMax("ID","Tabl e")
End Function
Note: you need to add some error trapping in the function to ensure your
Insert completes successfully, or the newRecordID might not be the ID of
the newest record.
Thank you:-) But Where do I put in this code? If I try to enter it in SQL
view
I get an error message saying "SQL Batch has to begin with ALTER or
CREATE"...
Add this Function to a Module. Your stored proc code (that adds a new row)
also goes inside this function. Then the new Function is called in place of
your stored procedure.
Something like:
Public Function (newRecordID) as Long
'this function adds a new record and returns the ID
'code to add new row here
db.Execute("INS ERT INTO Table (Field1, Field2) VALUES ('Gunnar','test ');")
'return latest ID
newRecordID = DMax("ID","Tabl e")
End Function
Public Sub testNewRecordID ()
msgbox "Record added. ID = " & newRecordID()
End Sub
