Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help Moving Form Sub to Module? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have the following Sub in a Form and it works OK

    I now want to move the following Sub to a Module and call it from this Form and other
    Forms to retrieve my desired totals.

    I need some help with the call statement to send the variables required and how do I
    return multiple totals from my new Module.

    Thanks, John


    <pre>Public Sub CalculateTotals()

    Dim rst As DAO.Recordset
    Dim strSQL As String

    Dim PersonalID As String

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

    Re: Help Moving Form Sub to Module? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Do the forms you want to use it on always contain controls/fields named idsKeyToPersonal, idsKeyToSBFile, FamilyRecordCountLTD, FamilyTotalBenefitLTD and FamilyTotalFeeLTD or must they be passed as arguments?

    The variable strTable doesn't get assigned a value in your code.

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

    Re: Help Moving Form Sub to Module? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Sorry, I chopped too much out.

    This will be the same for all forms:
    idsKeyToPersonal
    idsKeyToSBFile
    strTable = "qrytblSumServetblVouch2_NoVoids"

    This may be different:
    FamilyRecordCountLTD
    FamilyTotalBenefitLTD
    FamilyTotalFeeLTD

    This example is a small sample of a larger project, my objective would make it as variable driven as possible.

    Thanks, John

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

    Re: Help Moving Form Sub to Module? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Try something like this:

    Public Sub CalculateTotals(frm As Form, strFamilyRecordCountLTD As String, _
    strFamilyTotalBenefitLTD As String, strFamilyTotalFeeLTD As String)
    ...
    PersonalID = frm.idsKeyToPersonal
    SBFileID = frm.idsKeyToSBFile
    ...
    frm.Controls(strFamilyRecordCountLTD) = lngFamilyRecordCountLTD
    frm.Controls(strFamilyTotalBenefitLTD) = curFamilyTotalAllowedLTD
    frm.Controls(strFamilyTotalFeeLTD) = curFamilyTotalFeeLTD
    End Sub

    Call like this from a form:

    CalculateTotals Me, "FamilyRecordCountLTD", _
    "FamilyTotalBenefitLTD","FamilyTotalFeeLTD"

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

    Re: Help Moving Form Sub to Module? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hi Hans

    Great technique! This opens up a new world for me to continue to code once and use many.

    I

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

    Re: Help Moving Form Sub to Module? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    1. Yes, you can pass a variable of type Form, and use it in the called procedure/function to refer to controls on the form.
    2. Yes, you can use a variable of type Report exactly the same way.
    3. You could use a global variable, or you could call CalculateTotals2 from CalculateTotals1 and pass the calculated value(s) along with the frm variable.

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

    Re: Help Moving Form Sub to Module? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hans

    Do I understand curTotalBenefitYTDForDeductable would be available in CalculateTotals2 after calling CalculateTotals1 if setup as follows?

    mod_CalculateTotals

    Option Compare Database
    Option Explicit

    Public curTotalBenefitYTDForDeductable As Currency

    Public Sub CalculateTotals1(frm As Form

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

    Re: Help Moving Form Sub to Module? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Yes. I thought you already made extensive use of global variables, witness the function gloGetValue?

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

    Re: Help Moving Form Sub to Module? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    The problem is I stumbled onto gloGetValue long before I ever used a global variable and was interested how it was done in the old days.

    GloSetValue & gloGetValue are extremely easy to use.

    Question, should I use them when ever I need a global variable?

    Are there any limitations/disadvantages using GloSetValue & gloGetValue?

    Thanks, John

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

    Re: Help Moving Form Sub to Module? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    They (gloSetValue and gloGetValue) must be some kind of custom functions; I suppose that you have a library database that contains them or that you copy a module containing them into each database. I don't know what the exact code behind them is, but from their names, I can guess what they do.

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

    Re: Help Moving Form Sub to Module? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    You can find the code of gloSetValue and gloGetValue in <post#=198890>post 198890</post#>
    Francois

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

    Re: Help Moving Form Sub to Module? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Thanks, Francois. I was too lazy to search <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

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

    Re: Help Moving Form Sub to Module? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Francois was kind enough to dig up the code for gloGetValue and gloSetValue. The advantage of using them is that you don't have to declare each global variable you're going to use, you can add one on the fly in your code. If you use this mechanism anyway, I would recommend using it for all your global variables.

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

    Re: Help Moving Form Sub to Module? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hans

    In to following code, I

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

    Re: Help Moving Form Sub to Module? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Try

    SBFileID = frm.Controls("frm_SBF_Dependants").Form!idssbfid

    Make sure that you use the name of the subform as a control on the main form.

Page 1 of 2 12 LastLast

Posting Permissions

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