Results 1 to 9 of 9
  1. #1
    K Roger is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Sep 2011
    Posts
    27

    How to convert/run Code within a Module that now runs as event under a button on a form

    I have a routine (determining the distance between two circles) which runs fine as an event under a button on a form.


    However, I have to click the button for every record. Rather labor intensive since I have over 30,000 records

    I would like to be able to run this code against the whole file at once. However, the formula is too complex to run as a query (at least with my ability).
    I would like to (I think) create a module/macro so that this code (a subroutine?) can be run outside of a form and not one record at a time.

    The code essentially uses 2 sets of latitude/longitude coordinates of two points as fractions. (lat...x, and long...x).

    The formula below produces the distance between 2 circles in feet.

    x = (Sin(lat1x / 57.2958) * Sin(lat2x / 57.2958)) + (Cos(lat1x / 57.2958) * Cos(lat2x / 57.2958) * Cos(long2x / 57.2958 - long1x / 57.2958))

    Me![Distance] = 3963.001 * Atn(Sqr(1 - x ^ 2) / x)


    Any help in how to write a module to access this code would be greatly appreciated.

    Thank you in advance and have a safe day.

    K Roger

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Are looking to to step through each record and apply the result of the calculation to a field called "Distance"
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe you would make a copy of your dB, deleting all records except 50 to 100 records for testing/calculating?

    Do a Compact & Repair, Zip/compress the dB and Post it?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'd consider basing a query on the table/query where these records are. Then have a calculated field that calls a public function in a standard module if you can do the math on the query fields (i.e. the query fields are providing the data inputs). If you apply criteria to this query, you can have it run only for the filtered fields rather than all the records every time if that makes sense, or else you do it to 30K records at once. The function needs to deal with the potential for null values so that you don't raise issues akin to 'division by zero' and such. Afterwards, view the results in a form.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    As Micron suggests:

    Put this function in a standard module:


    Code:
    Public Function fcnDistance(La1 as double,La2 as double, Lo1 as double, Lo2 as double) as double
        dim x as double
        x = (Sin(La1 / 57.2958) * Sin(La2 / 57.2958)) + (Cos(La1 / 57.2958) * Cos(La2 / 57.2958) * Cos(Lo2 / 57.2958 - Lo1 / 57.2958))
        fcnDistance = 3963.001 * Atn(Sqr(1 - x ^ 2) / x)
    End Function



    To use, add this to the form's recordsource query (or any query with the pertinent data fields):


    Select ..., ..., ..., fcnDistance(lat1x,lat2x,long1x,long2x) as Distance, ...,...


    Every row in the query will have the Distance calculated.

  6. #6
    K Roger is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Sep 2011
    Posts
    27
    Thank you one and all for your comments/suggestions.

    Using the code as suggested above by Davegri (modified only by adding the field "Distance" to convert the results of "x" to mileage).

    The query I built was:

    SELECT tblDistanceFunctionTest.lat1x AS la1, tblDistanceFunctionTest.long1x AS lo1, tblDistanceFunctionTest.lat2x AS la2, tblDistanceFunctionTest.long2x AS lo2, fcnDistance([«La1»],[«La2»],[«Lo1»],[«Lo2»]) AS distance
    FROM tblDistanceFunctionTest;

    This query compiles but when I execute it ACCESS prompts me for the variables la1, la2,lo1, lo2.
    By entering blanks, the query errors out.

    If I enter real data, the query completes successfully, but all the distances are the same for each record (i.e. using the data I entered in the prompts).
    Furthermore, none of the Distance computations are filled into the table, but only displayed as query results.
    BTW, the computations for the 1st record are correct, so there is not a problem with the formula.

    Somehow, the program uses the data that I enter when prompted is used in all calculations and not the data for each record.

    Somehow, data is not being passed to/through/out of the function correctly and added to the database field Distance.

    To use an assembly line metaphor: I have all the parts but they are not quite put together in the right order.

    Thank you for your ongoing support of my problem.

    KRoger

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're supposed to pass the domain (table or query) field names as function parameters. If you alias lat2x as lat2 (which I would not do) then where do the arrows << >> come from? I'd say those have become parameters that you will get prompted for, and yes, will be used for every record. I'd use only the domain field names that hold the values and forget the aliasing
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    K Roger is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Sep 2011
    Posts
    27
    Not a problem with removing the alias's in the query, by changing the table field names. Because of the aliasing of the real field names, the BUILD function put the << >> in as it apparently treats an alias as unknown field.

    I just changed the field names to the real ones and deleted the << >> in the function field of the query.

    ALL IS NOW WORKING AS DESIRED.

    You made my day!
    Thank you one and all!

    KRoger

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad to know that something is going right for somebody at least!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-22-2018, 06:03 AM
  2. Code that runs one time only
    By shod90 in forum Forms
    Replies: 1
    Last Post: 01-03-2017, 05:09 AM
  3. Query runs before On Open Event Procedure
    By shuddle in forum Access
    Replies: 3
    Last Post: 12-21-2016, 01:36 PM
  4. How to cause a form event to fire from a general module
    By GraeagleBill in forum Programming
    Replies: 10
    Last Post: 09-04-2016, 08:08 AM
  5. Running a module from a button in a form
    By sardamil in forum Modules
    Replies: 3
    Last Post: 05-01-2012, 10:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums