Multi-User. No Record Error

[Pisteuo]

I am using A2003. I have an single-Form that uses =nz(DMax(field),0)+1 for the key's default value. The key is set for no-duplicates.

Multiple users are stumbling on each other when concurrently using the form.

At this point, it's easier just to tell my small group to not enter the application when others are in it. At least until I further understand multi-user environments in Access.

My immediate concern though is an integrity problem. When two users are in one record (lets say #2550) on the form, #2550 will retain data for the first user that closes. When the second user closes, the form closes with no error. There is no hint to the unaware user that the record was not saved.

Error handling code is not triggered either. And I have tried both lock options in the database options.

How can I either create an error situation to keep records from disappearing without the user knowing or lock the form to one user?

Thank you.

 

[boblarson]

You don't assign the value until the last possible moment - the Form's Before Update event.

 

[boblarson]

There's also a method somewhere around here that uses a table with a single entry and it locks that table when someone is getting a value and then releases so others can. Not remembering how it is completely set up. But then, as I said, it also should assign the value at the last possible moment.

 

[Pisteuo]

Thanks.

I have tried this and the form opens to record #0 which also creates integrity issues.

How do I provide a "new" record in the meantime? Is there a way to duplicate the autonumber behavior of using a generic record until update?

 

[boblarson]

Thanks.

I have tried this and the form opens to record #0 which also creates integrity issues.

No, it does NOT create integrity issues. The only issue is that you SEE #0 because it has not been assigned. So, don't show that field when someone is looking at it. You can leave it while you do your designing but then hide it.

How do I provide a "new" record in the meantime? Is there a way to duplicate the autonumber behavior of using a generic record until update?

A record is not saved until you move off it or close the form, or specifically save it. So, you just move to a new record like any normal way, but you assign the PK number in the form's BEFORE UPDATE event (as the very first line) so that it then has the number to work with. Not a problem, really. But you have to do what I said and not assume that it won't work without actually trying it.

 

[Pisteuo]

Great. So I kept the =nz(DMax(field)+1 as the key default, and in addition I added it to the beforeUpdate event. Looks like it is working.

Thanks again.