Not able to make multiple entries (records) in the table

[ramindya]

About the attachment database:

Ideally this is how I expect the application to work..

The user should be able to open the selected studies by selecting the study name in the combo box for which he is eligible
and then enter further information for each studies in tracking and scheduling.

There is a patient table which will have patient information and each patient can be eligible for multiple studies and each study can have multiple patients.So, many to many
relationship. So, tblPatientStudy is the junction table between tblA_MainBaseTable and tblStudy.

Question: I want the user to be able to enter multiple study info in both Tracking and Scheduling.Everytime I enter the information it is just updating the existing information for same
patient that is same MRN rather creating a new record in the tblScheduling and Tbltracking for same patient (MRN).

Thanks again !!TrackSchedule.zip

[June7]

You have two subforms with RecordSources that pull data from the same tables (couple different fields but the same 3 tables). This makes no sense to me. Use one subform.

You have a label with caption ' TRACKING & SCHEDULING'. An ampersand is a special character that defines a shortcut key. The character following the ampersand will be a shortcut with Alt+the keyboard key. If you want the & character to display, must double it: TRACKING && SCHEDULING

[ramindya]

Due to business requirement we need two different subforms for sure..Tracking subform is pointing to tracking table and scheduling subform pointing to scheduling table.Thanks for info on the &.

I thought I will also somemore addtional info regarding this issue...

I changed the value of data entry to yes.Now it creates a unique record for each data entry for each field.A record should be created only once for each studyID for an MRN.

This is how the user should be entering..
User opens the study and then enters all the information for that particular study of that patient (MRN) and saves them as one record/row in the table.If it is already an existing row/record for that study then the patient updates the record.

A patient can have any number of studies but cannot be in the same study more than once.

Also I have linked the tracking table and scheduling table with the TblAMainBase table and study table.I have also created composite indexes MRN and StudyID and setting StudyID as unique so an MRN which is patientID can have only one studyID.

Also, for some reason the studyID is not getting entered into the Tracking and Scheduling table.

Please somebody can help me it is an intensive deadline and I am a beginner.

Thanks for all your help!

[June7]

Sorry, I wasn't clear about which forms I was referencing. The frmMainBase has two subforms. These two subforms have RecordSource queries that are identical in that the queries have the same tables with the same relationships. Having two open forms bound to the same data source can cause issue. In this case, the Eligible subform shows only records that are eligible but doesn't show the IsCompleted field. The Enrolled subform shows only records that are completed. How will you edit the IsEligible and IsCompleted fields for the same record? I assume patient must be eligible for a study before they can complete it. How will you tag a study as completed?

A yes/no value for IsCompleted is not very informative. Wouldn't a date be better? Same for IsEligible, although you are saving a number (1, 0) in a number field instead of yes/no.

The form with the issue you describe is formMainBase?

"Everytime I enter the information it is just updating the existing information for same
patient that is same MRN rather creating a new record in the tblScheduling and Tbltracking for same patient (MRN)."

That is because the form opens to an existing record and you are entering data for a new study into that existing record. Of course it will update the record with the edits. You need to open the subforms to a new record or move to a new record with navigation bar. Or set the forms to only allow new records, not show existing. This can be done by setting the DataEntry property to Yes.