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?

Expand|Select|Wrap|Line Numbers

 
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

Expand|Select|Wrap|Line Numbers

  
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!

Expand|Select|Wrap|Line Numbers

 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

Quote:

Originally Posted by 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!

Expand|Select|Wrap|Line Numbers

 Public Function CheckDay(Name As String)

    If Me.Controls(Name) < 0 Then

        MsgBox "must be > 0!"

        DoCmd.CancelEvent

    End If

End Function

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

Quote:

Originally Posted by FishVal

That is nice, really nice.
Thanks for sharing.

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

NeoPa

Quote:

Originally Posted by ChipR

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.

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

Generic function for control BeforeUpdate

re: Generic function for control BeforeUpdate

re: Generic function for control BeforeUpdate

re: Generic function for control BeforeUpdate

re: Generic function for control BeforeUpdate

re: Generic function for control BeforeUpdate

re: Generic function for control BeforeUpdate

re: Generic function for control BeforeUpdate

re: Generic function for control BeforeUpdate

re: Generic function for control BeforeUpdate

re: Generic function for control BeforeUpdate

re: Generic function for control BeforeUpdate

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about