Moving through the recordset 1

[BusMgr]

I have a table that I have added two new fields to and I am trying to update the fields with the results of a function that I call LatLongCalc.

The table structure for tblHeader1 is as follows

s_GUID Autonumber
Contractor Text
Lease Text
Latitude Double
Longitude Double
County Text
State Text

I have created a form with the above fields on it and a cmdButton to run the update. I pass the function the county and state which have text values and xlat and xlong which are null in the table initially, but returned from the function. My code is as follows:

Private Sub cmdMapCounty_Click()
Dim dbs As Database
Dim rst As Recordset
Dim tdf As TableDef
Dim xlat As Double, xlong As Double

'Open the MS Access database
Set dbs = CurrentDb

'Create a TableDef object.
Set tdf = dbs.TableDefs("tblHeader1&quot

'Create the Recordset object
Set rst = dbs.OpenRecordset("tblHeader1", dbOpenTable)
With rst
Do Until rst.EOF
Call LatLongCalc(County, State, xlat, xlong)
With rst
.Edit
![Latitude] = xlat
![Longitude] = xlong
.Update
End With
.MoveNext
Loop
End With
Set rst = Nothing
End Sub

My problem is that the function will run, but I am not always sure where the update will go, and then it will not advance to the next record.

Thanks for your help.

BusMgr

 

[jhall156]

I'm a bit confused about how you are getting the xlat and xlong values that you are referencing when you update your recordset. Normally, a function returns a value, just one value. Is yours maybe a sub that is setting global variables or something of that nature? A call to a function is easy to spot because it almost alwas looks like this:
myreturnedvalue = myfunction(myparams)

Would you provide the function code? JHall

 

[BusMgr]

Sorry, it is a procedure, because I am not just returning a single value.

Some of the code has been commented out as I was using it on a different form to invoke MapPoint and determine a location for a different purpose.

Private Sub LatLongCalc(County As String, State As String, zlat As Double, zlong As Double)
'Purpose: To map location based on county and state
'Declarations

Dim oApp As Object, oMap As Object
Dim oPush As MapPoint.Pushpin, oPushA As MapPoint.Pushpin
Dim oLoc As MapPoint.Location, oLocA As MapPoint.Location
'A Mile = 0.01471 Degrees of Lat/Long, a Half mile = 0.007355
Dim Measure As Double
Dim DistX As Double, DistY As Double, DistZ As Double
Dim PointX As MapPoint.Location, PointY As MapPoint.Location
Dim rad As Integer, alt As Integer, I As Integer
Dim strSQL As String

'Assignments

Set oApp = CreateObject("Mappoint.Application&quot
Set oMap = oApp.ActiveMap

If State = "LA" Then
Set oLocA = oMap.Find(County & " Parish, " & State)
Else
Set oLocA = oMap.Find(County & " County, " & State)
End If
If Not oLocA Is Nothing Then
Set oPushA = oMap.AddPushpin(oLocA)
oPushA.GoTo

'myLat and myLong are a known point. In this case, Wichita, Kansas
myLat = 37.70212
myLong = -97.31775

'Initially, set zLat and zLong equal to the known point
zlat = myLat
zlong = myLong

'Measure is used to adjust the starting distance. In this case, start off with 750 miles.
'Measure is made smaller in the Do While loop below but it is a good idea to start off with a large number
Measure = 0.01471 * 750

'Create two points: oLoc as the reference point and your address for which you need lat/long
Set oLoc = oMap.GetLocation(myLat, myLong)
'Find your address from the form frmLatLong
On Error GoTo endnow 'If there is no address found, then Set oLocA will fail.
Set oLocA = oPushA.Location
On Error Resume Next

X = 0
'Create a loop that will continue until your desired precision. As indicated below this loop will repeat until the lat/long is found to be within 20 feet.
Do While Measure > 0.00005572 'This value is determined using the following formula:
' .01471/5280*Number of Feet for Precision
' Ex: .01471 / 5290 * 20 = .00005572
' Trivia: There are 5,280 feet in a mile.
X = X + 1

'Create two other reference points: PointX is one Measure off oLoc's Latitude. PointY is one Measure off oLoc's Longitude
Set PointX = oMap.GetLocation(zlat + Measure, zlong)
Set PointY = oMap.GetLocation(zlat, zlong + Measure)

'Measure the distances from each of the three reference points to our main address (oLocA)
DistX = oLocA.DistanceTo(PointX)
DistY = oLocA.DistanceTo(PointY)
DistZ = oLocA.DistanceTo(oLoc)

'Determine which reference point is closer to oLocA, our main address
If DistX < DistY And DistX < DistZ Then
'Make the master reference point oLoc equal to PointX since PointX was the closest our main address
Set oLoc = oMap.GetLocation(zlat + Measure, zlong)
zlat = zlat + Measure 'Don't forget to add the Measure to zLat for the next iteration
'Uncomment the next line if you want to see how the algorithm found the lat/long
'Set oPush = oMap.AddPushpin(oMap.GetLocation(zLat + Measure, zLong), x)
End If
If DistY < DistX And DistY < DistZ Then
Set oLoc = oMap.GetLocation(zlat, zlong + Measure)
zlong = zlong + Measure
'Set oPush = oMap.AddPushpin(oMap.GetLocation(zLat, zLong + Measure), x)
End If
If DistZ < DistX And DistZ < DistY Then
'The main reference point is closer than PointX or PointY.
'PointX and PointY were too far away by ADDING a Measure, so here we need
'to subtract a measure from both the Latitude and the Longitude
Set oLoc = oMap.GetLocation(zlat - Measure, zlong - Measure)
zlat = zlat - Measure
zlong = zlong - Measure
'Set oPush = oMap.AddPushpin(oMap.GetLocation(zLat - Measure, zLong - Measure), x)
End If

'Here is where Measure gets adjusted. Check to see if the distance between the new
'reference point is smaller than Measure. If so, reduce Measure by half.
'Don't forget that Measure is in degrees of Lat/Long while the distance will be
'in miles. To convert degrees to miles, multiply by .01471, the number of degrees
'in a mile.
If oLocA.DistanceTo(oLoc) < Measure / 0.01471 Then Measure = Measure / 2
Loop

'Create a PushPin with the new location that matches our address
'Add how many iterations of the loop it took (x) and the the Latitude, Longitude
Set oPushA = oMap.AddPushpin(oLocA, X & &quot;: &quot; & zlat & &quot;, &quot; & zlong)

'For I = 1 To 3
'oMap.Shapes.AddShape(geoShapeRadius, oLoc, radius * 2 * I, radius * 2 * I).Name = &quot;Zone&quot; & CStr(I)
'oMap.Shapes(&quot;Zone&quot; & CStr(I)).Adjustments(1) = 45
'Next I

MsgBox &quot;The Lat/Long of your address is &quot; & zlat & &quot;, &quot; & zlong & _
Chr(13) & Chr(13) & &quot;It was found in &quot; & X & &quot; iterations.&quot;, _
vbOKOnly, &quot;Lat/Long Results&quot;

'Me.[Latitude] = zlat
'Me.[Longitude] = zlong

'The next four lines are just for presentation. Uncomment them if you like
'oPush.BalloonState = geoDisplayBalloon 'Turn on the balloon state so we can see the lat/long
'oPushA.Highlight = True 'Highlight the pushpin so we really see it
'oMap.DataSets.ZoomTo 'Zoom the map to the individual pushpin
'oMap.Altitude = oMap.Altitude * 15 'alt
'oApp.Visible = True 'Turn on the map so we can see it!
'oApp.UserControl = True

'lblMapInfo.Caption = &quot;&quot;
Else
'lblMapInfo.Caption = &quot;Location Not Found!&quot;
End If
'Set oMap = Nothing
'Set oApp = Nothing
'Me.SetFocus
endnow:

End Sub

 

[jhall156]

I see. Automation is a wonderful thing.
You are not returning anything from this. Sub procedures do not return values, only a function can return a value.

If the sub is populating text boxes on a form that you can reference at runtime then this is not a problem. Otherwise you have a couple of options. Create global variables and have the sub assign them values or you can write one or more functions to handle the job.


My opinion is that you should use a stripped down version of your automation code to get the values you want within the recordset loop.

JHall

 

[BusMgr]

Thanks.

I was just finding that I wasn't returning what I as after.

BusMgr

 

[scking]

Ref: Sorry, it is a procedure, because I am not just returning a single value.

A procedure can be either a function or a subroutine. A function can return one variable or one type structure (with more than one variable) but a subroutine does NOT return anything. Your xlat and xlong variables are going we know not where. Sometimes subroutines update a module level variable and all procedures within that variable can then reference the value. If that is done then normally the variable is names something like m_variablename to indicate the level of variable.

With your procedure you are sending them off to the LatLongCalc where they are local scope variable. The results of any calculation disappear. You use them in the procedure as follows:

Set oPushA = oMap.AddPushpin(oLocA, X & &quot;: &quot; & zlat & &quot;, &quot; & zlong)

But then you reference them again in the original procedure, as
![Latitude] = xlat
![Longitude] = xlong

Steve King
Growth follows a healthy professional curiosity

 

[BusMgr]

Alright, it's is working to a point now.

I changed the following and updated the values on the form in the subroutine using

me.latitude = zlat
me.longitude = zlong
me.refresh

Then I recoded the subroutine doing the calling to
Private Sub cmdMapCounty_Click()
Dim dbs As Database
Dim rst As Recordset
Dim tdf As TableDef
Dim idx As Index
Dim s_GUID As Double
Dim strSQL As String, xlat As Double, xlong As Double

'Open the MS Access database
Set dbs = CurrentDb

'Create a TableDef object.
Set tdf = dbs.TableDefs(&quot;tblHeader1&quot

'Create the Recordset object
Set rst = dbs.OpenRecordset(&quot;tblHeader1&quot;, dbOpenTable)

rst.MoveFirst
With rst
Do Until rst.EOF
Call LatLongCalc(County, State, xlat, xlong)
strSQL = &quot;UPDATE tblHeader1 SET tblHeader1.Latitude = forms!appendLatlong!latitude, tblHeader1.Longitude = forms!appendLatlong!longtitude WHERE tblHeader1.[s_GUID] = forms!appendLatlong![s_GUID]&quot;

DoCmd.RunSQL strSQL
.MoveNext
Loop
End With

Set rst = Nothing

I works, but it does not go through the recordset. It does only the first record, unless you go onto the form and select a different record. I have over 5000 records to update and was looking for a degree of automation in the setup. How do I make it go through the recordset without interfacing with it every record?

Thanks for your help.

BusMgr