Getting Autonumber value BEFORE update

[LeanneGodney]

Hi there, does anyone know how I can get the autonumber value BEFORE my record is updated/added? I am running code in the "before update" section of my form and it uses the ID number to append a record to another table...

 

[LeanneGodney]

I've just worked something out that solves this:

In the BEFORE UPDATE event I've set the value of a public variable "RunAfterUpdate" to true. Then in the After Update event I've told it that if this variable is true then run my code, which correctly picks up the new autonumber value.

I was very impressed to see that even if the user clicked onto a DIFFERENT record and thereby evoked the Before Update event, the After Update event still considers the edited record to be the current record and only moved to the clicked record after both the Before Update and After Update events have run. Therefore the autonumber value assigned to the new record is still the value that I receive when grabbing the current records ID.

Hope this makes sense for anyone in a similar boat to me!

Leanne

 

[dwight1]

I had the same problem, What i did was in the vb code of the button i had code some thing like this

DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious

Well this generated the number. I know this is not the most effective way but helped me at that point.

Can you advise,What i want to do is, have an auto number created and tie that auto number to the id in the combo box. The steps would be like this

1. Select the id from the combo.
2. check the box (true/false) that generates the autonumber
3. Have id and autonumber in the same table through append. I am having problem here.

Can any one help.

I want the result to be

ID Autonumber

1 7003
2 7004

Thanks in advance

Dwight

 

[danvlas]

In SQL Server, Autonumber (Identity) is generated after the record has been saved. Therefore, there is no guaranteed way to get the value before that.

In Access (Jet), the value is generated as soon as a new record starts being created and is lost if the record is not saved. Therefore, it may be even dangerous to use the generates value before saving the record.

Generally, the sole purpose of the autonumber is to uniquely identify an existing row in the database and should be transparent to any user. It has a great value for database administration, queries, but it is a big mistake to give it any business meaning.

Why would you need it before saving the record?


HTH

Dan




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html