Edit Data in Form (1 Viewer)

[nstratton]

I have a form which I want to use to update data as needed. I keep receiving the notice that the recordset is not updateable. While trying to figure out why I came across this

You can edit bound controls based on a single table or tables with a one-to-one relationship. For controls bound to fields based on tables with a one-to-many relationship, you can't edit data from the join field on the "one" side of the relationship unless cascade update is enabled between the tables.

Now unless I am reading this wrong, this says I can make changes to the data on the "many" side without any issues. And before I get asked I have the cascade update checked and my tables are fine. Thing is I have one form that works and when trying to model the other forms from it, they do not work.

 

[plog]

1 Form, 1 table. When you want to create a form that directly interacts with data (edit/add/delete) that form should be based on a table (not a query).

When you have a 1 to many relationship between tables, you can accomplish that with a form (to edit the 1) and a subform (to edit the many). that's how you should be building forms. Here's a microsoft link for more info:

https://support.office.com/en-us/ar...any-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b

 

[nstratton]

Please excuse my ignorance here. I just can't seem to wrap my head around this concept. I'm going to try to explain what I think this all means.

I have a "junction" table I want to edit/add/delete data from. I set a form with a record source to this "junction" table. I then create subforms for the various parts of that "junction" table. From these subforms I should be able to add/etc...So follow-up question if all that is correct, does it make a difference between single vs continuous in what can be done to the data?

 

[drybone]

Yes, you should be able to update your many records in the subform and your 1 record in the form.

 

[plog]

The many table should be the subform and the main form should be based on the 1 table. Let's use Customers and CarMakes: 1 customer can own many makes of cars and 1 car make can be owned by many customers. Your tables would be such:

CarMakes
Make_ID, Make_Name

Customers
Cust_ID, Cust_Name

CustomerMakes (junction table)
CustMake_ID, Cust_ID, Make_ID

From a form persepective you would have 2 main forms: one based on CarMakes and one based on Customerss. Those forms would allow you to edit/add/delete data from one record in the respective table. You would then have a sub-fiorm based on CustomerMakes which would allow you to add/edit/delete from CustomerMakes. Because it links to both Customers and CarMakes it could be a subform on either of your main forms allowing you to control which Customer owns which Makes.

That's how forms should work for junction tables.

 

[nstratton]

When trying to view the information of the junction table as described above I am getting it to show one record at a time. What I am wanting to happen is to display something like all the CarMakes belonging to 1 customer in a continuous form type of way. I'm not entirely sure how to make this happen. Could there be a problem doing that when putting a subform within a subform?

 

[Minty]

No - but you need to get the child parent bit the correct way around and make sure it's referring to the either the sub form or the main form?

 

[plog]

What I am wanting to happen is to display something like all the CarMakes belonging to 1 customer in a continuous form type of way.

Then your main form should be based on Customers and the subform based on CustomerMakes. The subform should be a continous form and the parent/child relationship between main form and subform should be on the Cust_ID field.

Could there be a problem doing that when putting a subform within a subform?

There shouldn't be a subform on a subform with the layout described.

 

[nstratton]

Maybe it would help if I gave an example of what my project is:

I have tblSuppliers, tblSupplierWorkType, tblWorkTypes. The main form is tblSuppliers and the subform will be tblSupplierWorkType. If I leave tblSupplierWorkType as is I get back the ID from tblWorkTypes. So I made a subform out of tblWorkTypes to place on the subform for tblSupplierWorkType so I can see the name of the actual work type.

When doing this, even when set as a continuous form, the records still pull up one at a time. I'm fairly certain I had the parent/child relationships correct but not 100% so I'm trying again.

Basically, I am trying to ensure all the categories that belong to the supplier are displayed. Group the categories by supplier would be a way to describe what I am looking for

 

[nstratton]

Well I realized I am dull and overlooked something really simple. I'm sure I will have other issues but I am good for now. Thank you everyone for your patience

 

[Minty]

Maybe a picture of your form with some explanatory notes would help ?

 

[plog]

So I made a subform out of tblWorkTypes to place on the subform for tblSupplierWorkType so I can see the name of the actual work type.

Nope. You can do all of this in the subform based on tblSupplierWorkType. You only need 1 subform.

Your subform would have only 1 shown field--a drop down to select the WorkType. Since you are on the Supplier form, the subform should be parent/child linked to the SupplierID so that will allow the correct SupplierId to get into tblSupplierWorkType.

That drop down on the subform will show the WorkType name, but store the WorkTypeID. What you do is make the control source of it a query and bring in 2 fields from tblWorkType--the ID and the description. Be sure to do it in that order, the first field is the one stored. In the properties, change the 'Column Count' to 2 and it will then also show the description.

No subform within a subform needed. Just a properly configured drop down.

 

[nstratton]

The properly configured dropdown is what was tripping me up with this whole thing. So simple a concept just took me forever to get it together

 

[Alvarogue]

So most of the problem was because the relationships?