Data entered in form to record source

[JLS04]

I have a form where users enter data in various ways - combo boxes with drop down selections and text boxes where users manually enter some data. I also have several text boxes that return "answers" based on calculations from user entries mentioned above. The form is bound to a table where all the entered data is stored. I'm having trouble saving the calculated information to the bound table. It's easy in all the non-calced boxes, I just put the column name of the bound table in the control source, but in the calculated fields, I already have the formula in the control source. How can I identify the proper column to save the data in this case.

Thanks!

 

[ghudson]

You are advised not to store calculated data in your table. Calculated data is not constant. You question goes against one of the many rules to normalize your data tables. You should only display the calculations when needed like in a form or report.

 

[JLS04]

Hey ghudson - I see what you're saying, that makes sense. However, one of the "calculations" is simply to add a summary notation based on another entry. For instance, the user identifies a line of business as golf balls, I want to add a summary to the table that notes this entry as golf. I have a table where all lines of business are tagged with a summary, (golf balls, clubs, golf bag -> golf; hooks, fishing poles, lures -> fishing, etc.). This information shouldn't change, so the risks of the data being constant shouldn't be to great. What do you think?

Thanks again,
John

 

[ghudson]

If the data will "never" change then you should be okay. You will have to concatenate your data fields into the "one" field where you want to store the values. You will have to use a memo field if the length of the text string will [could] exceed 255 characters.

 

[JLS04]

If the information does change, I'll definitely have some issues to work out! Is there a way to tag the control source line with the name of the column in the bound table and the formula? I thought I could do something like this -> LineOfBusiness: =DLookUp("[LineOfBus]","[LineDetail]","[Item] = '" & [Combo2] & "'") - where "LineOfBusiness" is the name of the column in the bound table and the formula says return "LineofBus" in table "LineDetail" based on "Item" and Item is identified by the user in "Combo2." The formula itself works and returns the data I'm looking for. The problem is, when I try to add a reference the bound table and column, the formula no longer works. Can you help?

 

[JLS04]

Can anybody help me link a column in the bound table with a text box in my form? The problem I'm encountering is, I use the control source to define the formula, so I can't include the column name of the bound table. Help!!!