Returning Autonumber identity after update

[VBAhole22]

I am writing an Access project in Access 2k against a MS SQL Server db:
I have a form/subform setup where the user enters information on the form and when they tab out of the last field I validate it using the Before_Update event. If all of the data passes validation the record is entered. I then want to put up a message box telling the user the unique id that has been assigned to the record in the autonmuber field.
Currently I am doing this with a stored procedure:

Alter Procedure GetUniqueIDBack
As
SELECT TOP 1 Collection
FROM Collections
ORDER BY Collection DESC
return

That gets called in this same event.

I have to problems with this:
1. It's always a record behind. I guess because before update occurs before update. I know it's one behind because the number that gets transferred to my subform is the right one. Would there be a better event that occurs after the record is in?
2. I know from reading posts here that this isn't the best way to do this because another user in a multiuser environment could sneak in and enter a record before this proc ran. What's a better way to do this?

 

[crosmill]

I don't really use projects, but I do use SQL Server. You can use the @@Identity function when you create a new record to pass the created ID number back.

 

[VBAhole22]

Yes

Thanks for the suggestion. That's a good idea that I am going to work into my stored procedure. This way I will know for sure that the id returned is the same one associated with the record. Unfortunately, I'm not really sure how I would do that. See the record gets submitted when the form gets completed, I'm assuming right after the Before_Update event. I don't really submit the record using a query so I couldn't incorporate @@identity into any kind of query.

 

[crosmill]

Any access projects guru jump in here, but with access records don't get updated until focus is moved away from the record.

Is this right?

In any case, I think what you'd have to do is to set button click or event to run a custom built Stored Procedure, probably from VBA, that could then handle the returned parameter and populate a text field on a form.

The procedure would look like this:

CREATE PROCEDURE [dbo].[usp_addRecord]

AS

DECLARE
@id VARCHAR(20)

......SQL code here (add record)

SET @id = @@IDENTITY

RETURN @id

 

[VBAhole22]

Not many Access project gurus out there, unfortunately.
I have come up with an interim solution that seems to work on occasion. I built a stored proc that returns the highest id when the records are sorted desc.
I moved this proc to the After_Update event of the form and this does throw up a message with the correct id #.
Unfortunately this event doesn't seem to be firing all of the time. Why, I have no idea.
One other problem is that I use this form to view and edit records so I don't want this message popping up when the user is doing these 2 tasks. When I had the stored proc on the Before_Update event it was in a conditional statement that checked Me.NewRecord. Well when this proc is moved to the After_Update event the record is not new anymore so I had to remove the condition.