Saving A Calculated Field In A Table

[HectorTheInspector]

Hi, I have created a very simple database to store all my car magazines. I only have one form and one querie and a couple of tables. I have captured the first (Left) letters of some fields to create an ID. eg [make]![model]![magazinename]![year]![issue] to create the result FEACR12355 that I want to use as the [magazineID]. I feel that I need it saved in a field in the main table and can't get it to work. I know all the reviews saying "don't do it" I don't know any other way to store the first (left) letter/number of a word as a unique ID. I could just simply create a text box in my form and manually type in the first letter of each of these other fields, but it is time consuming and I have this calculation working great in my form and querie. Hope this makes sense. Thanks for reading this question.

[alansidman]

Allen Browne offers up a solution here:

http://allenbrowne.com/casu-14.html

[ItsMe]

Since you already have your calculation displayed in an unbound TextBox, you can pass that value to a bound TextBox that is bound to a field in a table. I would recommend creating another column in your table to store the calculated value. A field in addition to your PK. I would also recomend that you Index your new field in the table's properties.

This would be a similar situation to having an Employee ID or Driver License field in addition to a PK. The PK is used by Access to determine relationships and the Employee ID is something familiar to the User. The User can search for a specific record or relative records using only the Employee ID.

What you need to ask yourself is when do you UPDATE this field. I would suggest doing it when you create a new record. As soon as you have all the criteria needed to do the calculation you can run the code and update the field. Maybe you can disable the close button on your form and create your own close button. You could have your new close button check the form and make sure all the criteria was entered by the user and also check to see if the new field was updated by Acces with the calculation.

Back to your original question of how, you could simply make your unbound textbox bound to a new field in a table. When the calculation is passed to the textbox the table's field gets updated. ANother option would be to pass the value of the unbound tesxtbox to the new bound textbox in the click event of your new close button.

Me.NewFieldInTable.Value = Me.UnboundTextBox.Value

EDIT: I guess I should have types my response a little faster.