Incorrect ID value returned after insert, due to trigger

[DatabaseDude]

Here's a routine I'm inheriting, and I'm getting unexpected values after adding a trigger to a table.

tblProposal has an insert trigger, that adds a record to another table upon a record being added. (stating the obvious there, aren't I? sorry)

A record is added to tblProposal in VBA. The ID of that record is then captured to use in following steps.

        rsDest.Update
        v_nIDProposal = rsDest!IDProposal

However, v_nIDProposal is assigned the ID of the other table -- the scope identity if you will. This causes the rest of the code to fail, as no such record with ID value of v_nIDProposal is located.

Is there a more reliable way of capturing the proper IDProposal, thru VBA, after the record is updated? I'm urging the client to make this a stored procedure ultimately, but I'm not sure they want to take time for that immediately.

Thanks in advance!

 

[Remou]

@@Identity works with Access* and from reading, I believe it works with SQL Server, too.

[tt]strSQL="Select @@Identity From tbl"
Set rs=CurrentDB.OpenRecordset(strSQL)
NewID=rs.Fields(0)[/tt]

Perhaps it would suit?


==================================
* The Jet OLE DB version 4.0 provider supports the SELECT @@Identity query that allows you to retrieve the value of the auto-increment field generated on your connection. Auto-increment values used on other connections to your database do not affect the results of this specialized query. This feature works with Jet 4.0 databases but not with older formats.
From:

http://support.microsoft.com/defaul...port/kb/articles/Q232/1/44.ASP&NoWebContent=1

 

[DatabaseDude]

@@IDENTITY returns the ID of the last record inserted; in this case, it yields the same result. I tried also by using SCOPE_IDENTITY, which returned a null.

I think I will just rewrite a small part of this as a stored procedure in order to get the proper ID; but I believe there are other segments of code where I may encounter the same thing, so I'd like to continue exploring solutions.

Thanks!