Age/Year

[endri81]

Hi.I have in the table 2 fields FatherAge MotherAge.I need in the form two textboxes Year and Age for each of these fields in order to be entered data in case it is a year or a number.For example for Father I can have even 1970 or 40 and both have to be saved in FatherAge as number (40)
How to manage this?

 

[vicissitude]

Make a combo box and then in the 'on focus' event put in this code where
AgeToday is the name of the combo box and DateOfBirth is the text box where date of birth is input. If there is a value in the DAteOfBirth textbox then a calculation of age will appear in the combo box or else you can just manually put in the age yourself.

    'Set combobox rowsource type property to value list
    Me.[AgeToday].RowSourceType = "Value List"
       
    'date of birth must be completed or rowsource set to 'Empty'
    'Else set rowsource to the calculation of the difference between todays
    'date and date of birth dates. 'Fix' function rounds down to nearest integer

    If IsNull(Me.[DateOfBirth]) Then
        Me.[AgeToday].RowSource = Empty
    Else: Me.[AgeToday].RowSource = Fix(DateDiff("d", [DateOfBirth], Date) / 365)
    End If

 

[DCrake]

What's the point of having a combo box when you are only going to supply one item to the rowsource?

 

[vicissitude]

O right he wants it all to happen from one text box, apologies.

 

[endri81]

So how do i adapt in my case the code?
Is important that only the year or age will be entered.
For example it will be entered only 29 or 1981 and not all the birthday format like
01/01/1981

 

[vicissitude]

On the afterupdate event of textbox something like..

If [FathersAge] > 1000 And [FathersAge] < 3000 then
[FathersAge] = DatePart ("yyyy", Date) - FathersAge
Else [FathersAge] = [FathersAge]
End if

*Untested

 

[vicissitude]

The previous code works fine as long as names are correct as per your textbox

 

[DCrake]

Whatever you are doing is not accurate or correct. Tomorrow his age will change you do not store calculations in fields you calculate them in queries.

 

[vicissitude]

O yea! lol


OK what about...

Create a hidden text box on your form called [FatherYearOfBirth]
Create a new field in relevant table with similar name and bind textbox to it.

then when calculating age in combobox

If [FathersAge] > 1000 And [FathersAge] < 3000 then
[FathersYearOfBirth] = [FathersAge]
[FathersAge] = DatePart ("yyyy", Date) - [FathersAge]

Else [FathersAge] = [FathersAge]
[FathersYearOfBirth] = DatePart("yyyy", Date) - [FathersAge]
End if

and then make the control source of the combo box

comboboxName = DatePart("yyyy",Date) - [FathersYearOfBirth]

Or create a new field on the underlying query
FathersAgeCalc: DatePart("yyyy",Date) - [FathersYearOfBirth]

and then make that the control source of the FathersAge combobox

A possibility?

 

[DCrake]

I think the decision has to ome from the OP it is no good we dicussing the issue without their interjection.