Populated Fields NOT Populating Table Fields

Question

Good Morning,

I have a single form created from a single table with a couple of exceptions where we have a combo box and 2 text box's in the form. The text box's are automatically populated from whatever selection is made in the combo box. All of that is working great however the populated text box's are not populating the information to the table fields. Once I am able to get the table fields populated, then I can create some date range queries to get the desired info.

Can anyone help me with this?

Thanks,

Dean

---

DeanGarber

Answer 1

Hi Dean,

You should really only need to populate the field for the combobox because you can always "calculate" the other information from it.

Whenever you need to show those other values, just use a query or do the same thing you're doing now with your form.

Database normalization rules discourage storing calculated values in table fields.

Just my 2 cents...

Answer 2

Thank you for the reply. I have yet to figure out how to write that expression in a query or is that possible? For example in the form the expression is =[GameCombo].[Column](1). That doesn't function in a query so I must be doing something wrong?

---

DeanGarber

Answer 3

Let's not be too hasty in jumping to conclusions.  Your problem could be with the form, or it could be with the table to which it is bound.  My guess would be that the two text boxes in the form are not bound to the columns in the table, but are unbound controls whose ControlSource properties are expressions which, for example reference the combo box's Column property.  This is probably how it should be, for the reasons given by theDBguy, but not necessarily so.

Occasionally it is legitimate to assign a value derived from a combo box or some other source to a bound control, e.g.  in an OrderDetails table a value would be assigned to a control bound to a UnitPrice column in the table by code in a ProductID combo box's AfterUpdate event procedure, the value being obtained from a UnitPrice column in the referenced Products table.  This is valid and necessary, as the UnitPrice values in the Products table will change over time, but the value in the OrderDetails table should remain fixed as that at the time when the order was created.  No redundant duplication of data is incurred as the UnitPrice columns in each of the tables are functionally dependant solely on the key of the table.

More usually, but not invariably, to store a value derived from values in other columns, whether in the same or other tables, would introduce redundancy and the consequent risk of update anomalies, so the derived values should not be stored, but merely returned in unbound controls in a form or report, or in a computed column in a query.

We cannot say which of the two above scenarios is analogous to your situation on the basis of the limited information you have provided.  If you can describe exactly what the two columns in question represent in real life terms, and how their values are dependent on that selected in the combo box, then we'd be in a better position to advise you further.

---

Ken Sheridan, Stafford, England

Answer 4

Yes! Absolutely. 

Your first paragraph is accurate.

My unbound Combo Box has somewhere in the neighborhood of 40 game numbers which can be selected. These game numbers change frequently as one game may be discontinued and a new game added. Each game number  has a corresponding game name which is what is populated in my first "text box" from the second column in my Combo Box. Each game number/game name also has a currency value which is populated in my 2nd text box from the 3rd column in my Combo Box.

The Combo Box is populating the table however the 2 text boxes are not.

---

DeanGarber

Answer 5

The Combo Box is populating the table however the 2 text boxes are not.

Nor should they, for the simple reason that the two other columns should not be in the table at all.  The only column you need in the table is the foreign key which references the primary key of the referenced table from which the combo box's RowSource property returns its values.  The values in the referenced table's two non-key columns can be obtained at any time by joining the tables in a query, which can be the RecordSource of a report for instance.  A form could be based on the same query, or solely on the referencing table, and the values shown in unbound controls which reference the Column property of the combo box bound to the foreign key column.  You appear to be currently doing the latter, quite correctly.

By having the two redundant columns in the table they are transitively dependant on the table's primary key via the foreign key Game Number column.  The table is consequently not normalized to Third Normal Form (3NF) which requires all non-key columns to  be functionally determined solely by the whole of the key, with no transitive dependencies.  Otherwise the table is wide open to the risk of update anomalies as a value could be changed in one table, making it inconsistent with the value in the corresponding column in the relevant row or rows in the other table.

---

Ken Sheridan, Stafford, England

Answer 6

Hi DeanGarber,

Could you share us a simple demo file through OneDrive?

Then, we could work closely with your issue.

In addition, has issue below resolved? If it has, I would suggest you mark the helpful reply as answer which is the way to close a thread here, if not, please feel free to keep following.

#Calculated field on Access Form

https://social.msdn.microsoft.com/Forums/office/en-US/84fb708b-4921-4aca-945e-93c8f3a608a7/calculated-field-on-access-form?forum=accessdev

Regards,

Tony

---

Help each other