Update form fields from a table with a command button

Praedico

New Member
Joined
Jul 17, 2008
Messages
41
I'm working in Access 2010. I have a form with a command button and multiple fields (a combobox with local highways, a textbox for milepost, a textbox for latitude and a textbox for longitude). When the user presses the button, VBA code runs and updates the 2 GPS textboxes (latitude and longitude) from a built in GPS unit.
That part works great, now...

I have a table that has many (~150k) known GPS coordinates from around the region and the corresponding highway and milepost for those coordinates. What I would like is to have access use the GPS coordinates that are displayed in the textboxes to find the nearest location in the table and update the highway and milepost fields. The end goal is to have the user press the one button and get their current GPS coordinates and the nearest highway and milepost.

I'm fairly new to access and I'm lost on how to do this. If it should all be done in VBA, or if I should do it with queries, or a combination of both and what any of that would look like ... ?? help.

Thank you
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
You can approach this in two ways, find the nearest location by selecting the records that have the smallest absolute difference between current lat/lon and lat/lon from the reference table, or calculating the distance between current position and coördinates from the reference table and then selecting the records. The second option enables the posibility to give users a distance to the nearest locations.

However, calculating all differences or distances between current location and the reference table is not a very efficient MO. So you'll need to narrow down the records to compare before starting any calculation.
Narrowing down can be done by shifting the current position with one or two degrees both ways (I don't now the density of the locations, if they are all within a radius of 100km, you'll need to shift by only a tenth of a degree maybe).

So let's say you're locations are all over North America, then the max longitudes are between approx -125 and -60, and the latitudes somewhere between 25 and 50. Suppose the current location is: lat 42 lon -75 (somewhere in the state of New York). Shift lat by 1 degree, resulting in 41 and 43, and shift lon by 1 degree, resulting in -74 and -76.
Now you can query the locations table like:

Code:
Select * from tLocations Where (Latitude Between 41 and 43) AND (Longitude Between -74 and -76)

This result should be a lot less than 150k of records.

From here you'll need a formula to calculate the distance from current location and the result from the query.

I made a function do this, place it on a module and save it.
Now in a new query, using the query with the result of the shifted location, you can use this function.
The only poblem now is that I have to run, so I'll post the function and come back to you when I'm at home.


Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top