New record lock problem

[Sean O'Halloran]

I've searched the old posts and MS support for hours and I'm not finding the answer, yet I feel I'm simply missing something basic...please be patient.

I have 20+ concurrent users in an Access 2000 FE/BE Win NT environment. They are constantly entering 'Contact Notes' which are sub records of a larger Client record. Contact notes are entered in a continuous subform. When one user clicks into the new blank record but does not enter any data, any second user attempting to simultaneously create a new record gets the "Cannot update, currently locked" error message. As soon as the first user enters a character and the autonumber gets assigned, the blank form unlocks for the second user.

Is this an example of opportunistic locking? Please point me in the right direction so I can forge a solution.

Thanks in advance

 

[pono1]

Sean,

It's a bit of a hack but you can put code in the Continuous form's Current Event to create the autonumber when the rec is new. Roughly...

If me.newrecord = True then
     Me.TxtBox1 = "L"
     Me.TxtBox1 = ""
End If

Regards,
Tim

 

[Sean O'Halloran]

...in the dark night of my despair a bright candle of inspiration flares....

Tim, my deep thanks for your idea - hack or not, I think it will solve my problem - as a friend of mine says "One man's hack is another man's hairpiece."

Got time for one more question? After inserting your code I get the following: "ContactNotes.ContactDetails cannot be a zero-length string". I can work around this by leavaing a character in the field, but I'm thinking this points to an underlying issue that may be causing the original problem, no?

I'll start researching this - but can you point me in a direction?

Thanks again for your solution. - Sean

 

[a.sinatra]

Try this?..

If Me.NewRecord = True Then
     Me.ContactDetails = "X"
     Me.ContactDetails = Null
End If

________
Ford El Falcon Picture

 

[Sean O'Halloran]

Works great. Thank you. Am I wrong in thinking that the "can't be zero-length string" is not normal?

 

[a.sinatra]

You may have a custom validation rule.
________
PENNY STOCKS TO WATCH

 

[Sean O'Halloran]

Thanks; I'll check that ASAP. I appreciate you taking the time to help a non-coder. - Sean

 

[pono1]

Works great. Thank you. Am I wrong in thinking that the "can't be zero-length string" is not normal?

Sean,

The zero length string error: Open the table that holds the offending field. In design view, move to the field and then look down at the bottom left corner of the window. You should see a setting that says Allow Zero Length. Changing that setting to Yes would -- I think, not tested -- get rid of the error message using the code I posted. Yet if Anthony's "null fix" is working for you, there's no reason I can think of -- at the moment -- to fiddle with things.

And...20 users? That's terrific.

Regards,
Tim

 

[Sean O'Halloran]

Tim, thanks again for your help. I've implemented the work around that you and A.Sinatra suggested, and it is working beautifully.

And, yeah, we've got over 100 users spread across 3 linked databases - and I've yet to get any formal Access training, which is causing me headaches because my programmer guru left last summer and I'm in charge of maintaining and upgrading the applications (state government is SO forward-thinking). I often wish I had someone to review what I'm building; the next best thing is to come here and search for gold...

Maybe because I was beating my head against the wall for so long over this I've lost perspective, but it just seems that the behavior I described in my thread starter is just not normal. Of course, I have only built three databases, all in Access, and all with the help of a progammer, so I'm still in diapers, but since I couldn't find any past posts here with folks complaining of this behavior, I assumed that it was some wierd glitch we had built into the system...but is this "single-instance-of-an-object" locking normal behavior in Access?

If you don't have time to answer I certainly understand. Your help has definitely lowered my blood pressure - thanks again to you and A.Sinatra ( by the way, A., I love your music... )

Sean

 

[pono1]

Sean,

I don't have a definitive answer for you -- the "single instance of an object" reference, is that from another thread? -- but you gave me the biggest laugh I've had in weeks with your last sentence. Thanks for that.

I can, however, draw an inference on what's happening given the info in your initial post: An "interim" (my term) new rec is created by User 1 as he or she "clicks into the new blank record"; it's interim in the sense that an autonumber has not been assigned yet.

Access locks the interim rec so that User 1 has full control.

User 2 repeats the behavior of user 1 and thereby accesses the same interim record, with Access denying User 2 the right to do anything but read the record since User 1 was first (as his name would indicate) in accessing the record.

By forcing, through code, User 1 to assign an autonumber to the record, the interim record is destroyed (or something -- we don't know precisely what happens underneath the scenes; or most of us don't) and another "real" record is created -- a rec that is discrete in that it has been assigned a unique number. Now User 2 is free to create their own unique record because Access either releases the lock on the interim rec or the old interim rec no longer exists...

Hope that lowers the blood pressure a bit more. Keep up the good work.

Regards,
Tim

 

[Sean O'Halloran]

Tim, thanks for the further insight. No, the "single instance" terminology came from a network guy I know; I actually searched the forum for that term and came up blank.

My Best Regards - Sean