Major Form Problem

[mcbass1]

I have a database that will be used when our system is down to receive requests from customers and to dispatch out those requests. If anyone will take a look at the "Dispatch Panel" form and find what is causing this not to work, I'd be forever in your debt. A record is taken on the receiver screen, which then goes to the dispatch panel. After it is routed, it moves to the next column, and it moves across the screen until it's completed. The panel is set to requery every 40 seconds or so, or the user can requery it by pushing a button.

The problems start when I have multiple dispatchers opening the same records. Right now records are locked if another user is vieiwing it. I'd rather multiple users be able to update a single record at the same time, but I don't think it can be done. I've added a check box to check when a user opens a record, and then it tells the next person who has the record open. This works for the most part, but I am having a problem with a few calls being linked somehow. When one of the calls is opened and another one of the calls is opened, it is locked but not in use by anyone else. I'm also getting a "Object invalid or no longer set" error. I had to dump a lot of forms and queries to get this file small enough, but take a look:

Thank you,
Mike

 

[Pat Hartman]

No it is not possible to have multiple people updating the same record simultaneously and why would you want to do that? How would you determine which field values to save?

Depending on which version of Access you are using, you may or may not have "record level locking" as an option. If you don't have that option set, Jet will lock at the block level which is 2k. So however many records fit in the 2k will be locked if one of them is being updated.

 

[mcbass1]

Thank you for your quick reply. I'm working in Access 2003, but the file format is 2000. In Options, the default record locking is set to No Locks, and the box is checked for Record Level Locking. This is true of both the front and back ends.

Any info would be appreciated - Thanks again!

Mike

 

[Pat Hartman]

I'm pretty sure that Record Level Locking showed up as an option at least one version before it was actually implemented. So, I don't think it is actually available in A2K.

You didn't answer the question regarding why you want multiple people to be able to do simultaneous updates to the same record.

 

[mcbass1]

There will be instances where a dispatcher has a record pulled up, documenting it while talking to one of our contractors, and the customer calls back. I'd like the receiver of that call to be able to update the record - right now it would be locked, although it will tell them it's locked by a certain person. Maybe a hidden form used to prompt the dispatcher that the receiver wants to document on the call?

Any other ideas on the record locking problem?

Thanks,
Mike

 

[Pat Hartman]

If the dispatcher doesn't realy need to update the record, make his form read-only. That won't lock the record of others.

If "documenting" means that people are adding comments, the comments should be in a separate table and should be write only. That means that existing comments cannot be changed. This is a new "feature" in A2007 but you need to manually control this in earlier versions.

I think you are going to need to switch to at least A2003 to get record level locking.

 

[mcbass1]

The dispatcher does need to update the record. I don't have a problem with them (receiver or dispatcher) only being able to update a record when they are the only one with it opened, but the problem with record locking hinders that. When I go in the back way and pull up a record then try to pull up another one, sometimes it will be locked even though no one is in it. It doesn't give the msgbox to tell me it's locked by another user, because it's not (if I try to pull the same record it does give the msgbox). Each form checks a check box on load to see if it's in use, and if it's not, it checks the box. It errors here. It can't check the box, because it's locked for some reason.

The file format is 2002-2003, so I'm still at a loss.

 

[Banana]

Disclaimer: I'm not totally familiar with Jet's locking behavior.

Out of curiosity, whenever your dispatcher has a record open and is editing, how long does any dispatcher have it dirty? I believe that a record stays dirty until following happens: 1) the dispatcher moves off the record, 2) the dispatcher explicitly saves it, 3) the code you wrote in a certain event instruct the form to save the record. There may be more but that's all I can think right off the head.

The reason I'm asking is if it's common for your dispatcher to leave a record dirty for long time, they're basically locking the record for a long time and others will have to wait until that record has been saved before they can view it. If this is indeed the case, then you will have to change that, whether by forcing them to save more frequently or using transactions and isolate the transactions (with understanding that you are risking a potential deadlock) or other choices.

 

[mcbass1]

Hi Banana. I have two fields I added to the table, one is a check box which is checked when someone is in the record, and the other is a field that holds that person's ID#. The check box is checked on Load - if it's not checked, it checks it and puts that persons id# in the other field and the record is saved. If it is checked, it gives a msgbox that says it's locked and by who. On Unload, the box is unchecked and the record saved. This seems to be working fine, but other records that are not in use are showing locked when another record is opened - I don't get that.

Here's the code for the Load/Unload events I'm using:
Private Sub Form_Load()
If ([Call Locked] = -1) Then
MsgBox "This call is currently locked by " & [In Use By] & " and can't be updated.Please try again later."
Text310.Visible = True
Label309.Visible = True
FormHeader.BackColor = 8421504
Else
[Call Locked] = -1
[In Use By] = Forms![Authority]![H#]
inputTxt.SetFocus
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub
Private Sub Form_Unload(Cancel As Integer)
[Call Locked] = 0
DoCmd.RunCommand acCmdSaveRecord
End Sub

I'd appreciate any help you could throw my way - thanks.

Mike

 

[Mike375]

What about having a SaveRecord code/macro action attached to fields where changes are made and running on KeyDown or KeyUp

 

[Banana]

mcbass1,

This really should go in form_current, not form_load event so only one record is locked and that should be the one your users are looking at.

If you're using a datasheet or continuous, then use dirty event to lock the record and afterupdate to unlock the record.

The point here is that you have to keep the locking contention to absolute minimum. They shouldn't be editing a field then leaving it for a good while, which just increase the chance of a locking contention and potentially escalates into a deadlock.

Also, consider using transactions, which Access supports as long you're using one form. Microsoft has a article on this. This is much more better way to handle concurrency than a home-brew solution, not to disparage your solution, which I'm pretty would work, but I'm always in favor of using built-in functionality whether possible and resorting to my own wheel reinvention if those functionality are missing or inadequate.

 

[DCrake]

Simple Software Solutions

Back off holiday now.

Can I put forward a suggestion as an afterthought.

If the receiver and the despatcher a looking at the same record, but different elements of the record then change the two forms to unbound forms. This then forces the user to click on a button to save any changes. The changes made wil only apply to the users form contents that are not shared by both.

Lets say that you have 10 fields that are updatable 1-5 by the receiver and 6-10 by the despatcher. by using unbound forms each user can see all 10 fields but when the receiver clicks the save button only fields 1-5 are updated. Likewise 6-10 are updated for the despatcher.

The only flaw to this is that if more than one receiver or despatcher has the same record open. However your locking flags could control this by introducing another field to signify what type of user has the record open.

Despatcher 1 opens record 100 and the flags are set accordingly..

Locked = True
User = Desptacher 1
UserType = D

Despatcher 2 attempts to open record 100. The records is populated on screen with the current details for record 100 and the form control locked properties are set to True. And a message box tells Despatcher 2 that Despatcher 1 has the record open for editing.

You could also introduce idle time to detect any activity on a form and get it close the form, thus unlocking the record, if no activity is detected within a specified period. This prevents Despatcher 1 bringing up record 100, going for a fag or a brew for 15 mins thus locking the record until there return.

A bit more code involved but alot more manageable.

CodeMaster:

 

[mcbass1]

Banana:
I had actually already moved that code to the form_current event, just had an old version that I shared. The other is still on form_unload, because I want the checkbox to uncheck when they exit, even if they don't alter anything on the record. I guess it may be better to use form_dirty and form_afterupdate?

My big problem still lies on the continuous form - the dispatch panel. I'm still not understanding why multiple records will lock when only one is opened.

DCrake:
Thanks for your reply. There are fields that only dispatchers will be updating and fields that only receivers will update, but there are many fields that I want both of them to be able to update.

 

[Banana]

Did you add the source for the checkbox to the underlying table for the continuous forms? If the checkboxes are unbound, they will not work correctly on continuous form because Access "sees" only one form, not several rows with same controls, so one unbound control will be identical to all other unbound controls in other rows.

 

[mcbass1]

The checkbox is in the table and in the queries but not on any form. There are no unbound fields in the continuous form at all. Here are some screen shots: The five fields in the continuous form run off a query and show two or three fields from each record. When clicked, the single form comes up with that record. When I try to open another record that for some reason is locked, it does come up, but it can't be updated.

Mike

 

[mcbass1]

I have another problem with the dispatch panel form: I'd like multiple users to access this continuous form, and sometimes a few people can go into it at the same time. But, during testing, some users were not allowed to go in. There's no error, it just won't load.

 

[Pat Hartman]

I'm still not understanding why multiple records will lock when only one is opened.

Because a 2k (or 4k) chunk of bytes is what constitutes a Page and unless you are using Jet 4.0, ONLY page level locking is available. A page holds as many records as will fit in 2k (or 4k) bytes.

 

[mcbass1]

Thanks Pat. I have no clue about this - is there an easy way to get and use jet 4.0? Looks like I have a lot of reading to do.

Mike

 

[Pat Hartman]

The version of Jet you use depends on the Version of Access.

 

[mcbass1]

Thanks for your reply Pat and please exuse my ignorance in this area - I'm learning.

I'm running Access 2003 and Jet version 4.00.9511.0

If the database is set up to open using record level locking, why would it be locking at the page level?

Thanks,
Mike