| re: Change event trigger
Per Tony:[color=blue]
>I have two forms A and B, both opened. In form A, I programmatically
>change the Date of Birth field of the current record of form B. I
>noticed that form B automatically displays the new data correctly.
>However, when I tried trapping the After Update, On Dirty, On Change,
>and On Current events of the Date of Birth text box, none of them gets
>triggered. I want to trap this change event so that in form B, I can
>programmatically calculate the Age field based on the Date of Birth
>field that was changed. Is there a way to handle this situation?[/color]
In MS Access, there often seems tb at least a half-dozen "right" ways to do
something.
Personally, I'd favor writing a function to compute a person's age and using
that. The function would be dirt simple, but at least the logic would be
encapsulated in one place.
Error trapping is another story. You're on your own there bc I do stuff with
writing to an error log that would just make the example get lost in the noise.
For calc-ing the age, let's just say:
------------------------------------------
Public Function AgeOfPersonInEvenYears(ByVal theBirthDate As Variant) As Variant
' PURPOSE: To compute the age of somebody in years (no months, no days, just
' years)
' ACCEPTS: The person's birthdate
' RETURNS: The Person's age or Null
'
' NOTES: 1) This thing started life as something tb called from a query that
' a form is based on. Because that situation can result in calls
' to this function whenever fields change on the form and there
' not be a BirthDate present all the time, we want to let things
' slide if no BirthDate is passed.
' 2) The form's built-in editing *should* prevent non-date values from
' getting to us, but we check anyhow in case the func is called from
' someplace else.
Dim myAge As Variant
Dim myDays As Long
Dim myToday As Variant
If Not IsNull(theBirthDate) Then
If Not IsDate(theBirthDate) Then
MsgBox theBirthDate, vbCritical, "Not A Date"
Else
myToday = Date
myDays = DateDiff("d", theBirthDate, myToday)
If myAge < 0 Then
MsgBox theBirthDate, vbCritical, "Date Must Be Before Today"
Else
myAge = DateDiff("yyyy", theBirthDate, myToday)
End If
End If
End If
AgeOfPersonInEvenYears = myAge
End Function
-------------------------------------------
The above function is probably wretched excess and could be pruned to just a few
lines...but the idea is to wrap the calc in a single function.
Once the function is there, I'd base each form on the same query - something
like this:
-------------------------------------------
SELECT
tblPerson.*,
AgeOfPersonInEvenYears([BirthDate]) AS myAge
FROM tblPerson;
-------------------------------------------
Then in both frmA and frmB, we'd have a field named "txtAge" whose
..ControlSource=myAge.
Lastly, in frmA, we'd have:
-------------------------------------------
Private Sub txtBirthDate_AfterUpdate()
On Error Resume Next
Forms!frmB.Requery
End Sub
-------------------------------------------
Again, you're on your own for the error trapping.
The idea of On Error Resume Next is that the Sub won't bomb if frmA is not open.
--
PeteCresswell
| 
