Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How To Move Form Code to a Module? (A2k (9.0.3821) SR-1)

    How To Move Form Code to a Module?

    A2k (9.0.3821) SR-1

    The following code works fine to validate US and Canadian postal codes.

    Now I want to move this code to a module so I can reuse it with other forms.

    I need to know the Module Function syntax and BeforeUpdate event call statement syntax that includes what is needed to capture the cancel variable?

    Thanks, John

    Private Sub strZIP_BeforeUpdate(Cancel As Integer)

    Dim Zip As String

    If Not IsNull(Screen.ActiveControl) Then
    Zip = Screen.ActiveControl
    Select Case Len(Zip)
    Case 5 ' 5-Digit US Zip code
    If Not Zip Like "#####" Then
    MsgBox "'" & Zip & "' is not a valid 5-digit US Zip code."
    Cancel = True
    End If
    Case 6 ' Canadian postal code (without space)
    If Not Like2(Zip, "@#@#@#") Then
    MsgBox "'" & Zip & "' is not a valid Canadian postal code."
    Cancel = True
    End If
    Case 7 ' Canadian postal code (with space)
    If Not Like2(Zip, "@#@ #@#") Then
    MsgBox "'" & Zip & "' is not a valid Canadian postal code."
    Cancel = True
    End If
    Case 9 ' 9-Digit US Zip code (without hyphen)
    If Not Zip Like "#########" Then
    MsgBox "'" & Zip & "' is not a valid 9-digit US Zip code."
    Cancel = True
    End If
    Case 10 ' 9-Digit US Zip code (with hyphen)
    If Not Zip Like "#####-####" Then
    MsgBox "'" & Zip & "' is not a valid 9-digit US Zip code."
    Cancel = True
    End If
    Case Else ' Non-postal code
    MsgBox "'" & Zip & "' is not a valid 5- or 9-digit US Zip code or " & _
    "Canadian postal code."
    Cancel = True
    End Select
    End If
    End Sub

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Move Form Code to a Module? (A2k (9.0.3821) SR-1)

    John,

    I have a problem with the line
    If Not Like2(Zip, "@#@#@#") Then

    Is Like2 another function you build ?
    Francois

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Move Form Code to a Module? (A2k (9.0.3821) SR-1)

    Hi Francois

    yes it is.

    I rewrote the function to return a boolean value.

    Then, in before_update event on form, wrote:

    Cancel = ValidateZip()

    Seems to work.

    Thanks, John

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How To Move Form Code to a Module? (A2k (9.0.3821) SR-1)

    There have been a number of threads dealing with creating "global" functions to handle form events, so you might try a Lounge search for additional discussions.

    The simple answer is that you create a public function in a standard module and you call that function from the Form's event procedure, passing the arguments needed from the event procedure to the function. So if your function needs to refer to a control on a form and examine its values, one of the arguments you would pass is an object variable set to point to that control. If you called the routine from your strZip_BeforeUpdate, you might do something like this:

    Private Sub strZIP_BeforeUpdate(Cancel As Integer)
    Dim ctl As Control

    Set ctl = Screen.ActiveControl

    Cancel = Not ValidateZip(ctl)

    Set ctl = Nothing

    End Sub

    I'm assuming here that ValidateZip is the name of the function, that it accepts a control ByRef as an argument, and that it will return a Boolean value of True if the zip validates and False if not.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How To Move Form Code to a Module? (A2k (9.0.3821) SR-1)

    A slight variation on the solution Charlotte offers:
    Write a public function ValidateZip in a standard module:

    Public Function ValidateZip()
    Dim Zip As String
    Dim Cancel As Boolean
    Zip = Screen.ActiveControl
    ' Perform checks to set Cancel to True or False
    ' ...
    If Cancel = True Then
    DoCmd.CancelEvent
    End If
    End Function

    On your form, don't create a BeforeUpdate event routine in code, but enter
    =ValidateZip()
    in the Before Update event in the Properties window of the control.

    By doing it this way, in some cases you can avoid creating a class module for the form. This decreases the size and increases the performance of you database.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How To Move Form Code to a Module? (A2k (9.0.3821) SR-1)

    <hr>By doing it this way, in some cases you can avoid creating a class module for the form. <hr>
    That's the only reason to use the call to the function that way rather than from within the form module. If there is other code in the module, then there is no reason to call the function that way and it has no percepitible effect on performance. I've never been impressed with so-called "light" forms in Access except in very simple applications.
    Charlotte

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Move Form Code to a Module? (A2k (9.0.3821) SR-1)

    Thanks Charlotte & HansV

    My code works.

    I appreciate the different approaches.

    John

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Move Form Code to a Module? (A2k (9.0.3821) SR-1)

    Hi Charlotte

    As you said the following refers to a control on a form

    Dim ctl As Control
    Set ctl = Screen.ActiveControl

    How do I refer to a label associated to the above control on a form?

    In a public function in a standard module I want to be able to do the equivelant of:

    Me.DEPARTMENT_Label.caption = mstrLabelName

    Thanks, John

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How To Move Form Code to a Module? (A2k (9.0.3821) SR-1)

    I wasn't offended, Hans. Everyone develops his own style and preferences in programming. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I personally don't like "light" forms because I find it harder to control them reliably. I prefer to use forms classes, which require at least the shell of a module to work. I also find it much harder to debug those function calls in the property sheet, and they are, as you pointed out, not at all self-documenting.
    Charlotte

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How To Move Form Code to a Module? (A2k (9.0.3821) SR-1)

    It depends on whether the label is bound to the control. If it isn't, then you would have to pass the label as an additional argument, which you could make optional. If it's a bound label, you can do it like this in your external routine:

    Dim lbl As Label

    If TypeOf ctl.Controls(0) Is Label Then
    Set lbl = ctl.Controls(0)
    lbl.Caption = mstrLabelName
    End If

    Set lbl = Nothing

    This assumes the control is a simple control like a textbox. If the control were something like an option group, you would have to loop through the controls collection of the group to find the one that was a label.
    Charlotte

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How To Move Form Code to a Module? (A2k (9.0.3821) SR-1)

    Charlotte,

    My addition was by no means meant to disparage your solution.

    I agree that there is no size or performance advantage if the form contains other code. However, on occasion I've been able to move all code for a set of similar not-so-simple forms into a standard module, resulting in 100's of KB reduction in size.

    Apart from that, I think it's a matter of personal preference. I like the concept of having a function call in the Properties window above the clutter of having event handlers in code all over the place just calling other code. But others might prefer that because it makes documentating easier.

    Regards,
    Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •