Write conflict after Spiltting single table into 2 subforms.

[ramindya]

Hello:

Tools or environment: MS Access 2010 front end and Sql Server backend.The SQL Server tables are linked to the front end.

I am trying to split one single table Visit and into two separate subforms forms - Subform1: Track and Subform2: Schedule.

Both forms have one common field visit number from Table Visit and rest of them are different fields from one single table Visit.

Issue:


After entering a value for Visitnumber in the first subform and entering values for other fields and then when I try to enter values for the second subform it throws a write conflict error pop message.

Can you please let me know how to fix this ?

Thanks !!

-Ram

[orange]

Please show us a picture of your tables and relationships.
Could you also give us a few lines describing

- what your application is about, and
- what exactly you are trying to do( with a simple example).

We only know what you tell us, so please help us help you.

[ramindya]

Purpose of the application: It is health study application to follow up and enroll patients for various health studies who have come various treatments in our hospital.To follow up the patients who have visited the hospital to schedule interview ( visits ) done by a user - Scheduler and also to collect other information such as Last date of contact etc done by Interviewer.

Various forms/Frontend User interface:
MainForm:
The application is a research data windows application.Some of the basic information ( fields ) such as FirstName, Lastname, Phonenumber etc are filled/dumped externally by a Electronic Medical Record application automatically. I run a script to dump this data into a Patient table in SQL Server which is linked/imported to MS Access and all these information is displayed in MainForm.The Patient table has MRN which is the Primary key.
Subform1: Tracking embedded in a tab 1 and table is Visit.
Subform2: Scheduling embedded in a tab 2 and table is Visit.

frmPatient (Mainform to display): MRN, FirstName, LastName
frmSubTracking (Subform to display): VisitNumber, Date_of_LastContact, Current_Status
frmSubScheduling (Subform to display): VisitNumber, Current_Status, New_Status.

BackEnd/database:
There are only two tables in this application ( Patient and Visit )
Patient Table: MRN (PK), FirstName, LastName
Visit Table: ID (PK), VisitNumber, Date_of_LastContact,Current_Status, New_Status.
Visit Table: It has ID field which is the Primary key and identity increment by 1, MRN which is Foreign key and Visit Number which is automatically incremented through front end VBA code.
Patient to visit table is a one to many relationship so the user can create a fill any any number of visits for each patient.

User story:
Users can/should only enter/update information for following fields:
Date_of_LastContact, Current_Status, New_Status rest of the fields information are shown automatically because the data is already existing.

Issue:
When the patient John smith has made two visits.So, both the subforms show visit number 2 and other field information and when the interviewer/scheduler tries to update the data in the same form (ex: frmsubtracking) for the same patient for same visit or tries to enter a new data for another subform ( frmSubScheduling ) for the same patient for same visit. I get the write conflict error.

Question
How to overcome this error so either the same user can enter and update the information for the same patient for same visit number simultaneously or two different users can enter or update the information for the same patient for same visit number simultaneously.

PS:I am flexible with redesigning the table that is I can split the table etc.