Subforms Out of Synch

[scking]

I've developed an application that uses a main form with a PersonID as PID with subforms for appointments. It is now installed on a network with 4-5 people updating it at a time. The problem is that when they run the filter by last name they could get 72 persons and they use the navigation buttons to move to the correct person and update their appointment date/time. Occasionally the PID from the main form and the PID (foreign key) on the appointments do not match. Then, when they update the appointment date/time it updates the wrong appointment.

I have been unable to duplicate this problem on my laptop which is significantly faster and with more memory than the computers on the network. They may also break into code with no obvious errors and when I press F5 I get no errors. Has anyone experienced this problem and solved it? My hypothesis is that it is a network performance based. If the system timeout is set at 30 and the network can not keep up with the paging between records it times out and does not update the subform record to the correct subform. Currently we are running this app on a single state of the art laptop to test the hypothesis that it is a network performance issue.
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[dakota81]

I don't have any experience with the problem you are seeing, but I might offer a possible workaround. Instead of using the build-in navigation buttons, try placing navigation buttons into the form using the command button wizard, then modify the code of the command button's click event and at the very end add the line:

SubFormName.requery

That should have the subform set to the correct records before the user can do anything else. If for some reason you still get the same problem occuring, try adding this line afterwards:

DoCmd.RepaintObject

So then after the subform is requeried, this command will redraw the form (and subform I believe) before the user is able to enter or modify any data.

 

[saltecho]

Hi

A couple of things strike me here.

If I had to navigate using the nav buttons through 72 records I think I might have a sense of humour failure pretty quickly. Why not populate a combo or listbox with the choices from the filter?

Secondly, what is the record locking set to? With 'no locks' you will keep getting conflicts, with 'optimistic' or 'edited record' you will still get records not accessible due to the page being locked. In case you don't know, records are stored as data pages, when you access a record for editing all the records on that page are locked whilst the current record is being edited. With 4/5 people editing it is highly likely that there will be conflicts and that Access will time out waiting for access to the record.

So in a way you could be right, it is a network problem but the very act of navigating through dozens of data pages will lock hundreds of records.

Be aware, it is often very easy to blame the network when some thought on design can cure the problem, I've got the T-shirt.

Saltecho