Generic function for control BeforeUpdate

[ChipR]

I have a generic AfterUpdate function applied to my 14 text boxes in a continuous subform, but I can't figure out the syntax to do BeforeUpdate with Cancel. Is this even possible?

Private Sub Form_Load()
  Dim ctl As Control
  For Each ctl In Me.Controls
    If Left(ctl.Name, 3) = "Day" Then
'???  ctl.BeforeUpdate = "=CheckDay(""" & ctl.Name & """)"
      ctl.AfterUpdate = "=FixDay(""" & ctl.Name & """)"
      End If
  Next ctl
End Sub
 
Public Function FixDay(Name As String)
  If Me.Controls(Name) = "" Or IsNull(Me.Controls(Name)) Then
    Me.Controls(Name) = 0
  End If
End Function
 
Public Function CheckDay(Name As String, Cancel As Integer)
'???
  Dim hours As Currency
  hours = Me.Controls(Name)
  If (hours < 0) Or (hours > 24) Or (Not (hours * 4 = Int(hours * 4))) Then
    MsgBox "Daily hours should be positive, multiples of .25, and less than 24.", , "Timesheet Error"
    Cancel = True
  End If
End Sub

Each day in the pay period has a text box to enter a number of hours.
I know I can copy BeforeUpdate code for each control, but I would be very grateful if someone could give me an example of how to set the .BeforeUpdate to a custom function. I'm sure the capability to set these dynamicaly will be beneficial down the road.

[FishVal]

Hi, Chip.

I think it is not possible utilizing expression in BeforeUpdate property.
However it is possible using this method.

[ajalwaysus]

Try This... I tweaked it a bit

 
Public Function CheckDay(Name As String) as Integer
'???
  Dim hours As Currency
  hours = Me.Controls(Name)
  If (hours < 0) Or (hours > 24) Or (Not (hours * 4 = Int(hours * 4))) Then
    MsgBox "Daily hours should be positive, multiples of .25, and less than 24.", , "Timesheet Error"
    CheckDay= True
  End If
End Sub
 

I haven't tested it, but in theory it could work. =)

Let me know if this works,
-AJ

[ChipR]

AJ, I thought that and tried the exact same code, though I added:
line 3. CheckDay=False
I couldn't get it to Cancel. I can't seem to find any documentation on how Cancel works. I mean, I know how to use it, but not why.

I've decided to go with DonRayner's method in Post #9 of the thread that FIshVal linked, at least until I can sort through the more advanced material there.

Thanks for responding so quickly,
Chip

[NeoPa]

I'm not very experienced with this Chip, but from my reading of the Help System info, no parameters can be passed to User-defined functions.

Have you any experience of such parameters working (It's an interesting concept to be sure)?

[ChipR]

NeoPa - My experience with user-defined functions for events is limited to this attempt. I just decided it was time to learn.

On the positive side, some more poking around VB Help led me to the answer I was looking for originally!

Public Function CheckDay(Name As String)
    If Me.Controls(Name) < 0 Then
        MsgBox "must be > 0!"
        DoCmd.CancelEvent
    End If
End Function

[FishVal]

BTW, Chip, looking at function FixDay() I would expect it will wipe out the control which has fired AfterUpdate event since it returns Null in all cases.
Am I right?
............
............
Well. Looks like it works this way.

[FishVal]

@ChipR
That is nice, really nice.
Thanks for sharing.

[ChipR]

The AfterUpdate event doesn't seems to require a value to be returned, it just calls a function or a macro. FixDay() works as inteded, replacing blanks with zeros, and leaving the value alone otherwise. The BeforeUpdate event also doesn't seems to expect (or do anything with) a returned value, but I'm not sure what other events work this way.

[NeoPa]

@FishVal
Absolutely. I'm with Fish on this Chip. Well done.

[NeoPa]

@ChipR
As all Event Procedures are subroutines rather than functions, and the syntax for specifying a User-Defined procedure is simply "=UDF()", I would be very surprised if these were not all treated as subroutines. There seems nowhere to put a return value even if passed. I'm confident any values, if supplied, are simply dropped in the bit-bucket.

[ajalwaysus]

Thanks ChipR, I can use this code. =)

-AJ