Form & Subform in Dataentry view selective saving

[accesscoder]

Hello,

I am creating a main form and subform in data-entry view. Main form is about patient's general information (patientId, age, sex etc.) and the subform is about the tests that particular patient is taking. Both forms are related in one to many relationship using the patientID i.e for one patient there can be multiple tests.
My problem is, I want to create both the forms in Data-entry view. So when user wants to enter data, for new patient (whose record is not yet created in the patient table), patient record should be saved in patient table as well as if user entered in data in tests subform, that data should be saved in test table. But in second case, where user wants to enter the tests data for existing patient, I want to save only the data of the test subform and not the data of main form ( which obviously will end up throwing error, as primary key 'patientId' already existing )
Can you please guide me how can I do the selective database update( updating just test table and not the patient table) ?
Please let me know if you need any further details, being newbie to access, I was really unable to solve this problem after spending hours. I highly appreciate your help!
Thanks

[RuralGuy]

Are you suggesting creating record "for new patient (whose record is not yet created in the patient table)" without first creating the "patient's general information" record? I hope not.

[accesscoder]

No, I am not suggesting that.
What I mean by that is, entering patient's general info into the patient table, if that patient's general information is already not present i.e it's a new patient.
Please help me in solving this problem!

[RuralGuy]

If your MainForm is based on the Patient's table (preferably a query of same) and the SubForm is based on a query of the tests given then what you want to happen will happen. When the user moves the fucus to the SubForm, if the MainForm is Dirty then it *will* save the current record. The same thing happens when moving the focus back to the MainForm. If the Current record of the SubForm is Dirty then it *will* save. I would assume you have checked off "Enforce Referential Integrity" in the relationship screen. In any case, once the relationship is defined, Access will not allow you to create a Child record without a Parent record.

[accesscoder]

Thank you, RuralGuy for your help!

When the patient is new (i.e not yet present in the patient table) this approach is working fine i.e it is inserting patient info in patient table as well as inserting test data in test table.
But the problem comes when, user wants to enter the test data for already existing patient. I.e suppose user enters patient info and test info for patientID = 1. This will create a new patient in the patient table. Again after some time, user wants to add some more tests for patient Id =1, info for which is already existing in patient table. AS you said, in this case when user moves the focus to the subform, main form tries to save, but as that patien Id =1 is already present in the patient table, it will throw error(primary key violation). To avoid this saving of patient info for already existing patient, what I am doing is, in the before update event of the main form I am checking if the patient id is already existing, if it is then I am setting Cancel = true . I was thinking that this should avoid the saving of already existing patient info. But what is happening is, as soon as focus is lost from main form, and user clicks on control in subform, before update event of main form is fired. So user is not getting chance to enter anything in the subform's controls because everytime before update of main form is called continuosly.

I hope you will understand this. Please let me know, if it is not clear.

[RuralGuy]

You should use an unbound ComboBox with wizard Option #3 and LimitToList set to YES to select the Patient. Then in the NotInList event open another form to completely define a new patient. Your MainForm will then *always* be on a completely identified Patient. I would not use the PatientID as your PrimaryKey but instead use an AutoNumber for this function.

[accesscoder]

Thank you, RuralGuy! It is really wonderful solution. Not worked on it yet, but will try to implememnt it now and let you know if any problem occurs.

I have one more question, in access, I am noticing that, as soon as focus is lost on a given form, that form's data is saved. However I am providing the 'save' button on the form and I want all the data to be saved explicitly when the user click the save button and not the implicit save mechanism provided by access in the form. How can I achieve this?

Thank you!

[RuralGuy]

I would set a Public flag in your form with the "Save" button and enforce it in the BeforeUpdate event of the form. I personally do not use "Save" buttons.

[accesscoder]

Thank you, ruralguy ! Thats great suggestion!

[RuralGuy]

I have yet to find a valid reason to use unbound forms! Resist any suggestions to do so is my strong recommendation. It is often given as "one" solution to what you described. I wanted others who read this thread to understand what I just said. Post back here if you need further assistance with this procedure *and* you are welcome!