I worked out an example for you.
Created a form with two text boxes, GPSLongitude and GPSLatitude and a commandbutton 'cmd_GetNearestLocations'. On the form I placed a subform, frmNearestLocations (display set as DataSheet), with fields Highway, Milepost and Distance.
The idea is to get the 4 nearest locations with distance to the current position.
In words, this is what happens.
Get the GPS coordinates from the textboxes,
Shift coordinates with 4 degrees (but you can change this of course)
Create a querydef to narrow down the records from the table with locations (to avoid calculating distances to all records in the location table) I named my table tLocations, but I'm sure you're table will have another name, so you should change this.
Use the results from this querydef to calculate distances from current position
Select the 4 records with the closest distance from current position
Create a new SQL statement as recordsource for the subform, to display the results (Higway, milepost and distance)
Note: I had to convert the coördinates to strings in the code, due to system settings. Most likely you won't have this problem, but as long as it works, you can leave it as is.
The functions involved to calculate the distance have to be placed in a module, so create a module and paste the next code:
Code:
Option Compare Database
Option Explicit
Public Function dDistance(ByVal vCurLatitude As Variant, _
ByVal vCurLongitude As Variant, _
ByVal vCompLatitude As Variant, _
ByVal vCompLongitude As Variant) As Double
Dim vRadius As Variant
Dim vLat As Variant
Dim vLon As Variant
Dim a As Variant
Dim c As Variant
Dim d As Variant
vRadius = 6371 'earths radius in km, use 3958.756 for miles
vLat = Deg2Rad(vCompLatitude) - Deg2Rad(vCurLatitude)
vLon = Deg2Rad(vCompLongitude) - Deg2Rad(vCurLongitude)
vCurLatitude = Deg2Rad(vCurLatitude)
vCompLatitude = Deg2Rad(vCompLatitude)
a = Math.Sin(vLat / 2) * Math.Sin(vLat / 2) + Math.Sin(vLon / 2) * Math.Sin(vLon / 2) * Math.Cos(vCurLatitude) * Math.Cos(vCompLatitude)
c = 2 * Atan2(Math.Sqr(a), Math.Sqr(1 - a))
d = vRadius * c
dDistance = d
End Function
Function Deg2Rad(ByVal Deg As Variant) As Variant
Deg2Rad = Deg / 57.2957795130823
End Function
Public Function Atan2(ByVal y As Double, _
ByVal x As Double) As Double
Dim Pi As Variant
Pi = CDec(3.14159265358979)
If x = 0 And y = 0 Then
Atan2 = 0
ElseIf x <> 0 Then
Atan2 = Atn(y / x) - Pi * (x < 0) * (2 * (y < 0) - 1)
Else
Atan2 = Pi / 2 * (2 * (y > 0) - 1)
End If
End Function
Public Function d2s(ByVal v As Variant) As String
d2s = Replace(v, ",", ".")
End Function
The code for the command button on the form is:
Code:
Option Compare Database
Option Explicit
Private Sub cmd_GetNearestLocations_Click()
Dim db As Database
Dim qDef As QueryDef
Const sQDefNarrowDown As String = "qNarrowDownLocations"
Const sQDefNearestLocations As String = "qNearestLocations"
Dim sSQL As String
Const iShiftDegrees As Integer = 4
Dim dLonShiftDown As String
Dim dLonShiftUp As String
Dim dLatShiftDown As String
Dim dLatShiftUp As String
Set db = CurrentDb
'Shift longitude
dLonShiftDown = d2s(Me.GPSLongitude.Value - iShiftDegrees)
dLonShiftUp = d2s(Me.GPSLongitude.Value + iShiftDegrees)
'Shift latitude
dLatShiftDown = d2s(Me.GPSLatitude.Value - iShiftDegrees)
dLatShiftUp = d2s(Me.GPSLatitude + iShiftDegrees)
'Create SQL statement
sSQL = "Select * from tLocations Where (Latitude Between " & dLatShiftDown & " AND " & dLatShiftUp & ") AND (Longitude Between " & dLonShiftDown & " AND " & dLonShiftUp & ")"
'Create a new queryDef, first delete if already exists
For Each qDef In db.QueryDefs
If qDef.Name = sQDefNarrowDown Then
db.QueryDefs.Delete sQDefNarrowDown
Exit For
End If
Next qDef
db.CreateQueryDef sQDefNarrowDown, sSQL
'Use query to create new SQL statement for subform
sSQL = "Select Top 4 HighWay, Milepost, dDistance(" & d2s(Me.GPSLatitude.Value) & "," & d2s(Me.GPSLongitude) & ", Latitude, Longitude) as Distance From " & sQDefNarrowDown & " Order By dDistance(" & d2s(Me.GPSLatitude.Value) & "," & d2s(Me.GPSLongitude) & ", Latitude, Longitude)"
'Use this section once, to create a querydef so you can design the subform with the fields from the query.
'For Each qDef In db.QueryDefs
' If qDef.Name = sQDefNearestLocations Then
' db.QueryDefs.Delete sQDefNearestLocations
' Exit For
' End If
'Next qDef
'
'db.CreateQueryDef sQDefNearestLocations, sSQL
'When the form is created, use this line to set the recordsource for the subform
Me.frmNearestLocations.Form.RecordSource = sSQL
End Sub