Requery on what event?

[Navyguy]

Tonight seems to be the night for questions...

I have been reading both from posts and from my reference manuals regarding the requey method.

I have a main form FrmRegistration with a text box TxtDOB.
I have a subform SFrmAgeCalc with has TxtCurrentAge, TxtDOB and TxtFutureAge.

The subform is based on a query QryAgeCalc.

I have tried [Forms]![FrmRegistration].[TxtDOB].Requery on the OnCurrent Event of the SFrmAgeCalc and a few other options.

I have also tried [Forms]![FrmRegistration]![SFrmAgeCalc].[TxtDOB].Requery with no luck.

The query, QryAgeCalc completes a query based on the data from TxtDOB and a age module called BasDateCalculations.

Can somebody point me to a few posts or offer suggestions on where I am going wrong.

Thanks

 

[Rich]

Why do you have DOB in two tables?

 

[Brianwarnock]

Does this thread help?
http://www.access-programmers.co.uk/forums/showthread.php?t=70496&goto=newpost

As you will see WayneRyan is the main man, I just asked the questions

Brian

 

[Navyguy]

Rich

I don't have DOB in two tables, but it does show on the main form and the subform. I figured that once I got the AgeCalc figured out I would just hide the second instance. With it visible it tells me that the requery is not working properly.

Your question does make me think now about the possibility of putting the AgeCalc on the main form. Not sure how I would do that though as the main for is from a table and the the AgeCalc subform is from a query.

Thanks for making me rethink what I was going to do.

Brianwarnock

I will take a look at your suggestion and see if I can make sense of it.

Thanks for your help

 

[Navyguy]

So I reviewed the thought that Rich gave me about putting the Txt boxes on the same form. The calculations seem to work OK, but now my question is how do you store this data. Now before I get jumped on, I know that you are not supposed to store “calculated data” but there are some statistical purposes that this needs to be stored and then recalled at a later date. I am not sure how to get around the normalization rules for this, but I guess that is a separate question.

Normally I would put the field from the table or query in the Control Source of the Txt????. Now I have the formula =Age([TxtDOB]) in that position.

So my example would be:

On FrmRegistration which has a Record Source of TblRegistration.
On the FrmRegistration I have a box for current age called TxtCurrentAge.
The Control Source for TxtCurrentAge was a field from the TblRegistration called CurrentAge but now it is changed to =Age([TxtDOB]). How do I save the result of that to the field CurrentAge of TblRegistration.

Thanks

 

[Brianwarnock]

I'm puzzled, why would anybody need to store both the age and the DOB?
Age is meaningless unless accompanied by an 'on a givendate/situation' explanation.

Brian

 

[Mile-O]

I'm puzzled, why would anybody need to store both the age and the DOB?

They shouldn't.

If age is wanted to be calculated from a specific date.

i.e. What age was I when I started my first job?

I'd use the DOB and the date I started my first job with this fnction below:

Public Function CalculateAge(dteDOB As Date, Optional SpecDate As Variant) As Integer
    Dim dteBase As Date, intCurrent As Date, intEstAge As Integer
    If IsMissing(SpecDate) Then
        dteBase = Date
    Else
        dteBase = SpecDate
    End If
    intEstAge = DateDiff("yyyy", dteDOB, dteBase)
    intCurrent = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
    CalculateAge = intEstAge + (dteBase < intCurrent)
End Function

If I want my current age:

=CalculateAge(#20/02/1979#)

If I want the age I started my first job, for example:

=CalcualteAge(#20/02/1979#, #14/08/1995#)

 

[Navyguy]

So I see then. You still don't store the calculated age but use the date that you need to be referred to for future in the function.

So if I wanted to know how old you were when people registered, I would just use the registration date in that formula and have it show in the report at some point ion the future.

Man I have lots to learn……

Thanks