Expression in Control Source is preventing record from being saved in table

Question

Hello,

I have this MS Access database I am working on. A subform ("E D L Form") is linked to a table ("E D L"). Both the form and table have 11 fields. Some of the fields require data to be pull from the main form ("M P L Form"). I wrote certain expressions to capture this and placed them in the Control Source field on the Property Sheet. When using the form, all of the expressions work perfectly. The problem is the data in these fields are not saved in the records on the table.

I know that the Control Source for the fields in the form should be the names of the corresponding fields in the table but I have tried placing these expressions in queries and on the table fields themselves with no luck. Can someone tell me where I'm going wrong?

Here are examples of some of my expressions:

Part Number: =[Forms]![M P L Form].[Form]![Part Number]

Parts Lost to Downtime: =(-[Equipment Downtime (MIN)]/60)*([Forms]![M P L Form]![Goal OPLH])*([Forms]![M P L Form]![Headcount Used])

Answer 1

This is a matter of the functional dependency of the values in the columns to which you are trying to assign the computed values.

You firstly need to ask yourself whether the values being computed by the expressions in the ControlSource properties of the controls in the subform are time independently determined by the key of the table to which the subform is bound or not, i.e. if one or more of the values in the referenced row in the parent form's table should subsequently change, should the computed values in the columns in the table to which the subform is bound remain unchanged.  Only if this is the case should the computed values be assigned to columns in the table to which the subform is bound.  If so the assignment should be by code in the subform's module.

For an example of a situation where it is legitimate to assign values in the above way take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file the section on 'entering data via a form/subforms' includes an order form in which is embedded an order details subform.  In this the current unit price of the selected item in the referenced table of items is assigned to the UnitPrice column in the subform's table by code in the AfterUpdate event procedure of the bound ItemID control in the subform.  This is legitimate because prices change over time, but the price current at the time the order was made should remain static with that order.

If, on the other hand, it is not the case that the computed values are time independently determined by the key of the table to which the subform is bound there should not be columns in the subform's table to which the values are assigned.  To have such columns would introduce redundancy, and the table would consequently not be normalized to Third Normal Form (3NF), with the consequent risk of update anomalies.  In this scenario the computation of the values in unbound controls in the subform is correct, and the columns to which you are trying to assign those values should be dropped from the subform's table.

Answer 2

Hi Greta,

May I know if you have checked the replies of MVPs'? May I know if you still need other help? If so, you can post back and we will try our best to help you.

Best Regards,

Sukie

Answer 3

No control source values beginning with "=" will be saved to your table. You would need code or a macro (shudder) to update a value in a text box with the expression. You can also try set the default value of a text box to some value from another form.