Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A function valid for all forms (Access 2000)

    A function valid for all forms

    I have a function that works for one form(actually a subform), called [Forder details extended].
    I want to rewrite the function so that it may be called with any form,regardless of its name,
    regardless whether the form is a main form or a subform. Is it possible ?

    Here is my function:


    Public Function deletearecord()
    Dim intAnswer As Integer
    intAnswer = MsgBox(" The product will be deleted. Are you sure ? ", vbQuestion + vbYesNo)
    If intAnswer = vbYes Then
    Dim f As Form
    Set f = [Forms]![FOrderinformation]![Forder details extended].[Form]
    Dim office As Control
    Set office = Forms![FOrderinformation]![office]
    Dim strDocName As String
    strDocName = "FOrderinformation"
    If IsOpen(strDocName) = True Then
    Select Case Forms![FOrderinformation]![office]
    Case 1
    f![stock].Value = f![stock].Value + f![cartons].Value
    f![items].Value = f![items].Value + f![Quantity].Value
    Case 2
    f![branch1].Value = f![branch1].Value + f![cartons].Value
    f![items1].Value = f![items1].Value + f![Quantity].Value
    Case 3
    f![branch2].Value = f![branch2].Value + f![cartons].Value
    f![items2].Value = f![items2].Value + f![Quantity].Value
    Case 4
    f![branch3].Value = f![branch3].Value + f![cartons].Value
    f![items3].Value = f![items3].Value + f![Quantity].Value
    end select
    end function

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: A function valid for all forms (Access 2000)

    Yes, you can create a function which is used by multiple forms - we do it occasionally. The technique we typically use is to use a calling parameter to tell us which form called the routine. Then we can do things to the controls on the calling form which in turn lets us modify records, etc. Your code may work (though you haven't closed the IF statment on line four with an ENDIF), but I've never tried to do it that way. Maybe someone who has taken this approach can comment.
    Wendell

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

    Re: A function valid for all forms (Access 2000)

    I often make routines that run against multiple forms external to those forms. In this case, you have some very specific handling of named controls going on, so that limits how "reusable" your routine will be, however.

    I would start out in this case by putting a shell into a standard module (i.e., just the Public Function <procedure name>() and End Function lines) and then start filling in the pieces based on the code you've already written. The first thing to do is give the function an argument that requires a form object, something like:

    Public Function deletearecord(ByRef frm As Form)

    That immediately removes the specific of setting a form variable in your function and allows you to pass in the form you want to apply the function to. In your subform, you would call this along these lines:

    deletearecord Me

    Then make messages generic by changing this:

    "The product will be deleted. Are you sure ?"

    to this:

    "The item will be deleted. Are you sure?"

    <pre>Public Function deletearecord(ByRef frm As Form, _
    ByRef ctl As Control)
    Dim intAnswer As Integer
    Dim office As Control

    intAnswer = MsgBox(" The item will be deleted. Are you sure ? ", _
    vbQuestion + vbYesNo)
    If intAnswer = vbYes Then

    Set office = ctl

    Select Case ctl.Value
    Case 1
    frm ![stock].Value = frm ![stock].Value + frm ![cartons].Value
    frm![items].Value = frm![items].Value + frm![Quantity].Value
    Case 2
    frm![branch1].Value = frm![branch1].Value + frm![cartons].Value
    frm![items1].Value = frm![items1].Value + frm![Quantity].Value
    Case 3
    frm![branch2].Value = frm![branch2].Value + frm![cartons].Value
    frm![items2].Value = frm![items2].Value + frm![Quantity].Value
    Case 4
    frm![branch3].Value = frm![branch3].Value + frm![cartons].Value
    frm![items3].Value = frm![items3].Value + frm![Quantity].Value
    end select
    end function</pre>


    The fact that you are specifically addressing particular controls on the form limits the usefulness of this function, but this will give you the general idea.
    Charlotte

  4. #4
    Lounger
    Join Date
    Apr 2001
    Location
    KY USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A function valid for all forms (Access 2000)

    Typically, if I have to use one of many forms, I'll call screen.activeform, or if I have to use a specific control, I'll use screen.activeform.activecontrol. Word or warning, it makes debugging a pain.

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

    Re: A function valid for all forms (Access 2000)

    It also means that you have to be careful about when and how you call it, since the *right* form had better be the active one and the *right* control must have the focus. By passing the form object itself, you can make the code work on a form even if it's hidden temporarily or doesn't yet have the focus.
    Charlotte

Posting Permissions

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