How to edit an existing record and not create a new one when using subforms in Access?

Question

I have an access database that holds medical information. It holds quite a bit of information so i have grouped like fields together into individual tables and linked them using a common primary key. I have created a tab style form with subforms on each tab.

Some of these subforms contain fields from only one table, however there are a few subforms where i have included fields from more than one table.

For example, i have a table that holds blood transfusion data and a table that holds patient characteristics. Some Fields from the blood transfusion table and patient characteristics table are in the same subform, but I also have fields from both these tables in other subforms.

When i try to insert data into the sub-forms with fields from multiple tables i get the following errors..

update or cancelupdate without addnew or edit

and

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

From researching the problem i gather this is because access is trying to create a new record for both tables, but if a record has already been created with that primary key (from inserting data into a previous subform) it won't edit the existing record.

Does anyone know how to get access to edit the existing record in this instance instead of trying to add a new record? I have basic skills in VBA but this is a bit past my level of experience.

Any suggestions would be greatly appreciated.

Answer 1

I used have a whole long response about split forms instead but I was having a horrible time getting it to work. So here is my new and improved answer for using subforms.

Here is a link with sub form info if you want to brush up for your purposes https://support.office.com/en-us/article/Create-a-form-that-contains-a-subform-a-one-to-many-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b

Step 1 Make sure your main form is bound to the right table. For my purposes I used a single combo box on my main form to search with. Make sure all the field parameters on your combo box are correct. This includes making sure the Row Source is correct and that you DO NOT have a control source entered.

Step 2 Don't press enter after making a selection in the combo box. To prevent people from hitting enter I created a dummy button at the bottom that says "Save and Refresh" but all it does it create a message window that pops up with "Save Successful". I find hitting enter creates the first error you keep getting. I'm not sure how to address this in a more sophisticated way yet.

Anything else that comes up I will add later.