Query-populating form updates individual table attributes, applies to all records-wtf

[OTOTO]

I have form attached to query. When I edit information about records in the form, the table where the information resides updates and the update affects all other records. Rather than assigning a new primary key and creating a new record in the table and assigning that to the record that I'm editing, the attribute for the original key changes, and all other records referencing that same key show the changed attribute in the forms. Perhaps this is an end-of-the day wall I'm hitting. Seems obvious, but I could use some assistance. Thanks.

[ItsMe]

If you have a form that is using a query as its Record Source, it is possible to update data in more than one table at a time. If your query has two tables and the JOIN creates a One to Many relationship, you could possibly update multiple records (on the Many side) in one fell swoop. It has been a while since I designed a form this way.

Consider the query and the JOIN. Then consider which field you are updating from within the form. Is this control bound to the table on the Many side?

[OTOTO]

hi itsme, thanks for the reply
I'm not sure what you mean by "is the control bound to the table on the many side?"

[ItsMe]

The way I understand it is that you are typing data into a control. You are doing this while in Single Form view of a form. You expect the data you are inputting to appear in one record within the tables. Instead, the same data is appearing in multiple rows/records.

Can you isolate a single control on a form where this phenomenon is occurring? What is the name of the field this control is bound to? What is the name of the table that this field resides in? Is this table on the many side of your JOIN?

[OTOTO]

The way I understand it is that you are typing data into a control. You are doing this while in Single Form view of a form. You expect the data you are inputting to appear in one record within the tables. Instead, the same data is appearing in multiple rows/records.

Can you isolate a single control on a form where this phenomenon is occurring? What is the name of the field this control is bound to? What is the name of the table that this field resides in? Is this table on the many side of your JOIN?

HI,

I'm looking at a form which I've created. One of the controls is a text box called TotalRetentionPeriod. All of the form content is coming from a query which was set up automatically by Access when I imported the data and broke it into various tables.

The view of the query relationships looks like this (I don't see the one and many sides as I would expect from this auto-generated query): See Attachment


The Query's relationship properties looks like this for the relationship in question: See Attachment

[OTOTO]

Additionally, I'd like to throw in the image of the relationships which DOES show that the many and one relationships:

[ItsMe]

What happens when you type data into your control on your form, the control that is bound to TotalRetentionPeriod?

[OTOTO]

Hi,

When I type data into the control on the form, it accepts the data as a change to that attribute in the tblTotalRetentionPeriod rather than creating a new primary key ID for the new attribute or assigning a different ID to the record in the event that it is an already-occurring attribute. I figured that this would be built in logic

[OTOTO]

more: I created this form by creating by going to the one side table of tblBusinessFunctionNames and then adding to it the subform which is a form created from the query of all the tables. I thought that in this way, maybe the changes roll up to the 1 table and apply to many but this is the case even in the subform when it is viewed as a standalone form.

[ItsMe]

... When I edit information about records in the form, the table where the information resides updates and the update affects all other records...

Is this what happens when you type data into your control on your form, the control that is bound to TotalRetentionPeriod?

[OTOTO]

yes it is what happens when I type data into the control that is bound to TotalRetentionPeriod

[ItsMe]

It is probably not relevant for this discussion but, your JOINS in your query appear to be LEFT joins. This, by itself, is not a big issue. However, your relationships are defined as tblRetentionCode... being the table with the Many records.

I suggest you use either an INNER join or a RIGHT join. The difference between LEFT and RIGHT is very confusing because it has to do with which table you name first withinin the statement. Fortunately Access can give you a GUI to help you decide if you need RIGHT or LEFT.

From within the query builder in Design View, double click the line that connects two of the tables. Choose the option, Include all Records from tblRetentionCode...

This will give you the correct join to display the Many side appropriately. Another option would be an INNER join. This is created when you select the option to Include records from both sides where the value is equal, or wording close to that. To recap, there are three options. The option you have selected is not appropriate. The INNER join is usually best suited for viewing data, eg reports. The LEFT or RIGHT joins are better for data entry, typically.

Now on to the relationships Window. You can create relationships here that will help to enforce referential integrity. This is done by applying Constraints at the table layer. From within the relationships window, you can double click the line that connects the two tables to edit constraints.

I suggest you fix the JOIN type for your query and then move to the relationships window to edit the Constraints. Understand that all of this may or may not impact how your form updates data in your table. However, understanding these points and using your knowledge to create the correct Constraints and queries will be beneficial when you create forms.

[OTOTO]

You mean this property or the Right-To-Left versus Left-To-Right "Orientation"?

[ItsMe]

I would not be so concerned about the orientation as much as choosing one of the Radio Buttons. If you adjust the table names in the Pull Down Menus the description of the Radio Buttons will change. By simply focusing on the Radio Buttons, you do not need to be concerned whether RIGHT or LEFT is employed.

Include ALL records from the table with Many

[OTOTO]

Thanks ItsMe. I was able to update the radial button relationships so that the query runs as I would expect
Current status:
Trying to get the parent tables and references to update correctly.