calculated fields appearing in table

[jamhome]

Hi

I have read some threads about this topic but I need more detailed instructions. I have a form that has two calculated fields--age and year. The following formula appears in the control source:
=Right(Str(Year(Date())),2) in Year field.
=Int((DateDiff("d",[Birthday],Now())/365)) in Age field.

I have the two fields in my table but the results do not appear. How do I get the results to appear in my table. I know that this is not recommended practice but I need to solve.

[June7]

Access 2010 has a new field type - Calculated. You can do these calcs in the table then these calculated fields are available to the form or report. Otherwise, need VBA code to save the calculation result from form to table.

[jamhome]

I am using 2010 and I do not see that choice calculated in table design--for future reference I would like to know how to do? I need to solve this problem in Access 2007. Can you supply the VBA code?

[June7]

If the fields are included with the RecordSource of the form then simply:
Me!Birthday = Me.tbxAge
or do the calc in the code in place of the textbox reference.

The trick is figuring out what event to put the code in. Could be button Click or form Close.

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

[jamhome]

Hi,
Need more instruction. I put this code in an after update event procedure on the birthday field. I typed in Me!birthday = Me.tbxAge. It did not work. I did read that article. What does tbx stand for?

[June7]

tbx is a prefix I use when naming textboxes. Some people use txt. Other examples are cbx or cbo for combobox, lbx or lbo for listbox, etc. Use what you like or nothing. What you need to use is whatever names you have assigned to the textboxes.

[jamhome]

Hi

Still not sure exactly what I should be doing. I have the field birthday. I have the field age. In the Age field on the control source I have: =Int((DateDiff("d",[Birthday],Now())/365)) . The Birthday field is named birthday, the age field is named age. The event procedure is on the birthday field.
Private Sub Birthday_AfterUpdate()
Me!Birthday = Me.Age
End Sub
In the form, it changes the date in the birthday field to 1900 and gives me an age of 111. I do not get any results showing in the table.

[June7]

Sorry, my goof.
Me!Age = Me.tbxAge
Me![Year] = Me.tbxYear

Year is a reserved word. Should not use reserved words as names. Better would be BirthYear.

[jamhome]

Hi

This is what I have now:
Private Sub Birthday_AfterUpdate()
Me!Age = Me.Age
Me![Year] = Me.Year
End Sub
I get "You can't assign a value to this object"
When I use tbx:
Private Sub Birthday_AfterUpdate()
Me!Age = Me.tbxAge
Me![Year] = Me.tbxYear
End Sub
I get error--method or data member not found.
This error occurs when an event has failed to run because the location of the logic for the event cannot be evaluated. For example, if the OnOpen property of a form is set to =[Field], this error occurs because a macro or event name is expected to run when the event occurs.

[June7]

What are the names of the textboxes that the calculations are in?

Do you intend to update the Age field every year?

You are experiencing reasons saving calculated values is not advised. These two calcs are so simple and easy to accomplish when needed, as opposed to the programming effort and making sure the saved value does not get stale (the Age changes).

[jamhome]

HI

The text box names are Birthday and Age. The age field gets updated when the user enters info into the form which can be a daily entry.
Willing to abandon this if you think it is not worth the effort.

[June7]

Give the controls a name different from the fields in the table. That's why I use tbx prefix.

Even though I know it can be done, I really don't think you should. Sure, the age will be calculated and saved when the birthdate is entered but that data will get stale, would have to update it every year.

[jamhome]

Hi

I renamed the text boxes and it worked in the form with the following:
Private Sub Birthday_Change()
Me!tbxBirthday = Me.tbxAge
End Sub
The event procedure is on change.
How do you get the results to appear in the table?

[June7]

I would use AfterUpdate instead of Change event.

Why are you setting the birthday control to the age value? Change the code to:

Me!Age = Me.tbxAge

This code should save value to table. I know, I tested it.

Oh, did forget to mention that record and any edits to it will be committed to table when the form closes or move to another record.

[jamhome]

Hi

Works great! The user will be happy. Thank you.