Trigger code when form is open or selected

[Dom606]

I have a tab form (continuous form)that has the following code which checks the assignment end date and compares it to today's date. If the assignment end date is > than today's date the assigned percent field is updated. I can get the code to work, however, I want the code to trigger when tabbed form is selected. I tried a me.requery on the Form Active property but that does not work. Can anyone tell how I can trigger the code when the form is opened or selected? Or am I doing this the wrong way?

Option Compare Database
Option Explicit

Private Sub Assignment_Percent_Utilized_AfterUpdate()
Me.Requery
End Sub

Private Sub Form_Activate()
Me.Requery
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsDate(Me![Assignment Start Date]) Then
  MsgBox "Invalid Start date"
  Me![Assignment Start Date].SetFocus
  Cancel = True
  Exit Sub
  End If
If Not IsNull(Me![Assignment End Date]) Then
  If Not IsDate(Me![Assignment End Date]) Or Me![Assignment End Date] < Me![Assignment Start Date] Then
    MsgBox "Invalid End Date - End Date Must Be Greater Than Start Date"
    Me![Assignment End Date].SetFocus
    Cancel = True
    Exit Sub
    End If
[COLOR=green]If [Assignment End Date] > Date Then
    [Assignment Percent Utilized] = 0
    Else
    [Assignment Percent Utilized] = [Assignment Percent Utilized]
    
    End If[/color]
End If

 

[PHV]

Why requery ? You may use the Current event procedure.

Option Compare Database
Option Explicit
Private Sub Form_Current()
If Me![Assignment End Date] > Date Then
  Me![Assignment Percent Utilized] = 0
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsDate(Me![Assignment Start Date]) Then
  MsgBox "Invalid Start date"
  Me![Assignment Start Date].SetFocus
  Cancel = True
  Exit Sub
End If
If Not IsNull(Me![Assignment End Date]) Then
  If Not IsDate(Me![Assignment End Date]) Or Me![Assignment End Date] < Me![Assignment Start Date] Then
    MsgBox "Invalid End Date - End Date Must Be Greater Than Start Date"
    Me![Assignment End Date].SetFocus
    Cancel = True
    Exit Sub
  End If
  If Me![Assignment End Date] > Date Then
    Me![Assignment Percent Utilized] = 0
  Else
' I don't understand the following line ...
    Me![Assignment Percent Utilized] = Me![Assignment Percent Utilized]
  End If
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JBinQLD]

Mate,

put this bit

'see if new record:
if isdate(me![assignmen end date]) then   'it's not new!

   If [Assignmenment end date] > Date Then
       [Assignment Percent Utilized] = 0
   Else
       [Assignment Percent Utilized] = [Assignment Percent Utilized]
   end if
else
   'new record, do nothing    
End If

into your OnCurrent event....


JB

 

[Dom606]

Thank you PH and JBinQLD. That worked great. You guys are not only very good, you are quick.
Thanks again for the help,
Dom