Instantly update a table based on inputs to a continuous form

[ramprat]

Hi Everyone,

I have been going crazy trying to figure this out and I'm sure it's easy. I have a continuous form based on a table. Once someone enters a new record on the form I have code in the "before insert'" property of the form that reads the max record id from the table and adds 1 to it and enters it on the form. (I'm using this in place of an auto number field to record a unique id). I cannot use an auto number field here so it must be done this way. Unfortunately the record is not added to my table until after the person is finished filling out all the fields. The problem is that before they finish filling out the data, another user could try to add a record and will end up with the same max record Id because the other person's record hasn't been added to the table yet.

Is there any way to immediately add the record to the table the second someone starts adding a record on the form?

Thanks for any and all help.

Ramprat

[missinglinq]

Your problem is in your choice of events for this code. The BeforeInsert event occurs when the user types the first character in a new record, even before the Form_Dirty event fires, so as you've found out, a number of people could be working on new records, all of which have been assigned the same auto-incremented number. The trick is to assign this number at the ***last possible moment*** rather than the ***first possible moment.*** Move your code to the Form_BeforeUpdate event (make it the last line, if you have other code in this event) and you should be fine. The chance of two records being saved at the precise same moment is almost non-existent. Most people, when posting this kind of code, warn about the possibility, but in years of using this type of hack I've never seen it happen, nor have I seen posts from anyone who has.

BTW, an auto-incrementing number like this is the way to go, rather than using an AutoNumber. AutoNumbers should not be seen or heard! They should simply remain in the background for Access to use for housekeeping.

Linq ;0)>

[ramprat]

Thank you Linq!! That was just what I needed. Thanks again.

Ramprat