I have been struggling with a bit of code. I am looking for assistance please.



Background: I have two database tables (Access 2007). The first table is called grid_box. This table contains a list of polygon points where each set of points are a single polygon. Vertices in this table can vary in size from 5 to 10 vertices. I am using DAO recordsets. All the code is in a single function, but I will break it out into separate routines once it works. example:

Code:
GRID_BOX
AI_NAME LATITUDE LONGITUDE VERTICES
BOX1 36.671365 64.462451 5
BOX1 36.667541 64.462217 5
BOX1 36.667762 64.46879 5 
BOX1 36.671351 64.468481 5
BOX1 36.671365 64.462451 5 
BOX2 36.671192 64.476694 5 
BOX2 36.667608 64.476578 5 
BOX2 36.667325 64.487119 5 
BOX2 36.67132 64.487551 5 
BOX2 36.671192 64.476694 5
The other table is a table of test points (testData) where I test each point in this table against each set of polygons points in GRID_BOX.
example:
Code:
testDATA
LATITUDE LONGITUDE UNIT_TYPE 
36.669231 64.482487 EVERGREEN 
36.66937724 64.46338971 EVERGREEN
36.66928308 64.46364584 EVERGREEN 
36.66933595 64.46380672 EVERGREEN 
36.66924499 64.46402025 EVERGREEN 
36.66921625 64.46420304 EVERGREEN 
36.66941101 64.46299657 EVERGREEN 
36.56090962 64.50384457 HARDWOOD 
36.57915892 64.50316157 HABITAT COVE 
36.56249547 64.49805611 HARDWOOD
Once a point is considered to be inside the polygon, write that point to a new table. (this works correctly for first polygon only).
Example:
Code:
newDATA
LATITUDE LONGITUDE UNIT_TYPE AI_NAME
NEW DATA WRITTEN TO THIS TABLE
The code below is able to iterate through each test point and test against the first set of polygon points. However, where I am having the problem is after I get through all the test points with the BOX1, the function exits. What I want it to do is move to the next set of polygon points starting at BOX2 and restart checking test points from the begining - this is because polygons do overlap. Then continue to BOX3, etc until all points have been tested against all polygons.

The actual data has over 200K test points in testData. The GRID_BOX table has 585 points that make up 96 different polygons that vary in size from 5 points to 10 points.

The code for the routine:

Code:
Option Compare Database
'Array Variables
Dim polygon As Variant
Dim point As Variant
'Counter Variables for FOR..NEXT loops
Dim r As Integer
Dim j As Integer
Sub pointInPolygon()
    'Recordset Variables
    Dim recPoly As DAO.Recordset
    Dim recPoint As DAO.Recordset
    Dim dbs As DAO.Database
 
    'String Variables
    Dim polySQL, pointSQL As String
 
    Set dbs = CurrentDb
 
    DoCmd.SetWarnings False
 
    On Error Resume Next
 
    'Open Polygon Point Recordset
    polySQL = "SELECT AI_NAME, LONGITUDE, LATITUDE, VERTICES FROM GRID_BOX"
    Set recPoly = dbs.OpenRecordset(polySQL)
    recPoly.MoveLast
    recPoly.MoveFirst
 
    'Open Point Recordset
    pointSQL = "SELECT LONGITUDE, LATITUDE, UNIT_TYPE FROM testDATA"
    Set recPoint = dbs.OpenRecordset(pointSQL)
    recPoint.MoveLast
    recPoint.MoveFirst
 
    'Assign Polygon and Point Variables to an Array
    polygon = recPoly.GetRows(recPoly.RecordCount)
    point = recPoint.GetRows(recPoint.RecordCount)
 
    'Set Intial Values of Variables
    inout = -1
    myCounter = 0 'Used for polygon point counter for number of points in each polygon
 
    'Start test point loop
    '(UBound(point, 2) + 1) is the total number of points to test
    For r = 0 To (UBound(point, 2) + 1)
 
        'Start polygon point loop
        For j = 0 To (UBound(polygon, 2) + 1)
 
            'Counter for all points of a polygon.
            'myCounter will count each iteration of the j loop.  Once
            'myCounter iterates through all the vertices of a single polygon,
            'reset myCounter back to 0.  Inout will either be negative or postive.
            'if positive, point is inside the polygon.
            '(polygon(3, j) - 1) is the number of vertices of the polygon being
            'tested.
            If myCounter >= (polygon(3, j) - 1) Then
                myCounter = 0
                GoTo 4
            End If
 
            'Assign Variables to X and Y test points
            xpoint = point(0, r)
            ypoint = point(1, r)
 
            'Assign Variables to X and Y polygon points
            xpoly = polygon(1, j)
            ypoly = polygon(2, j)
            xjpoly = polygon(1, j + 1)
            yjpoly = polygon(2, j + 1)
 
 
            'Test to determine if a test point is between two polygon points.
            'The inside loop will continue to test until all
            'Polygon points for a single polygon have been tested for a single
            'test point.  Once complete, inout will be either positive or
            'negative.  If it is positive, then the point is inside the polygon.
            If (((ypoly <= ypoint) And (ypoint < yjpoly)) Or ((yjpoly <= ypoint) And (ypoint < ypoly))) Then
                If (xpoint < ((xjpoly - xpoly) * (ypoint - ypoly) / (yjpoly - ypoly) + xpoly)) Then
                    inout = -(inout)
                End If
            End If
 
            'Counter for a single polygon.
            myCounter = myCounter + 1
 
    Next j
    If inout = 1 Then
 
        'Test point is inside polygon, write to database.
            mysql = "INSERT INTO newDATA " _
                & "(UNIT_TYPE, LONGITUDE, LATITUDE, AI_NAME) VALUES " _
                & "('" & point(2, r) & "'," & point(0, r) & "," & point(1, r) & ",'" & polygon(0, j) & "') "
            DoCmd.RunSQL mysql
 
            'Change the sign back to original state
            inout = -1
    End If
Next r
MsgBox "I AM DONE"
'close stuff
Set dbs = Nothing
Set recPoly = Nothing
Set recPoint = Nothing
dbs.Close
 
End Sub
I appreciate any assitance with this problem.

Thanks,
Ken