Solved Query: Unable to Add New Records to Subform Related to the Main Form Through Another Table (1 Viewer)

[MacroTT]

Hoping I can get some guidance on this. I've tried finding a solution but no luck...

I created a form with a record source of the table "Units" and a subform that should show all the "BAU" "Deliverables" for each unit. The subform's record sources are all three tables: Units, BAUItems, and Deliverables. The subform displays all the information I need it to and I can update existing records but when I try to select a "BAU" item from my combo box for the "Deliverable", I get a message that says, "to make changes to this field, first save the record."

This is less important but I'd appreciate some guidance too on how to limit the list in the combo box to only the BAU items related to the Unit. I don't know if it will help but I included a snip of the relationships for some context.

 

[tvanstiphout]

> The subform's record sources are all three tables
That is almost certainly an error. See if you can bind the subform to a single table.
Example: Northwind Starter Edition template database, frmOrderDetails has a subform sfrmOrderDetails, and it is bound to a query on a single table: OrderDetails.
That won't work you'll say, because of the field ProductID, and I need to display ProductName, so must join with Products table.
Not so say I, under the hood each OrderDetail saves a ProductID, but humans can choose from ProductNames in the cboProductID combobox. That combobox gets its records from the Products table.
A similar story can be written to explain why the query for sfrmOrderDetails does not join with Orders: it does not need to.
I believe it's very likely you have an analogous situation.

 

[Pat Hartman]

I get a message that says, "to make changes to this field, first save the record."

Without seeing the schema and knowing what tables are in the RecordSource query, it is hard to say what the problem might be. While there is no problem joining to multiple "lookup tables", a bound form should never be bound to a query that will return more than a single row for the main record you are trying to update.

If the form is supposed to be updating Deliverables, then neither units, nor items should be included. The mainform should be for BAUItems and the subform for Deliverables. The main form would have a combo or search box that is used to select a Unit_ID.

 

[mike60smart]

Hoping I can get some guidance on this. I've tried finding a solution but no luck...

I created a form with a record source of the table "Units" and a subform that should show all the "BAU" "Deliverables" for each unit. The subform's record sources are all three tables: Units, BAUItems, and Deliverables. The subform displays all the information I need it to and I can update existing records but when I try to select a "BAU" item from my combo box for the "Deliverable", I get a message that says, "to make changes to this field, first save the record."

This is less important but I'd appreciate some guidance too on how to limit the list in the combo box to only the BAU items related to the Unit. I don't know if it will help but I included a snip of the relationships for some context.

Hi
Can you upload a copy of your database?

 

[MacroTT]

Good morning everyone. I will go through the comments and try to implement the suggestions but I'm just attaching an example in the meantime.

 

[mike60smart]

ReplyHi
I modified your Form slightly so that the Main Form was linked correctly to the Subform.
Added a Combobox to allow you to select the Unitname required to View.

I should add your combobox for selecting activities is all wrong.

You need to set up a table that lists all ACTIVITIES.

Then you need to create a Form based on your Units with a Subform listing all Activities Associated
with the Unit.

Then in your tbl_BauItems you need to change the field Bau_Activity to Bau_ActivityID - Long Integer FK.

 

[mike60smart]

Good morning everyone. I will go through the comments and try to implement the suggestions but I'm just attaching an example in the meantime.

Hi
I Modified your database to allow you to select a Specific Unit.

Then in the related Subform displaying activities associated with the Unit, when you select a specific Activity the
related subform displays the related dates.

 

[MacroTT]

ReplyHi
I modified your Form slightly so that the Main Form was linked correctly to the Subform.
Added a Combobox to allow you to select the Unitname required to View.

I should add your combobox for selecting activities is all wrong.

You need to set up a table that lists all ACTIVITIES.

Then you need to create a Form based on your Units with a Subform listing all Activities Associated
with the Unit.

Then in your tbl_BauItems you need to change the field Bau_Activity to Bau_ActivityID - Long Integer FK.

Thank you! This seems to address the problem I was having and I understand better now what tvanstiphout was explaining.

 

[MacroTT]

Thank you everyone for your contributions. Definitely learnt something with each one.

 

[mike60smart]

We were glad to help