Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 1 / December 2005

Tip: Looking for answers? Try searching our database.

Change event trigger

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Tony - 31 Dec 2005 20:38 GMT
Hi,

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?

Thanks
Tony
(PeteCresswell) - 31 Dec 2005 22:52 GMT
Per Tony:
>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
[quoted text clipped - 4 lines]
>programmatically calculate the Age field based on the Date of Birth
>field that was changed. Is there a way to handle this situation?

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.
Signature

PeteCresswell

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.