Bundling record changes in form using transaction

[Monterey_Manzer]

Hi All,

Okay this is kind of complicated but I will do my best to spell it out.

The situation:
My organization does an annual audit (Annual Report) of customers and, as part of the general Access application, I have constructed a series of forms and tables for users to enter this data. This audit has multiple sections with different types of information.

The tables:
Some data are valid for that customer as part of their general record (any new data overwrites the old, e.g. name change) so those are stored on the table that contains the customer data(PartInfo). Some are valid only for that year's audit and those are stored on the parent table ARPT (e.g. # of transactions this year). The relationship between the those two are (PK PartInfo -> FK ARPT). Then there are sections which may have multiple entries or none (e.g. complaints). I store these in child tables related back to the parent table (PK for ARPT -> FK for child tables). Child tables: ARACC, AROF, AROE, ARFT. Alright, we clear so far?

The forms:
Okay so I have one form where the user enters all the information for the general annual into the PartInfo and ARPT tables. I also included buttons that open separate forms for each of the child tables. The open command includes the WHERE statement "WHERE ARPTID = '" & Me.ARPTID & "';" so that the only records we look at are the ones that pertain to the audit the user is working on. I included a bit of code that first searches to find if there are any records in the child table the specific ARPTID and if there are not, it inserts a new (blank) one into the table so the form has a record source to load. This is just a simple DAO record collecting protocol using the above WHERE statement with an If....Then loop which generates a new record if rst.BOF and rst.EOF are true.

The Problem:
Occasionally when my users open the child forms, enter information, close the child forms, and make further changes to the parent form (ARPT) they receive an error message stating that other users have modified this record and asking whether or not they want to commit these changes. Now each of my coworkers is assigned a specific group of customers to work with so we do not have multiple users entering data on the same customer.

My Question:
It seems to me that I might be able to get around this by using transaction processing. Is there a way to group all the record changes a user makes and commit them when they do something like click the subform button or the close form button? It was my understanding that record changes were committed to the table once a user moves from one record to the next but the errors my users are experiencing seem to suggest that is not the case. Any insight anyone might have into a better method for handling this process would be much appreciated.

Thanks!


Ryan

[June7]

Data does commit to table when navigating records. Somehow the same record is being edited by two different entities (2 users or 2 forms or code, same result).

I don't think transaction process will help with manual data entry.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Missinglinq]

...Occasionally when my users open the child forms, enter information, close the child forms, and make further changes to the parent form (ARPT) they receive an error message stating that other users have modified this record and asking whether or not they want to commit these changes....

...we do not have multiple users entering data on the same customer...

This erroneous error (don't you just love the sound of that?) frequently pops up when you have an Access Database being used in a multi-user environment and either the app is not split into a Front End/Back End configuration, or the app is split, but you the Front End on a shared drive with multiple users accessing it. Is this app split properly, with a copy of the Front End on each user's hard drive?

Linq ;0)>