Records are being added to base tables when subform records are edited

[smbrush]

I'm using Access 2016. I've created an application with a form and subform. The main form has a control which filters the records showing in the subform. The record source of the subform is a query consisting of several tables between which are left and right joins. Because of this, the Recordset Type property of the subform has been set to Dynaset (Inconsistent Updates) to allow users to edit data in the subform.

For some reason, a new record is added to the base table when the field bound to a column in the query derived from that base table is edited. The field in the subform has been set to combo boxes instead of text boxes to control data entry. For instance, if I change a value in the Topic field of a subform record, a new record is added to the Docs table with the value entered in the Topic field but blank for every other field.

I'd appreciate it if someone could tell me why this is happening and how I can prevent it.

[June7]

Why are you including multiple tables in the RecordSource? I've never used that dataset type and never encountered this issue.

If you want to provide db for analysis, follow instructions at bottom of my post.

[smbrush]

You've misunderstood. I'm using a query for the record source of the subform. The query was created in the query builder tool in Access. Several tables were added to the builder to create the query. It's called frmLegacyLinks. Here is the SQL of this query:

SELECT LQSData.LQdocNumber AS [Legacy Chapter], LQSData.LQreqNumber AS [Legacy Req Num], LQSData.LQmarketReqText AS [Legacy Market Requirement], QSDocs.QSDocNumber AS [QS Topic], QSReqs.QSreqNumber AS [QS Req Num], QSReqs.QSmarketReqText AS [QS Market Supply Requirement]
FROM (LQSData LEFT JOIN [LQS-QSReqsLink] ON LQSData.LQId = [LQS-QSReqsLink].LQId) LEFT JOIN (QSDocs RIGHT JOIN QSReqs ON QSDocs.QSDocId = QSReqs.QSDocID) ON [LQS-QSReqsLink].QSReqId = QSReqs.QSReqId
ORDER BY LQSData.LQDocSectionNum1, LQSData.LQDocSectionNum2, LQSData.LQSubSection1, LQSData.LQSubSection2, QSDocs.QSDocNumber, QSReqs.QSReqSubSection1, QSReqs.QSReqSubSection2, QSReqs.QSReqSubSection3;

The subform is populated with rows generated by this query. When I edit a field in the subform, a new record is added to the source table with only one field having the value that was entered in the subform. Is there a way to prevent the addition of this new record to the base table?

[June7]

I do understand and still ask: why include multiple tables in RecordSource query?

[smbrush]

I'm working with a normalized database. That's the point of using SQL. My application requires the display of information from several different tables. The user can then make changes, additions, and deletions of the data and code will handle how these actions change the base tables. I understand that this deviates from the usual usage of subforms but this is what I have to do. Of course, I can create a single table from the query and use that for my record source but I'm hoping not to have to take that step. Such a table would need to be deleted and recreated with every change the user makes in order to refresh the subform. Is this your suggestion?

[orange]

Hmmm?
You might want to describe your application in simple terms so readers get a clear understanding of your requirements, and some rationale for your proposed approach. June has over 46,000 posts so I don't think she misunderstands your post.

Comments such as:

My application requires

is a requirement--WHAT the application must deliver. It does not say --you must use a recordsource involving multiple tables with multiple joins.

but this is what I have to do

--when it comes to HOW things could/should be done, that is a design consideration selected from 1 or more feasible implementations.

I also do not see any compelling need for the complex recordsource. Can it be done?--probably. Are there alternatives? Most definitely, but feasibility requires the designer to know the details of the issue that generated the need for a solution.

Good luck with your project.

[Minty]

To build a little on the questions, I assume the additional tables are to display related descriptive text based on a stored FK ?
If so you can simply use a combo box and not have to drag in the additional tables to your query.

The additional tables are almost certainly the cause of your issue.

You can normally prove this by simply opening the datasheet view of the source query and trying to make and edit.
See what happens to the data in its raw state. If you don't get additional records (and remember to refresh the query after making your changes) then the records are being added by some code else where on your form.

[June7]

Normal approach is 1 form does entry/edit for 1 table and 'lookup' tables can be included to display related info but those data should not be edited on the same form.

Now having said that, I do have one db where multiple tables are edited on one form. There is a parent table joined to several dependent tables. Parent has 1-to-1 relationship with each dependent table. New records are created in another process and then this form is opened for user to complete data entry.

So if your form is including lookup tables or dependent tables, then adding new records via form will cause issues. Likely you need to use form/subform arrangement for parent/child 1-to-many relationship. If the additional tables are lookup tables, definitely have to edit those tables via separate form. Editing lookup table affects what is displayed for ALL records.

[smbrush]

I shouldn't have to justify my mode of working in a forum post. I'm just looking for an answer to my question. Since no one is capable of understanding what I'm talking about, I'll just close this thread.

[orange]

No one is asking you to justify your mode of working. We are all asking for WHAT you are trying to accomplish so we can understand your question in context.

Since no one is capable of understanding what I'm talking about, I'll just close this thread.

That's quite a jump in interpretation. But you are certainly welcome to make any decision you choose.

Good luck with your project.

[Minty]

My simple suggestion to try the updates / edits directly in the source query would have probably shown the source of the problem.

Guess we'll never know as we're incapable of understanding ?

[June7]

If you want to allow "Inconsistent Updates" then I suppose that is what you will get.

I like explanation in https://www.reddit.com/r/MSAccess/co...stent_updates/