By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,825 Members | 1,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,825 IT Pros & Developers. It's quick & easy.

Generic function for control BeforeUpdate

Expert 100+
P: 1,287
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
  1. Private Sub Form_Load()
  2.   Dim ctl As Control
  3.   For Each ctl In Me.Controls
  4.     If Left(ctl.Name, 3) = "Day" Then
  5. '???  ctl.BeforeUpdate = "=CheckDay(""" & ctl.Name & """)"
  6.       ctl.AfterUpdate = "=FixDay(""" & ctl.Name & """)"
  7.       End If
  8.   Next ctl
  9. End Sub
  10.  
  11. Public Function FixDay(Name As String)
  12.   If Me.Controls(Name) = "" Or IsNull(Me.Controls(Name)) Then
  13.     Me.Controls(Name) = 0
  14.   End If
  15. End Function
  16.  
  17. Public Function CheckDay(Name As String, Cancel As Integer)
  18. '???
  19.   Dim hours As Currency
  20.   hours = Me.Controls(Name)
  21.   If (hours < 0) Or (hours > 24) Or (Not (hours * 4 = Int(hours * 4))) Then
  22.     MsgBox "Daily hours should be positive, multiples of .25, and less than 24.", , "Timesheet Error"
  23.     Cancel = True
  24.   End If
  25. 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.
Sep 3 '09 #1

✓ answered 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
  1. Public Function CheckDay(Name As String)
  2.     If Me.Controls(Name) < 0 Then
  3.         MsgBox "must be > 0!"
  4.         DoCmd.CancelEvent
  5.     End If
  6. End Function

Share this Question
Share on Google+
11 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, Chip.

I think it is not possible utilizing expression in BeforeUpdate property.
However it is possible using this method.
Sep 3 '09 #2

Expert 100+
P: 266
Try This... I tweaked it a bit

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function CheckDay(Name As String) as Integer
  3. '???
  4.   Dim hours As Currency
  5.   hours = Me.Controls(Name)
  6.   If (hours < 0) Or (hours > 24) Or (Not (hours * 4 = Int(hours * 4))) Then
  7.     MsgBox "Daily hours should be positive, multiples of .25, and less than 24.", , "Timesheet Error"
  8.     CheckDay= True
  9.   End If
  10. End Sub
  11.  
I haven't tested it, but in theory it could work. =)

Let me know if this works,
-AJ
Sep 3 '09 #3

Expert 100+
P: 1,287
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
Sep 3 '09 #4

NeoPa
Expert Mod 15k+
P: 31,299
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)?
Sep 3 '09 #5

Expert 100+
P: 1,287
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
  1. Public Function CheckDay(Name As String)
  2.     If Me.Controls(Name) < 0 Then
  3.         MsgBox "must be > 0!"
  4.         DoCmd.CancelEvent
  5.     End If
  6. End Function
Sep 3 '09 #6

FishVal
Expert 2.5K+
P: 2,653
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.
Sep 3 '09 #7

FishVal
Expert 2.5K+
P: 2,653
@ChipR
That is nice, really nice.
Thanks for sharing.
Sep 3 '09 #8

Expert 100+
P: 1,287
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.
Sep 3 '09 #9

NeoPa
Expert Mod 15k+
P: 31,299
@FishVal
Absolutely. I'm with Fish on this Chip. Well done.
Sep 3 '09 #10

NeoPa
Expert Mod 15k+
P: 31,299
@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.
Sep 3 '09 #11

Expert 100+
P: 266
Thanks ChipR, I can use this code. =)

-AJ
Sep 3 '09 #12

Post your reply

Sign in to post your reply or Sign up for a free account.