ACCESS 2010 Update primary key in a form through a macro

[geekatron42]

I hope I am posting this in the correct location, but here goes. This access issue has been troubling me for a while and I have yet to find a viable solution anywhere on the net.


I have an Access DB that is split in two (front end and back end). The primary key for my table, called Notice, is set to number, however in the form I have this function: DMax("[NoticeID]","[tblNotice]")+1
This essentially allows me to use autonumber without actually using the autonumber data type (which I would rather avoid using). So anytime a user opens the form, the next available NoticeID is assigned.
The problem I am having is when two people have the front end DB open, and navigate to a blank entry in the form to fill they are both assigned the same NoticeID. When one user eventually saves before the other, it writes that notice to the table. However when the second user tries to save the record, the form spits out an error that you cant have duplicate primary keys.
I have tried to use a requery macro in the BeforeUpdate event for the form and the NoticeID field however I haven't had any luck. Any help on this would be appreciated.
Thanks,
Zack

[June7]

I handle this by immediately committing record with the new ID to table. Not impossible for multiple users to generate the same ID but greatly reduces the odds. This raises question of do you want to allow users to abort the entry and how do you want to handle?

[Missinglinq]

Because I usually give users the option to dump a New, unsaved Record, and usually don't allow Records to be saved until all data has been Validated, which cannot be easily done, after the New Record is saved, I normally do this by doing the assignment in the Form_BeforeUpdate event. Because this occurs in the last nanosecond before the Record is saved, I've never had a duplication problem, in over a decade of developing Access apps.

I've only used June7's method once, I believe, because the users simply had to know the number (for another, non-database application) while the New Record was being entered, which happens if you assign the number in the Form_Current event and immediately save the Record.

Linq ;0)>

[June7]

Yes, that's my situation as well. Need to show the generated ID to users. I allow for aborting the entry and the saved (but unused) number is retrieved and used in the next record entry. Going on 4+ years and I think we got a duplication error once.

[geekatron42]

Sorry for the delay in response. I would need the primary key that is generated since it is used outside of the database.

@June7: The only problem I see with committing the key to the table right away would be that if the user aborts I want the key to still be available.

@Missinglinq: I tried a couple of things with the Before_Update event; mainly a repaint macro however it didn't work.

I am not too familiar with macros or VBA unfortunately, but I am working on it. Thanks again for the quick reply's

Zack

[alcapps]

I like your Idea about the autonumber..

I have done this before as well. I used the Form_BeforeInsert to handle the the timing of the ID beening saved.
I would call the number again in the before insert event and save the id at that time. DMax("[NoticeID]","[tblNotice]")+1

I've also.. had a timer function of the form.. to update the Id field every 10 seconds.. in a multiuser environment. You can turn up or down the checking on the form by using a default table. Load the value in the form on open or load.. say 10 seconds..

so as the user is working the form will write the number in the id field.. The trick is in this is to have the form timer save where you were if the number has to change. After the number changes it can set the focus back. (This is not easy sometimes). If you don't need to change the number every thing happens unnoticed.

just a thought if you wanted to try this.

Form_BeforeInsert ..
and Form Timmer event

[June7]

In my setup, if the user aborts, the key is still available. What would you want to do with it? In my db the aborted key is saved without data and then is retrieved for use next time a sample is logged in.