Unanswered: Write conflict due to two subforms updating data in same table
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:
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.
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.
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.
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.
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.