Need help with Loop that is not repeating the function

[efandango]

I want to loop through a function call with the code below. But when I run
it, the Function only works on the actual record that I am on within the
form. Is it a Loop problem or something deeper?


Private Sub btn_Runs_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String
Dim lngFN As Long
Dim strFileName As String

lngFN = FreeFile()

strStartRun_No = InputBox("Enter the lower Run No")
strEndRun_No = InputBox("Enter the higher Run No")

strFileName = "Run" & "_" & "Points_" & strStartRun_No & "-" & strEndRun_No

If Len(strStartRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML_Points_Groups")
qdf.Parameters("StartRun") = strStartRun_No
qdf.Parameters("EndRun") = strEndRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)

Do Until rs.EOF = True



rs.MoveNext
Loop

Call YahooAddressLookup(0)
Call sSleep(2000)

rs.Close

End If

Close #lngFN
MsgBox (Done)

End Sub

 

[John W. Vinson]

Do Until rs.EOF = True



rs.MoveNext
Loop

Ummm...

This is like opening a book, turning over every page, and not looking at any
of them. You're looping through every record in the recordset but not DOING
anything with the records.

What's the intent? What are you trying to accomplish?

 

[efandango]

I'm trying to call this function: YahooAddressLookup

It was in my original post. It is a function that gets a geocode from yahoo.

I want to open up a recordset, then go through each record and geocode that
record's address fields.

evrything works ok, except it doesn't 'loop', instead it just does the the
first record, then ends the process.

 

[John W. Vinson]

I'm trying to call this function: YahooAddressLookup

It was in my original post. It is a function that gets a geocode from yahoo.

I want to open up a recordset, then go through each record and geocode that
record's address fields.

evrything works ok, except it doesn't 'loop', instead it just does the the
first record, then ends the process.

It *is* looping... through the recordset, start to end... and THEN calling the
function after the last record. Put the function call inside the looping, not
after it:

Do Until rs.EOF = True
Call YahooAddressLookup(0)
Call sSleep(2000)
rs.MoveNext
Loop

Do... Loop form a bracketed set. The code starts at the Do, executes all the
statements up to the Loop statement, then branches back.

 

[efandango]

John,

I made the change, but for some reason it only updates the first record, and
then ends with my deisgnated message box, It's as if the function only
happens once and the loop doesnt' call it again. But then, I don't know
what's going wrong (i'm not a proper coder, at best, nearer a re-engineer of
code), and would appreciate some help in getting to the bottom of it.

Loop code:

************
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String
Dim lngFN As Long
Dim strFileName As String

lngFN = FreeFile()

strStartRun_No = InputBox("Enter the lower Run No")
strEndRun_No = InputBox("Enter the higher Run No")

strFileName = "Run" & "_" & "Points_" & strStartRun_No & "-" & strEndRun_No

If Len(strStartRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML_Points_Groups")
qdf.Parameters("StartRun") = strStartRun_No
qdf.Parameters("EndRun") = strEndRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)

Do Until rs.EOF = True
Call YahooAddressLookup
Call sSleep(2000)
rs.MoveNext
Loop

rs.Close

End If

Close #lngFN
MsgBox ("Done")

End Sub
************

This is the function: (which works for the 1st record only)

************
Function YahooAddressLookup(Run_point_Address As String, Run_Point_Postcode
As String) As String
' perform RESTian lookup on Yahoo
Dim response As String

url =
"http://local.yahooapis.com/MapsService/V1/geocode?appid=efandango&location="
& Run_point_Address & ", " & Run_Point_Postcode & ", London"



'Create Http object
If IsEmpty(http) Then Set http =
CreateObject("WinHttp.WinHttpRequest.5.1")

'Send request To URL
http.Open "GET", url

http.send
'Get response data As a string

response = http.responseText

' capture the latitude by regex matching the values in the tags
<geo:lat> and <geo:long>
lat = RegExMatch(response, "<Latitude>([\.\-0-9]+)</Latitude>")
lng = RegExMatch(response, "<Longitude>([\.\-0-9]+)</Longitude>")
precision = RegExMatch(response, "precision=""([a-z0-9+]+)""")

' return a comma delimited string
' if values not found, this will return ","
YahooAddressLookup = lat & "," & lng & "," & precision
If (precision <> "" And precision <> "state") Then
[address_latitude] = lat
[address_longitude] = lng
Else
[address_latitude] = "not found"
[address_longitude] = "not found"
End If

End Function
************

 

[John W. Vinson]

John,

I made the change, but for some reason it only updates the first record, and
then ends with my deisgnated message box, It's as if the function only
happens once and the loop doesnt' call it again. But then, I don't know
what's going wrong (i'm not a proper coder, at best, nearer a re-engineer of
code), and would appreciate some help in getting to the bottom of it.

Ok. You know the structure of your database; I don't. You know what you want
this code to do; I don't know that either! A few questions inline:

Loop code:

************
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String
Dim lngFN As Long
Dim strFileName As String

lngFN = FreeFile()

strStartRun_No = InputBox("Enter the lower Run No")
strEndRun_No = InputBox("Enter the higher Run No")

strFileName = "Run" & "_" & "Points_" & strStartRun_No & "-" & strEndRun_No

If Len(strStartRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML_Points_Groups")

What is this query? Could you post the SQL, or is it relevant?

qdf.Parameters("StartRun") = strStartRun_No
qdf.Parameters("EndRun") = strEndRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)

You're opening a Snapshot which is ipso facto not updateable. Don't you want a
Dynaset?

Do Until rs.EOF = True
Call YahooAddressLookup

What does YahooAddressLookup *do*? You're not calling it with any parameters
(from the recordset) - is it supposed to do something specific with each
record? If so how does it know which record you mean?

Call sSleep(2000)
rs.MoveNext
Loop

rs.Close

End If

Close #lngFN
MsgBox ("Done")

End Sub
************

This is the function: (which works for the 1st record only)

************
Function YahooAddressLookup(Run_point_Address As String, Run_Point_Postcode
As String) As String

You're not passing those values to the function. I don't know where it should
be getting them from, but I'm surprised the code even compiles since you're
calling a two-argument function with no arguments, and you're calling it as if
it were a sub rather than a function. I gather that it's looking up an
address... but what do you want to DO with that address once you've looked it
up?

I'd expect your code to use something like

rs.Edit <using a Dynaset so it's updateable rather than a Snapshot>
rs!addressfield = YahooAddressLookup(rs!Run_point_address, rs!Point_postcode)
rs.Update ' update the recordset with the looked up address

' perform RESTian lookup on Yahoo
Dim response As String

url =
"http://local.yahooapis.com/MapsService/V1/geocode?appid=efandango&location="
& Run_point_Address & ", " & Run_Point_Postcode & ", London"

I've had no experience with creating HTTP objects so I probably won't be much
help with the rest...

'Create Http object
If IsEmpty(http) Then Set http =
CreateObject("WinHttp.WinHttpRequest.5.1")

'Send request To URL
http.Open "GET", url

http.send
'Get response data As a string

response = http.responseText

' capture the latitude by regex matching the values in the tags
<geo:lat> and <geo:long>
lat = RegExMatch(response, "<Latitude>([\.\-0-9]+)</Latitude>")
lng = RegExMatch(response, "<Longitude>([\.\-0-9]+)</Longitude>")
precision = RegExMatch(response, "precision=""([a-z0-9+]+)""")

' return a comma delimited string
' if values not found, this will return ","
YahooAddressLookup = lat & "," & lng & "," & precision
If (precision <> "" And precision <> "state") Then
[address_latitude] = lat
[address_longitude] = lng
Else
[address_latitude] = "not found"
[address_longitude] = "not found"
End If

End Function
************

 

[efandango]

John,

1. "You're opening a Snapshot which is ipso facto not updateable. Don't you
want a
Dynaset"

I don't really know the difference, but for some reason, my first record in
the table behind the query does get updated, further, If I go to another
record on the form and start the process, that single record will get updated
too.


2. "What does YahooAddressLookup *do*? You're not calling it with any
parameters
(from the recordset) - is it supposed to do something specific with each
record? If so how does it know which record you mean?"

I accidentally pasted a slightly older version of the code. I changed it to
this after
I believe it should now be this:

Call YahooAddressLookup(Run_point_Address, Run_Point_Postcode)


3. "Function YahooAddressLookup(Run_point_Address As String,
Run_Point_Postcode

As String) As String

You're not passing those values to the function. I don't know where it should
be getting them from, but I'm surprised the code even compiles since you're
calling a two-argument function with no arguments, and you're calling it as if
it were a sub rather than a function. I gather that it's looking up an
address... but what do you want to DO with that address once you've looked it
up?"


I want it to get the geocodes and place them back into the record table that
the query is based on.


regards

Eric

John,

I made the change, but for some reason it only updates the first record, and
then ends with my deisgnated message box, It's as if the function only
happens once and the loop doesnt' call it again. But then, I don't know
what's going wrong (i'm not a proper coder, at best, nearer a re-engineer of
code), and would appreciate some help in getting to the bottom of it.

Ok. You know the structure of your database; I don't. You know what you want
this code to do; I don't know that either! A few questions inline:

Loop code:

************
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String
Dim lngFN As Long
Dim strFileName As String

lngFN = FreeFile()

strStartRun_No = InputBox("Enter the lower Run No")
strEndRun_No = InputBox("Enter the higher Run No")

strFileName = "Run" & "_" & "Points_" & strStartRun_No & "-" & strEndRun_No

If Len(strStartRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML_Points_Groups")

What is this query? Could you post the SQL, or is it relevant?

qdf.Parameters("StartRun") = strStartRun_No
qdf.Parameters("EndRun") = strEndRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)

You're opening a Snapshot which is ipso facto not updateable. Don't you want a
Dynaset?

Do Until rs.EOF = True
Call YahooAddressLookup

What does YahooAddressLookup *do*? You're not calling it with any parameters
(from the recordset) - is it supposed to do something specific with each
record? If so how does it know which record you mean?

Call sSleep(2000)
rs.MoveNext
Loop

rs.Close

End If

Close #lngFN
MsgBox ("Done")

End Sub
************

This is the function: (which works for the 1st record only)

************
Function YahooAddressLookup(Run_point_Address As String, Run_Point_Postcode
As String) As String

You're not passing those values to the function. I don't know where it should
be getting them from, but I'm surprised the code even compiles since you're
calling a two-argument function with no arguments, and you're calling it as if
it were a sub rather than a function. I gather that it's looking up an
address... but what do you want to DO with that address once you've looked it
up?

I'd expect your code to use something like

rs.Edit <using a Dynaset so it's updateable rather than a Snapshot>
rs!addressfield = YahooAddressLookup(rs!Run_point_address, rs!Point_postcode)
rs.Update ' update the recordset with the looked up address

' perform RESTian lookup on Yahoo
Dim response As String

url =
"http://local.yahooapis.com/MapsService/V1/geocode?appid=efandango&location="
& Run_point_Address & ", " & Run_Point_Postcode & ", London"

I've had no experience with creating HTTP objects so I probably won't be much
help with the rest...

'Create Http object
If IsEmpty(http) Then Set http =
CreateObject("WinHttp.WinHttpRequest.5.1")

'Send request To URL
http.Open "GET", url

http.send
'Get response data As a string

response = http.responseText

' capture the latitude by regex matching the values in the tags
<geo:lat> and <geo:long>
lat = RegExMatch(response, "<Latitude>([\.\-0-9]+)</Latitude>")
lng = RegExMatch(response, "<Longitude>([\.\-0-9]+)</Longitude>")
precision = RegExMatch(response, "precision=""([a-z0-9+]+)""")

' return a comma delimited string
' if values not found, this will return ","
YahooAddressLookup = lat & "," & lng & "," & precision
If (precision <> "" And precision <> "state") Then
[address_latitude] = lat
[address_longitude] = lng
Else
[address_latitude] = "not found"
[address_longitude] = "not found"
End If

End Function
************

 

[efandango]

John,

this is my query, nothing special about it:

SELECT tbl_Points.Run_No, tbl_Points.Run_point_Venue,
tbl_Points.Run_point_Address, tbl_Points.Run_Point_Postcode,
tbl_Points.Custom_Point, tbl_Points.lat, tbl_Points.lng
FROM tbl_Points
WHERE (((tbl_Points.Run_No) Between [StartRun] And [EndRun]) AND
((tbl_Points.Custom_Point)=0));

it is the Points.lat and Points.lng fields that I want to update across the
recordset.


Also, I'm not sure where you intended this to go.

rs.Edit
rs!addressfield = YahooAddressLookup(rs!Run_point_Address, rs!Point_postcode)
rs.Update ' update the recordset with the looked up address

In the Function or The Sub?
and what would addressfield be?, I have no reference to addressfield.

regards

Eric

John,

I made the change, but for some reason it only updates the first record, and
then ends with my deisgnated message box, It's as if the function only
happens once and the loop doesnt' call it again. But then, I don't know
what's going wrong (i'm not a proper coder, at best, nearer a re-engineer of
code), and would appreciate some help in getting to the bottom of it.

Ok. You know the structure of your database; I don't. You know what you want
this code to do; I don't know that either! A few questions inline:

Loop code:

************
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String
Dim lngFN As Long
Dim strFileName As String

lngFN = FreeFile()

strStartRun_No = InputBox("Enter the lower Run No")
strEndRun_No = InputBox("Enter the higher Run No")

strFileName = "Run" & "_" & "Points_" & strStartRun_No & "-" & strEndRun_No

If Len(strStartRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML_Points_Groups")

What is this query? Could you post the SQL, or is it relevant?

qdf.Parameters("StartRun") = strStartRun_No
qdf.Parameters("EndRun") = strEndRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)

You're opening a Snapshot which is ipso facto not updateable. Don't you want a
Dynaset?

Do Until rs.EOF = True
Call YahooAddressLookup

What does YahooAddressLookup *do*? You're not calling it with any parameters
(from the recordset) - is it supposed to do something specific with each
record? If so how does it know which record you mean?

Call sSleep(2000)
rs.MoveNext
Loop

rs.Close

End If

Close #lngFN
MsgBox ("Done")

End Sub
************

This is the function: (which works for the 1st record only)

************
Function YahooAddressLookup(Run_point_Address As String, Run_Point_Postcode
As String) As String

You're not passing those values to the function. I don't know where it should
be getting them from, but I'm surprised the code even compiles since you're
calling a two-argument function with no arguments, and you're calling it as if
it were a sub rather than a function. I gather that it's looking up an
address... but what do you want to DO with that address once you've looked it
up?

I'd expect your code to use something like

rs.Edit <using a Dynaset so it's updateable rather than a Snapshot>
rs!addressfield = YahooAddressLookup(rs!Run_point_address, rs!Point_postcode)
rs.Update ' update the recordset with the looked up address

' perform RESTian lookup on Yahoo
Dim response As String

url =
"http://local.yahooapis.com/MapsService/V1/geocode?appid=efandango&location="
& Run_point_Address & ", " & Run_Point_Postcode & ", London"

I've had no experience with creating HTTP objects so I probably won't be much
help with the rest...

'Create Http object
If IsEmpty(http) Then Set http =
CreateObject("WinHttp.WinHttpRequest.5.1")

'Send request To URL
http.Open "GET", url

http.send
'Get response data As a string

response = http.responseText

' capture the latitude by regex matching the values in the tags
<geo:lat> and <geo:long>
lat = RegExMatch(response, "<Latitude>([\.\-0-9]+)</Latitude>")
lng = RegExMatch(response, "<Longitude>([\.\-0-9]+)</Longitude>")
precision = RegExMatch(response, "precision=""([a-z0-9+]+)""")

' return a comma delimited string
' if values not found, this will return ","
YahooAddressLookup = lat & "," & lng & "," & precision
If (precision <> "" And precision <> "state") Then
[address_latitude] = lat
[address_longitude] = lng
Else
[address_latitude] = "not found"
[address_longitude] = "not found"
End If

End Function
************

 

[John W. Vinson]

Call YahooAddressLookup(Run_point_Address, Run_Point_Postcode)


3. "Function YahooAddressLookup(Run_point_Address As String,
Run_Point_Postcode

You're not passing those values to the function. I don't know where it should
be getting them from, but I'm surprised the code even compiles since you're
calling a two-argument function with no arguments, and you're calling it as if
it were a sub rather than a function. I gather that it's looking up an
address... but what do you want to DO with that address once you've looked it
up?"


I want it to get the geocodes and place them back into the record table that
the query is based on.

Note that you can Call a Function - but if you do so the value it returns will
be lost. If you want to return a value and use it, the syntax is

targetvalue = Functionname(arg, arg, arg)

A Function returns a value (one value). It seems (thanks for telling me, I
didn't know before :-{( ) that you want to look up a latitude and a longitude.
If so you need to either pass the function your recordset object, or have two
functions which look up those two values, or do this in some other way. It
seems you're trying to call a function and have it automagically determine
what you want to do with the result of the function... and it won't.

You say "there was no address field" but... what is Run_point_address if that
is the case?

 

[efandango]

John,

Thanks for the feedback, I will re-read it tomorrow, can I come back to you
gain on this?. I am in the UK, and it's past midnight now, and I've been on
this all weekend...

regards

Eric

 

[John W. Vinson]

Thanks for the feedback, I will re-read it tomorrow, can I come back to you
gain on this?. I am in the UK, and it's past midnight now, and I've been on
this all weekend...

Sure, Eric - hope you got some rest.

 

[efandango]

John,

Going back to your most recent comment:

"You're not passing those values to the function. I don't know where it should
be getting them from, but I'm surprised the code even compiles since you're
calling a two-argument function with no arguments, and you're calling it as if
it were a sub rather than a function. I gather that it's looking up an
address... but what do you want to DO with that address once you've looked it
up?"

I think this whole paragraph sums up where I am, and am not...

I don't know enough to know where or how to feed the function the neccesary
address data that it can then use to poll yahoo's gecoder and get back the
lat/lon gecodes that I want to feed back into my table. As have I said, I
have managed to do it with any one record (that the form is open on), but
only the one record; not a whole recordset. I assume in my ignorance that If
the function was happily doing it for one record, then by putting in a loop,
it would iterate through the recordset and do them all. Obviously I was wrong
in that assumption, hence the original reuqest in this thread.


As I see it, the function should be geting the records from the open
recordset: 'Generate_KML_Points_Groups'

as in this example:
Set qdf = db.QueryDefs("Generate_KML_Points_Groups")
Set rs = qdf.OpenRecordset(dbOpenDynaset)

as to how I actually 'feed' them sequentially to the function, I just don't
know, because I thought the loop would take care of that.

I don't know where to go from here, except to ask for some help on getting
the records from the recordset to parse through the function and write the
resultant yahoo data back to the recordset/table.

regards

Eric