output a series of field records to a text string using a loop?

[Guest]

previously, I was able to do this by having seperate field names; for example:

stAppName = "C:\Program Files\Internet Explorer\iexplore.exe
http://maps.google.co.uk/maps?f=q&hl=en&q= " & "from: " & Link_Waypoint_1 +
", London" & (" to: " + Link_Waypoint_2 + ", London") & (" to: " +
Link_Waypoint_3 + ", London") & (" to: " + Link_Waypoint_4 + ", London") & ("
to: " + Link_Waypoint_5 + ", London")

But now, instead of a 'spreadsheet style table' I have a 'normal' table
containing just the one (significant) field [Link_waypoint] containg a series
of records bound by a common [Run_No].

So I need to have the event code go through each field record and parse it
to the string as a sequence of records based on a master record No [Run_No]

can anybody help me with this problem. I figured that I would need some kind
of loop, but can't get my head around the code.

the example table setup is:

[Run_No] [Link_waypoint]
5 Main St
5 South St
5 Narrow St
5 Wide St
5 Chester St

 

[Graham Mandeno]

Congratulations! It's always good to see a new convert to the righteous
path of normalisation

I think that you will need another field in your Waypoints table to ensure
the waypoints are visited in the correct order. Let's call it [WP_Seq].
Now your data will look like this:

[Run_No] [WP_Seq] [Link_waypoint]
5 1 Main St
5 2 South St
5 3 Narrow St
5 4 Wide St
5 5 Chester St

Now you need to open a recordset based on a SQL query, and loop through the
records for a given [Run_No], appending each [Link_waypoint] to the string.
Code will look something like this:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sRoute as String
Dim iWPCount as Integer
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"Select [Link_waypoint] from tblWaypoints where [Run_No]=" _
& lngRunNo & " order by [WP_Seq];", dbOpenForwardOnly)
Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf( iWPCount=1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 then
Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q="
_
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If

This is "air code" (untested and written on the fly) so sorry if there are
any typos or other errors in it. Note that I'm using
"Application.FollowHyperlink" instead of shelling iexplore. Easier and more
reliable.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

PS: Grudging congrats to your rugby players ;-)

 

[Guest]

Graham, Thanks for replying.

I tried your code with a slight ammendment to the extra field that you
suggested, I did already have an autonumber field for that purpose
[Link_List_ID]. Anyhow, I tried your code and when I run it, I get the
following error:

Run-time error '3074'

Syntax error (missing operator) in query expression '[Run_No]='

Here is my code:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select [Link_waypoint] from tbl_Link_Waypoints
where [Run_No]=" & lngRunNo & " order by [Link_List_ID];", dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

Congratulations! It's always good to see a new convert to the righteous
path of normalisation

I think that you will need another field in your Waypoints table to ensure
the waypoints are visited in the correct order. Let's call it [WP_Seq].
Now your data will look like this:

[Run_No] [WP_Seq] [Link_waypoint]
5 1 Main St
5 2 South St
5 3 Narrow St
5 4 Wide St
5 5 Chester St

Now you need to open a recordset based on a SQL query, and loop through the
records for a given [Run_No], appending each [Link_waypoint] to the string.
Code will look something like this:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sRoute as String
Dim iWPCount as Integer
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"Select [Link_waypoint] from tblWaypoints where [Run_No]=" _
& lngRunNo & " order by [WP_Seq];", dbOpenForwardOnly)
Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf( iWPCount=1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 then
Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q="
_
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If

This is "air code" (untested and written on the fly) so sorry if there are
any typos or other errors in it. Note that I'm using
"Application.FollowHyperlink" instead of shelling iexplore. Easier and more
reliable.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

PS: Grudging congrats to your rugby players ;-)

previously, I was able to do this by having seperate field names; for
example:

stAppName = "C:\Program Files\Internet Explorer\iexplore.exe
http://maps.google.co.uk/maps?f=q&hl=en&q= " & "from: " & Link_Waypoint_1
+
", London" & (" to: " + Link_Waypoint_2 + ", London") & (" to: " +
Link_Waypoint_3 + ", London") & (" to: " + Link_Waypoint_4 + ", London") &
("
to: " + Link_Waypoint_5 + ", London")

But now, instead of a 'spreadsheet style table' I have a 'normal' table
containing just the one (significant) field [Link_waypoint] containg a
series
of records bound by a common [Run_No].

So I need to have the event code go through each field record and parse it
to the string as a sequence of records based on a master record No
[Run_No]

can anybody help me with this problem. I figured that I would need some
kind
of loop, but can't get my head around the code.

the example table setup is:

[Run_No] [Link_waypoint]
5 Main St
5 South St
5 Narrow St
5 Wide St
5 Chester St

 

[Graham Mandeno]

You missed this important bit:

'Assume lngRunNo is either declared elsewhere or passed as an argument

You could put this code in the Click event procedure for a button on a form,
where the Run_No is in a textbox named "txtRun_No". In this case, you can
substitute the name of the textbox in the SQL string to open the recordset:
.... where [Run_No]=" & Me.txtRun_No & ...

Or, you could put this code in a function in a standard module, like this:

Public Function ShowRouteMap( lngRunNo as Long )
<paste code here>
End Function

This method is especially suitable if you need to display the map from more
than one form in your application. To call it, simply pass a route number
as the argument:

Call ShowRouteMap( Me.txtRun_No )

Or you could call the function from the property sheet of a command button:

OnClick: =ShowRouteMap( [txtRun_No] )

Also, and most importantly, you should always disallow the implicit
declaration of variables!!!!! To do this, always ensure that the first line
of EVERY module is:
Option Explicit

You can make this happen automatically for new modules by selecting "Require
Variable Declaration" in Tools>Options (from the code window)
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, Thanks for replying.

I tried your code with a slight ammendment to the extra field that you
suggested, I did already have an autonumber field for that purpose
[Link_List_ID]. Anyhow, I tried your code and when I run it, I get the
following error:

Run-time error '3074'

Syntax error (missing operator) in query expression '[Run_No]='

Here is my code:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select [Link_waypoint] from tbl_Link_Waypoints
where [Run_No]=" & lngRunNo & " order by [Link_List_ID];",
dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

Congratulations! It's always good to see a new convert to the righteous
path of normalisation

I think that you will need another field in your Waypoints table to
ensure
the waypoints are visited in the correct order. Let's call it [WP_Seq].
Now your data will look like this:

[Run_No] [WP_Seq] [Link_waypoint]
5 1 Main St
5 2 South St
5 3 Narrow St
5 4 Wide St
5 5 Chester St

Now you need to open a recordset based on a SQL query, and loop through
the
records for a given [Run_No], appending each [Link_waypoint] to the
string.
Code will look something like this:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sRoute as String
Dim iWPCount as Integer
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"Select [Link_waypoint] from tblWaypoints where [Run_No]=" _
& lngRunNo & " order by [WP_Seq];", dbOpenForwardOnly)
Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf( iWPCount=1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
_
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If

This is "air code" (untested and written on the fly) so sorry if there
are
any typos or other errors in it. Note that I'm using
"Application.FollowHyperlink" instead of shelling iexplore. Easier and
more
reliable.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

PS: Grudging congrats to your rugby players ;-)

previously, I was able to do this by having seperate field names; for
example:

stAppName = "C:\Program Files\Internet Explorer\iexplore.exe
http://maps.google.co.uk/maps?f=q&hl=en&q= " & "from: " &
Link_Waypoint_1
+
", London" & (" to: " + Link_Waypoint_2 + ", London") & (" to: " +
Link_Waypoint_3 + ", London") & (" to: " + Link_Waypoint_4 + ",
London") &
("
to: " + Link_Waypoint_5 + ", London")

But now, instead of a 'spreadsheet style table' I have a 'normal' table
containing just the one (significant) field [Link_waypoint] containg a
series
of records bound by a common [Run_No].

So I need to have the event code go through each field record and parse
it
to the string as a sequence of records based on a master record No
[Run_No]

can anybody help me with this problem. I figured that I would need some
kind
of loop, but can't get my head around the code.

the example table setup is:

[Run_No] [Link_waypoint]
5 Main St
5 South St
5 Narrow St
5 Wide St
5 Chester St

 

[Guest]

Graham,

I'm a bit confused, I'm not an experienced coder in any sense. but...

I changed the line

Run_No]=" & lngRun_No & "...
to
[Run_No]=" & Me.Run_No & "...

(I don't quite understand where 'txtRun_No' came from or where it is
referenced, my field is always called Run_No)


and now I get this error:

Run-time error '87':
An unexpected error has occured

and the VBA window now highlights this code further down the line:

Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute

For the record, my code is on a button on the form that is pushed after the
user has chosen his route via a combo box. At the moment the code is exlusive
to this button, once I get it working, I will most likely create a module,
but I'm not very experienced with modules, so prefer to keep the coding bits
tied to a given button/event.

You missed this important bit:

'Assume lngRunNo is either declared elsewhere or passed as an argument

You could put this code in the Click event procedure for a button on a form,
where the Run_No is in a textbox named "txtRun_No". In this case, you can
substitute the name of the textbox in the SQL string to open the recordset:
... where [Run_No]=" & Me.txtRun_No & ...

Or, you could put this code in a function in a standard module, like this:

Public Function ShowRouteMap( lngRunNo as Long )
<paste code here>
End Function

This method is especially suitable if you need to display the map from more
than one form in your application. To call it, simply pass a route number
as the argument:

Call ShowRouteMap( Me.txtRun_No )

Or you could call the function from the property sheet of a command button:

OnClick: =ShowRouteMap( [txtRun_No] )

Also, and most importantly, you should always disallow the implicit
declaration of variables!!!!! To do this, always ensure that the first line
of EVERY module is:
Option Explicit

You can make this happen automatically for new modules by selecting "Require
Variable Declaration" in Tools>Options (from the code window)
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, Thanks for replying.

I tried your code with a slight ammendment to the extra field that you
suggested, I did already have an autonumber field for that purpose
[Link_List_ID]. Anyhow, I tried your code and when I run it, I get the
following error:

Run-time error '3074'

Syntax error (missing operator) in query expression '[Run_No]='

Here is my code:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select [Link_waypoint] from tbl_Link_Waypoints
where [Run_No]=" & lngRunNo & " order by [Link_List_ID];",
dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

Congratulations! It's always good to see a new convert to the righteous
path of normalisation

I think that you will need another field in your Waypoints table to
ensure
the waypoints are visited in the correct order. Let's call it [WP_Seq].
Now your data will look like this:

[Run_No] [WP_Seq] [Link_waypoint]
5 1 Main St
5 2 South St
5 3 Narrow St
5 4 Wide St
5 5 Chester St

Now you need to open a recordset based on a SQL query, and loop through
the
records for a given [Run_No], appending each [Link_waypoint] to the
string.
Code will look something like this:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sRoute as String
Dim iWPCount as Integer
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"Select [Link_waypoint] from tblWaypoints where [Run_No]=" _
& lngRunNo & " order by [WP_Seq];", dbOpenForwardOnly)
Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf( iWPCount=1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
_
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If

This is "air code" (untested and written on the fly) so sorry if there
are
any typos or other errors in it. Note that I'm using
"Application.FollowHyperlink" instead of shelling iexplore. Easier and
more
reliable.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

PS: Grudging congrats to your rugby players ;-)

previously, I was able to do this by having seperate field names; for
example:

stAppName = "C:\Program Files\Internet Explorer\iexplore.exe
http://maps.google.co.uk/maps?f=q&hl=en&q= " & "from: " &
Link_Waypoint_1
+
", London" & (" to: " + Link_Waypoint_2 + ", London") & (" to: " +
Link_Waypoint_3 + ", London") & (" to: " + Link_Waypoint_4 + ",
London") &
("
to: " + Link_Waypoint_5 + ", London")

But now, instead of a 'spreadsheet style table' I have a 'normal' table
containing just the one (significant) field [Link_waypoint] containg a
series
of records bound by a common [Run_No].

So I need to have the event code go through each field record and parse
it
to the string as a sequence of records based on a master record No
[Run_No]

can anybody help me with this problem. I figured that I would need some
kind
of loop, but can't get my head around the code.

the example table setup is:

[Run_No] [Link_waypoint]
5 Main St
5 South St
5 Narrow St
5 Wide St
5 Chester St

 

[Guest]

Graham,

I forgot to paste my latest code in the last mesage: this is the code in full.

*********************************
Option Compare Database
Option Explicit

Private Sub Command17_Click()
'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select [Link_waypoint] from tbl_Link_Waypoints
where [Run_No]=" & Me.Run_No & " order by [Link_List_ID];", dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

*********************************

You missed this important bit:

'Assume lngRunNo is either declared elsewhere or passed as an argument

You could put this code in the Click event procedure for a button on a form,
where the Run_No is in a textbox named "txtRun_No". In this case, you can
substitute the name of the textbox in the SQL string to open the recordset:
... where [Run_No]=" & Me.txtRun_No & ...

Or, you could put this code in a function in a standard module, like this:

Public Function ShowRouteMap( lngRunNo as Long )
<paste code here>
End Function

This method is especially suitable if you need to display the map from more
than one form in your application. To call it, simply pass a route number
as the argument:

Call ShowRouteMap( Me.txtRun_No )

Or you could call the function from the property sheet of a command button:

OnClick: =ShowRouteMap( [txtRun_No] )

Also, and most importantly, you should always disallow the implicit
declaration of variables!!!!! To do this, always ensure that the first line
of EVERY module is:
Option Explicit

You can make this happen automatically for new modules by selecting "Require
Variable Declaration" in Tools>Options (from the code window)
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, Thanks for replying.

I tried your code with a slight ammendment to the extra field that you
suggested, I did already have an autonumber field for that purpose
[Link_List_ID]. Anyhow, I tried your code and when I run it, I get the
following error:

Run-time error '3074'

Syntax error (missing operator) in query expression '[Run_No]='

Here is my code:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select [Link_waypoint] from tbl_Link_Waypoints
where [Run_No]=" & lngRunNo & " order by [Link_List_ID];",
dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

Congratulations! It's always good to see a new convert to the righteous
path of normalisation

I think that you will need another field in your Waypoints table to
ensure
the waypoints are visited in the correct order. Let's call it [WP_Seq].
Now your data will look like this:

[Run_No] [WP_Seq] [Link_waypoint]
5 1 Main St
5 2 South St
5 3 Narrow St
5 4 Wide St
5 5 Chester St

Now you need to open a recordset based on a SQL query, and loop through
the
records for a given [Run_No], appending each [Link_waypoint] to the
string.
Code will look something like this:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sRoute as String
Dim iWPCount as Integer
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"Select [Link_waypoint] from tblWaypoints where [Run_No]=" _
& lngRunNo & " order by [WP_Seq];", dbOpenForwardOnly)
Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf( iWPCount=1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
_
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If

This is "air code" (untested and written on the fly) so sorry if there
are
any typos or other errors in it. Note that I'm using
"Application.FollowHyperlink" instead of shelling iexplore. Easier and
more
reliable.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

PS: Grudging congrats to your rugby players ;-)

previously, I was able to do this by having seperate field names; for
example:

stAppName = "C:\Program Files\Internet Explorer\iexplore.exe
http://maps.google.co.uk/maps?f=q&hl=en&q= " & "from: " &
Link_Waypoint_1
+
", London" & (" to: " + Link_Waypoint_2 + ", London") & (" to: " +
Link_Waypoint_3 + ", London") & (" to: " + Link_Waypoint_4 + ",
London") &
("
to: " + Link_Waypoint_5 + ", London")

But now, instead of a 'spreadsheet style table' I have a 'normal' table
containing just the one (significant) field [Link_waypoint] containg a
series
of records bound by a common [Run_No].

So I need to have the event code go through each field record and parse
it
to the string as a sequence of records based on a master record No
[Run_No]

can anybody help me with this problem. I figured that I would need some
kind
of loop, but can't get my head around the code.

the example table setup is:

[Run_No] [Link_waypoint]
5 Main St
5 South St
5 Narrow St
5 Wide St
5 Chester St

 

[Guest]

Graham,

It works!... I posted back to you saying that this line:

Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute


was now giving me problems, but somehow felt that it shouldn't, so i looked
at my forms again, and realised that the [Run_No] was not in the 2nd
subform/table. I have now rejigged that arrangement and have managed to get
your beautiful code to fire up explorer and load the map. The problem I now
have is that specific to googlemaps, it likes a postcode and city name. I
have previous experience of parsing this, so will try and adapt your code, I
may ask you for further help if that's ok with you. But in the meantime,
thanks so much for your help on this.

PS: meanwhile, England expects every man to do his duty this coming
Saturday... (fingers crossed...)

Graham,

I forgot to paste my latest code in the last mesage: this is the code in full.

*********************************
Option Compare Database
Option Explicit

Private Sub Command17_Click()
'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select [Link_waypoint] from tbl_Link_Waypoints
where [Run_No]=" & Me.Run_No & " order by [Link_List_ID];", dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

*********************************

You missed this important bit:

'Assume lngRunNo is either declared elsewhere or passed as an argument

You could put this code in the Click event procedure for a button on a form,
where the Run_No is in a textbox named "txtRun_No". In this case, you can
substitute the name of the textbox in the SQL string to open the recordset:
... where [Run_No]=" & Me.txtRun_No & ...

Or, you could put this code in a function in a standard module, like this:

Public Function ShowRouteMap( lngRunNo as Long )
<paste code here>
End Function

This method is especially suitable if you need to display the map from more
than one form in your application. To call it, simply pass a route number
as the argument:

Call ShowRouteMap( Me.txtRun_No )

Or you could call the function from the property sheet of a command button:

OnClick: =ShowRouteMap( [txtRun_No] )

Also, and most importantly, you should always disallow the implicit
declaration of variables!!!!! To do this, always ensure that the first line
of EVERY module is:
Option Explicit

You can make this happen automatically for new modules by selecting "Require
Variable Declaration" in Tools>Options (from the code window)
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, Thanks for replying.

I tried your code with a slight ammendment to the extra field that you
suggested, I did already have an autonumber field for that purpose
[Link_List_ID]. Anyhow, I tried your code and when I run it, I get the
following error:

Run-time error '3074'

Syntax error (missing operator) in query expression '[Run_No]='

Here is my code:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select [Link_waypoint] from tbl_Link_Waypoints
where [Run_No]=" & lngRunNo & " order by [Link_List_ID];",
dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub


:

Congratulations! It's always good to see a new convert to the righteous
path of normalisation

I think that you will need another field in your Waypoints table to
ensure
the waypoints are visited in the correct order. Let's call it [WP_Seq].
Now your data will look like this:

[Run_No] [WP_Seq] [Link_waypoint]
5 1 Main St
5 2 South St
5 3 Narrow St
5 4 Wide St
5 5 Chester St

Now you need to open a recordset based on a SQL query, and loop through
the
records for a given [Run_No], appending each [Link_waypoint] to the
string.
Code will look something like this:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sRoute as String
Dim iWPCount as Integer
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"Select [Link_waypoint] from tblWaypoints where [Run_No]=" _
& lngRunNo & " order by [WP_Seq];", dbOpenForwardOnly)
Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf( iWPCount=1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
_
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If

This is "air code" (untested and written on the fly) so sorry if there
are
any typos or other errors in it. Note that I'm using
"Application.FollowHyperlink" instead of shelling iexplore. Easier and
more
reliable.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

PS: Grudging congrats to your rugby players ;-)

previously, I was able to do this by having seperate field names; for
example:

stAppName = "C:\Program Files\Internet Explorer\iexplore.exe
http://maps.google.co.uk/maps?f=q&hl=en&q= " & "from: " &
Link_Waypoint_1
+
", London" & (" to: " + Link_Waypoint_2 + ", London") & (" to: " +
Link_Waypoint_3 + ", London") & (" to: " + Link_Waypoint_4 + ",
London") &
("
to: " + Link_Waypoint_5 + ", London")

But now, instead of a 'spreadsheet style table' I have a 'normal' table
containing just the one (significant) field [Link_waypoint] containg a
series
of records bound by a common [Run_No].

So I need to have the event code go through each field record and parse
it
to the string as a sequence of records based on a master record No
[Run_No]

can anybody help me with this problem. I figured that I would need some
kind
of loop, but can't get my head around the code.

the example table setup is:

[Run_No] [Link_waypoint]
5 Main St
5 South St
5 Narrow St
5 Wide St
5 Chester St

 

[Guest]

Graham,

I have resolved the problem, but now when I try to incorporate the postcode;
only the last postcode in the recordset is passed to googlemaps. the postcode
field is: [Link_Waypoint_Pcode]


this is the result i get from googlemaps:

from: Liverpool Road, London NW6 to: Abbey Road, London NW6

from this table:

Link_List_ID Run_No Run_Link_ID Link_waypoint Link_Waypoint_Pcode
158 1 1 Liverpool Road N1
159 1 1 Abbey Road NW6


Here is my present code

******************************

Private Sub Command17_Click()
'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select Link_waypoint, Link_Waypoint_Pcode from
tbl_Link_Waypoints where [Run_No]=" & Me.Run_No & " order by
[Link_List_ID];", dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London " & [Link_Waypoint_Pcode]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

************************************

Congratulations! It's always good to see a new convert to the righteous
path of normalisation

I think that you will need another field in your Waypoints table to ensure
the waypoints are visited in the correct order. Let's call it [WP_Seq].
Now your data will look like this:

[Run_No] [WP_Seq] [Link_waypoint]
5 1 Main St
5 2 South St
5 3 Narrow St
5 4 Wide St
5 5 Chester St

Now you need to open a recordset based on a SQL query, and loop through the
records for a given [Run_No], appending each [Link_waypoint] to the string.
Code will look something like this:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sRoute as String
Dim iWPCount as Integer
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"Select [Link_waypoint] from tblWaypoints where [Run_No]=" _
& lngRunNo & " order by [WP_Seq];", dbOpenForwardOnly)
Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf( iWPCount=1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 then
Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q="
_
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If

This is "air code" (untested and written on the fly) so sorry if there are
any typos or other errors in it. Note that I'm using
"Application.FollowHyperlink" instead of shelling iexplore. Easier and more
reliable.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

PS: Grudging congrats to your rugby players ;-)

previously, I was able to do this by having seperate field names; for
example:

stAppName = "C:\Program Files\Internet Explorer\iexplore.exe
http://maps.google.co.uk/maps?f=q&hl=en&q= " & "from: " & Link_Waypoint_1
+
", London" & (" to: " + Link_Waypoint_2 + ", London") & (" to: " +
Link_Waypoint_3 + ", London") & (" to: " + Link_Waypoint_4 + ", London") &
("
to: " + Link_Waypoint_5 + ", London")

But now, instead of a 'spreadsheet style table' I have a 'normal' table
containing just the one (significant) field [Link_waypoint] containg a
series
of records bound by a common [Run_No].

So I need to have the event code go through each field record and parse it
to the string as a sequence of records based on a master record No
[Run_No]

can anybody help me with this problem. I figured that I would need some
kind
of loop, but can't get my head around the code.

the example table setup is:

[Run_No] [Link_waypoint]
5 Main St
5 South St
5 Narrow St
5 Wide St
5 Chester St

 

[Guest]

Graham,

sorry for the succesive posts, but they are really a timeline to how I
solved the problem (with your massive input...)

the key line seemed to be:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London, " & rs![Link_Waypoint_Pcode]

the previous attempt, kept giving me the same postcode for all addresses, so
I added an additional 'rs!' before the last field request, as in:

& rs![Link_Waypoint_Pcode]

and this finally solved the entire problem!.

BUT... I don't know why? (I'm a complete novice at recordset stuff), can you
explain why and how this made the difference, and maybe how this whole code
actually works.

regards

Eric

Graham,

I have resolved the problem, but now when I try to incorporate the postcode;
only the last postcode in the recordset is passed to googlemaps. the postcode
field is: [Link_Waypoint_Pcode]


this is the result i get from googlemaps:

from: Liverpool Road, London NW6 to: Abbey Road, London NW6

from this table:

Link_List_ID Run_No Run_Link_ID Link_waypoint Link_Waypoint_Pcode
158 1 1 Liverpool Road N1
159 1 1 Abbey Road NW6


Here is my present code

******************************

Private Sub Command17_Click()
'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select Link_waypoint, Link_Waypoint_Pcode from
tbl_Link_Waypoints where [Run_No]=" & Me.Run_No & " order by
[Link_List_ID];", dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London " & [Link_Waypoint_Pcode]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

************************************

Congratulations! It's always good to see a new convert to the righteous
path of normalisation

I think that you will need another field in your Waypoints table to ensure
the waypoints are visited in the correct order. Let's call it [WP_Seq].
Now your data will look like this:

[Run_No] [WP_Seq] [Link_waypoint]
5 1 Main St
5 2 South St
5 3 Narrow St
5 4 Wide St
5 5 Chester St

Now you need to open a recordset based on a SQL query, and loop through the
records for a given [Run_No], appending each [Link_waypoint] to the string.
Code will look something like this:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sRoute as String
Dim iWPCount as Integer
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"Select [Link_waypoint] from tblWaypoints where [Run_No]=" _
& lngRunNo & " order by [WP_Seq];", dbOpenForwardOnly)
Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf( iWPCount=1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 then
Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q="
_
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If

This is "air code" (untested and written on the fly) so sorry if there are
any typos or other errors in it. Note that I'm using
"Application.FollowHyperlink" instead of shelling iexplore. Easier and more
reliable.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

PS: Grudging congrats to your rugby players ;-)

previously, I was able to do this by having seperate field names; for
example:

stAppName = "C:\Program Files\Internet Explorer\iexplore.exe
http://maps.google.co.uk/maps?f=q&hl=en&q= " & "from: " & Link_Waypoint_1
+
", London" & (" to: " + Link_Waypoint_2 + ", London") & (" to: " +
Link_Waypoint_3 + ", London") & (" to: " + Link_Waypoint_4 + ", London") &
("
to: " + Link_Waypoint_5 + ", London")

But now, instead of a 'spreadsheet style table' I have a 'normal' table
containing just the one (significant) field [Link_waypoint] containg a
series
of records bound by a common [Run_No].

So I need to have the event code go through each field record and parse it
to the string as a sequence of records based on a master record No
[Run_No]

can anybody help me with this problem. I figured that I would need some
kind
of loop, but can't get my head around the code.

the example table setup is:

[Run_No] [Link_waypoint]
5 Main St
5 South St
5 Narrow St
5 Wide St
5 Chester St

 

[Graham Mandeno]

Hi Eric

Ah... the difficulties of time zones! You did all this work while I was
asleep - well done!

I suspect you have on your form a textbox named "Link_Waypoint_Pcode".
Therefore, when you use this expression:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") _
& rs![Link_waypoint] & ", " & "London, " & [Link_Waypoint_Pcode]

(note the absence if the "rs!") you are using the value in that /textbox/
every time around the loop, and therefore getting the same postcode repeated
each time.

When you use rs![Link_Waypoint_Pcode] you are referencing the /field/ named
[Link_Waypoint_Pcode] in your recordset, and that changes for each loop
iteration.

If you will allow me to suggest a few improvements:

1) Link_List_ID sounds suspiciously like an autonumber primary key. The
problem with this is that you have no control over its value, and therefore
the order of the waypoints. This means you will run into problems if you
(a) change the order of the waypoints in a run, or (b) wish to add a new
waypoint part way through a run. My suggestion was that you use a
non-unique numeric field named "Link_Waypoint_Order" (or similar) and number
the waypoints /within/ a run in the required order. Then you can change the
numbers when you want to change the order or insert a new waypoint. Note
that you should use this field in the "order by" clause when you open the
recordset.

2) It may not happen this year, but some time your database might be used
outside London. I suggest you add a "Link_Waypoint_City" field to your
table to give you that flexibility. If you wish, you can set its default
value to "London".

And a question to satisfy my curiosity: I thought the old "N1", "SW4"
postcodes were now obsolete in the UK. Does Google fully understand them,
and wouldn't you be better to use the new postcode format?

Best of luck to the lads for Saturday. I'm not sure who I want to win -
it's all too depressing! ;-)
--
Cheers,

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

sorry for the succesive posts, but they are really a timeline to how I
solved the problem (with your massive input...)

the key line seemed to be:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London, " & rs![Link_Waypoint_Pcode]

the previous attempt, kept giving me the same postcode for all addresses,
so
I added an additional 'rs!' before the last field request, as in:

& rs![Link_Waypoint_Pcode]

and this finally solved the entire problem!.

BUT... I don't know why? (I'm a complete novice at recordset stuff), can
you
explain why and how this made the difference, and maybe how this whole
code
actually works.

regards

Eric

Graham,

I have resolved the problem, but now when I try to incorporate the
postcode;
only the last postcode in the recordset is passed to googlemaps. the
postcode
field is: [Link_Waypoint_Pcode]


this is the result i get from googlemaps:

from: Liverpool Road, London NW6 to: Abbey Road, London NW6

from this table:

Link_List_ID Run_No Run_Link_ID Link_waypoint Link_Waypoint_Pcode
158 1 1 Liverpool Road N1
159 1 1 Abbey Road NW6


Here is my present code

******************************

Private Sub Command17_Click()
'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select Link_waypoint, Link_Waypoint_Pcode from
tbl_Link_Waypoints where [Run_No]=" & Me.Run_No & " order by
[Link_List_ID];", dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London " & [Link_Waypoint_Pcode]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

************************************

Congratulations! It's always good to see a new convert to the
righteous
path of normalisation

I think that you will need another field in your Waypoints table to
ensure
the waypoints are visited in the correct order. Let's call it
[WP_Seq].
Now your data will look like this:

[Run_No] [WP_Seq] [Link_waypoint]
5 1 Main St
5 2 South St
5 3 Narrow St
5 4 Wide St
5 5 Chester St

Now you need to open a recordset based on a SQL query, and loop through
the
records for a given [Run_No], appending each [Link_waypoint] to the
string.
Code will look something like this:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sRoute as String
Dim iWPCount as Integer
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"Select [Link_waypoint] from tblWaypoints where [Run_No]=" _
& lngRunNo & " order by [WP_Seq];", dbOpenForwardOnly)
Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf( iWPCount=1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
_
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If

This is "air code" (untested and written on the fly) so sorry if there
are
any typos or other errors in it. Note that I'm using
"Application.FollowHyperlink" instead of shelling iexplore. Easier and
more
reliable.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

PS: Grudging congrats to your rugby players ;-)

previously, I was able to do this by having seperate field names; for
example:

stAppName = "C:\Program Files\Internet Explorer\iexplore.exe
http://maps.google.co.uk/maps?f=q&hl=en&q= " & "from: " &
Link_Waypoint_1
+
", London" & (" to: " + Link_Waypoint_2 + ", London") & (" to: " +
Link_Waypoint_3 + ", London") & (" to: " + Link_Waypoint_4 + ",
London") &
("
to: " + Link_Waypoint_5 + ", London")

But now, instead of a 'spreadsheet style table' I have a 'normal'
table
containing just the one (significant) field [Link_waypoint] containg
a
series
of records bound by a common [Run_No].

So I need to have the event code go through each field record and
parse it
to the string as a sequence of records based on a master record No
[Run_No]

can anybody help me with this problem. I figured that I would need
some
kind
of loop, but can't get my head around the code.

the example table setup is:

[Run_No] [Link_waypoint]
5 Main St
5 South St
5 Narrow St
5 Wide St
5 Chester St

 

[Guest]

Graham,

Yes I do have an autonumber field, but it works just fine for my needs. But
I do take your point about flexibility. But also bear in mind that these
fields are entered using a drop down combo, and are short lists. they are not
entered in the traditional typed manner, the idea being least user input the
better.

I also take your point about using it outside London. Funny you should say
that becuase this is exlusively for London (trust me on this...) and will
never be used elsewhere.


On another note though, I would like the browser to be inside a the database
and not as an external app. I tried to revert back to my original browser
setup (using code from another section) but it doesn;t work. How can I modify
your code call

Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q=" &
sRoute

to make it appear in a WebBrowser control on the form?

or can you tell me why this doesn't work:

Form.frm_Links!LinksMapBrowser.Navigate
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & sRoute

the error states:

Run_time error 2465 Application-defines or object defined error

regards

Eric

Hi Eric

Ah... the difficulties of time zones! You did all this work while I was
asleep - well done!

I suspect you have on your form a textbox named "Link_Waypoint_Pcode".
Therefore, when you use this expression:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") _
& rs![Link_waypoint] & ", " & "London, " & [Link_Waypoint_Pcode]

(note the absence if the "rs!") you are using the value in that /textbox/
every time around the loop, and therefore getting the same postcode repeated
each time.

When you use rs![Link_Waypoint_Pcode] you are referencing the /field/ named
[Link_Waypoint_Pcode] in your recordset, and that changes for each loop
iteration.

If you will allow me to suggest a few improvements:

1) Link_List_ID sounds suspiciously like an autonumber primary key. The
problem with this is that you have no control over its value, and therefore
the order of the waypoints. This means you will run into problems if you
(a) change the order of the waypoints in a run, or (b) wish to add a new
waypoint part way through a run. My suggestion was that you use a
non-unique numeric field named "Link_Waypoint_Order" (or similar) and number
the waypoints /within/ a run in the required order. Then you can change the
numbers when you want to change the order or insert a new waypoint. Note
that you should use this field in the "order by" clause when you open the
recordset.

2) It may not happen this year, but some time your database might be used
outside London. I suggest you add a "Link_Waypoint_City" field to your
table to give you that flexibility. If you wish, you can set its default
value to "London".

And a question to satisfy my curiosity: I thought the old "N1", "SW4"
postcodes were now obsolete in the UK. Does Google fully understand them,
and wouldn't you be better to use the new postcode format?

Best of luck to the lads for Saturday. I'm not sure who I want to win -
it's all too depressing! ;-)
--
Cheers,

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

sorry for the succesive posts, but they are really a timeline to how I
solved the problem (with your massive input...)

the key line seemed to be:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London, " & rs![Link_Waypoint_Pcode]

the previous attempt, kept giving me the same postcode for all addresses,
so
I added an additional 'rs!' before the last field request, as in:

& rs![Link_Waypoint_Pcode]

and this finally solved the entire problem!.

BUT... I don't know why? (I'm a complete novice at recordset stuff), can
you
explain why and how this made the difference, and maybe how this whole
code
actually works.

regards

Eric

Graham,

I have resolved the problem, but now when I try to incorporate the
postcode;
only the last postcode in the recordset is passed to googlemaps. the
postcode
field is: [Link_Waypoint_Pcode]


this is the result i get from googlemaps:

from: Liverpool Road, London NW6 to: Abbey Road, London NW6

from this table:

Link_List_ID Run_No Run_Link_ID Link_waypoint Link_Waypoint_Pcode
158 1 1 Liverpool Road N1
159 1 1 Abbey Road NW6


Here is my present code

******************************

Private Sub Command17_Click()
'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select Link_waypoint, Link_Waypoint_Pcode from
tbl_Link_Waypoints where [Run_No]=" & Me.Run_No & " order by
[Link_List_ID];", dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London " & [Link_Waypoint_Pcode]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

************************************

:

Congratulations! It's always good to see a new convert to the
righteous
path of normalisation

I think that you will need another field in your Waypoints table to
ensure
the waypoints are visited in the correct order. Let's call it
[WP_Seq].
Now your data will look like this:

[Run_No] [WP_Seq] [Link_waypoint]
5 1 Main St
5 2 South St
5 3 Narrow St
5 4 Wide St
5 5 Chester St

Now you need to open a recordset based on a SQL query, and loop through
the
records for a given [Run_No], appending each [Link_waypoint] to the
string.
Code will look something like this:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sRoute as String
Dim iWPCount as Integer
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"Select [Link_waypoint] from tblWaypoints where [Run_No]=" _
& lngRunNo & " order by [WP_Seq];", dbOpenForwardOnly)
Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf( iWPCount=1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
_
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If

This is "air code" (untested and written on the fly) so sorry if there
are
any typos or other errors in it. Note that I'm using
"Application.FollowHyperlink" instead of shelling iexplore. Easier and
more
reliable.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

PS: Grudging congrats to your rugby players ;-)

previously, I was able to do this by having seperate field names; for
example:

stAppName = "C:\Program Files\Internet Explorer\iexplore.exe
http://maps.google.co.uk/maps?f=q&hl=en&q= " & "from: " &
Link_Waypoint_1
+
", London" & (" to: " + Link_Waypoint_2 + ", London") & (" to: " +
Link_Waypoint_3 + ", London") & (" to: " + Link_Waypoint_4 + ",
London") &
("
to: " + Link_Waypoint_5 + ", London")

But now, instead of a 'spreadsheet style table' I have a 'normal'
table
containing just the one (significant) field [Link_waypoint] containg
a
series
of records bound by a common [Run_No].

So I need to have the event code go through each field record and
parse it
to the string as a sequence of records based on a master record No
[Run_No]

can anybody help me with this problem. I figured that I would need
some
kind
of loop, but can't get my head around the code.

the example table setup is:

[Run_No] [Link_waypoint]
5 Main St
5 South St
5 Narrow St
5 Wide St
5 Chester St

 

[Guest]

Graham,

On another note though, I would like the browser to be inside a the database
and not as an external app. I tried to revert back to my original browser
setup (using code from another section) but it doesn't work. How can I modify
your code call

Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q=" &
sRoute

to make it appear in a WebBrowser control on the form?

or can you tell me why this doesn't work:

Form.frm_Links!LinksMapBrowser.Navigate
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & sRoute

the error states:

Run_time error 2465 Application-defines or object defined error

regards

Eric

Hi Eric

Ah... the difficulties of time zones! You did all this work while I was
asleep - well done!

I suspect you have on your form a textbox named "Link_Waypoint_Pcode".
Therefore, when you use this expression:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") _
& rs![Link_waypoint] & ", " & "London, " & [Link_Waypoint_Pcode]

(note the absence if the "rs!") you are using the value in that /textbox/
every time around the loop, and therefore getting the same postcode repeated
each time.

When you use rs![Link_Waypoint_Pcode] you are referencing the /field/ named
[Link_Waypoint_Pcode] in your recordset, and that changes for each loop
iteration.

If you will allow me to suggest a few improvements:

1) Link_List_ID sounds suspiciously like an autonumber primary key. The
problem with this is that you have no control over its value, and therefore
the order of the waypoints. This means you will run into problems if you
(a) change the order of the waypoints in a run, or (b) wish to add a new
waypoint part way through a run. My suggestion was that you use a
non-unique numeric field named "Link_Waypoint_Order" (or similar) and number
the waypoints /within/ a run in the required order. Then you can change the
numbers when you want to change the order or insert a new waypoint. Note
that you should use this field in the "order by" clause when you open the
recordset.

2) It may not happen this year, but some time your database might be used
outside London. I suggest you add a "Link_Waypoint_City" field to your
table to give you that flexibility. If you wish, you can set its default
value to "London".

And a question to satisfy my curiosity: I thought the old "N1", "SW4"
postcodes were now obsolete in the UK. Does Google fully understand them,
and wouldn't you be better to use the new postcode format?

Best of luck to the lads for Saturday. I'm not sure who I want to win -
it's all too depressing! ;-)
--
Cheers,

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

sorry for the succesive posts, but they are really a timeline to how I
solved the problem (with your massive input...)

the key line seemed to be:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London, " & rs![Link_Waypoint_Pcode]

the previous attempt, kept giving me the same postcode for all addresses,
so
I added an additional 'rs!' before the last field request, as in:

& rs![Link_Waypoint_Pcode]

and this finally solved the entire problem!.

BUT... I don't know why? (I'm a complete novice at recordset stuff), can
you
explain why and how this made the difference, and maybe how this whole
code
actually works.

regards

Eric

Graham,

I have resolved the problem, but now when I try to incorporate the
postcode;
only the last postcode in the recordset is passed to googlemaps. the
postcode
field is: [Link_Waypoint_Pcode]


this is the result i get from googlemaps:

from: Liverpool Road, London NW6 to: Abbey Road, London NW6

from this table:

Link_List_ID Run_No Run_Link_ID Link_waypoint Link_Waypoint_Pcode
158 1 1 Liverpool Road N1
159 1 1 Abbey Road NW6


Here is my present code

******************************

Private Sub Command17_Click()
'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select Link_waypoint, Link_Waypoint_Pcode from
tbl_Link_Waypoints where [Run_No]=" & Me.Run_No & " order by
[Link_List_ID];", dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London " & [Link_Waypoint_Pcode]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

************************************

:

Congratulations! It's always good to see a new convert to the
righteous
path of normalisation

I think that you will need another field in your Waypoints table to
ensure
the waypoints are visited in the correct order. Let's call it
[WP_Seq].
Now your data will look like this:

[Run_No] [WP_Seq] [Link_waypoint]
5 1 Main St
5 2 South St
5 3 Narrow St
5 4 Wide St
5 5 Chester St

Now you need to open a recordset based on a SQL query, and loop through
the
records for a given [Run_No], appending each [Link_waypoint] to the
string.
Code will look something like this:

'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sRoute as String
Dim iWPCount as Integer
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"Select [Link_waypoint] from tblWaypoints where [Run_No]=" _
& lngRunNo & " order by [WP_Seq];", dbOpenForwardOnly)
Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf( iWPCount=1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
_
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If

This is "air code" (untested and written on the fly) so sorry if there
are
any typos or other errors in it. Note that I'm using
"Application.FollowHyperlink" instead of shelling iexplore. Easier and
more
reliable.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

PS: Grudging congrats to your rugby players ;-)

previously, I was able to do this by having seperate field names; for
example:

stAppName = "C:\Program Files\Internet Explorer\iexplore.exe
http://maps.google.co.uk/maps?f=q&hl=en&q= " & "from: " &
Link_Waypoint_1
+
", London" & (" to: " + Link_Waypoint_2 + ", London") & (" to: " +
Link_Waypoint_3 + ", London") & (" to: " + Link_Waypoint_4 + ",
London") &
("
to: " + Link_Waypoint_5 + ", London")

But now, instead of a 'spreadsheet style table' I have a 'normal'
table
containing just the one (significant) field [Link_waypoint] containg
a
series
of records bound by a common [Run_No].

So I need to have the event code go through each field record and
parse it
to the string as a sequence of records based on a master record No
[Run_No]

can anybody help me with this problem. I figured that I would need
some
kind
of loop, but can't get my head around the code.

the example table setup is:

[Run_No] [Link_waypoint]
5 Main St
5 South St
5 Narrow St
5 Wide St
5 Chester St

 

[Graham Mandeno]

Hi Eric

Of course, it's up to you whether or not you use an autonumber field to
ensure you visit your waypoints in the correct order, but I would
/certainly/ not do so!

On the other question, the problem is that you are referring to "Form" (the
form object in the current context) instead of "Forms" (the global
collection of all open forms).

Is the browser control on the same form that contains your "Command17"
command button? (I would rename the button, by the way, to cmdShowRunMap,
or some such) If it is, then you don't need to reference the form, because
the web control is in the local scope, so try:

LinksMapBrowser.Navigate ...

If it is on another form, then use "Forms!" instead of "Form."

Forms!frm_Links!LinksMapBrowser.Navigate ...

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Yes I do have an autonumber field, but it works just fine for my needs.
But
I do take your point about flexibility. But also bear in mind that these
fields are entered using a drop down combo, and are short lists. they are
not
entered in the traditional typed manner, the idea being least user input
the
better.

I also take your point about using it outside London. Funny you should say
that becuase this is exlusively for London (trust me on this...) and will
never be used elsewhere.


On another note though, I would like the browser to be inside a the
database
and not as an external app. I tried to revert back to my original browser
setup (using code from another section) but it doesn;t work. How can I
modify
your code call

Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q=" &
sRoute

to make it appear in a WebBrowser control on the form?

or can you tell me why this doesn't work:

Form.frm_Links!LinksMapBrowser.Navigate
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & sRoute

the error states:

Run_time error 2465 Application-defines or object defined error

regards

Eric

Hi Eric

Ah... the difficulties of time zones! You did all this work while I was
asleep - well done!

I suspect you have on your form a textbox named "Link_Waypoint_Pcode".
Therefore, when you use this expression:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") _
& rs![Link_waypoint] & ", " & "London, " & [Link_Waypoint_Pcode]

(note the absence if the "rs!") you are using the value in that /textbox/
every time around the loop, and therefore getting the same postcode
repeated
each time.

When you use rs![Link_Waypoint_Pcode] you are referencing the /field/
named
[Link_Waypoint_Pcode] in your recordset, and that changes for each loop
iteration.

If you will allow me to suggest a few improvements:

1) Link_List_ID sounds suspiciously like an autonumber primary key. The
problem with this is that you have no control over its value, and
therefore
the order of the waypoints. This means you will run into problems if you
(a) change the order of the waypoints in a run, or (b) wish to add a new
waypoint part way through a run. My suggestion was that you use a
non-unique numeric field named "Link_Waypoint_Order" (or similar) and
number
the waypoints /within/ a run in the required order. Then you can change
the
numbers when you want to change the order or insert a new waypoint. Note
that you should use this field in the "order by" clause when you open the
recordset.

2) It may not happen this year, but some time your database might be used
outside London. I suggest you add a "Link_Waypoint_City" field to your
table to give you that flexibility. If you wish, you can set its default
value to "London".

And a question to satisfy my curiosity: I thought the old "N1", "SW4"
postcodes were now obsolete in the UK. Does Google fully understand
them,
and wouldn't you be better to use the new postcode format?

Best of luck to the lads for Saturday. I'm not sure who I want to win -
it's all too depressing! ;-)
--
Cheers,

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

sorry for the succesive posts, but they are really a timeline to how I
solved the problem (with your massive input...)

the key line seemed to be:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London, " & rs![Link_Waypoint_Pcode]

the previous attempt, kept giving me the same postcode for all
addresses,
so
I added an additional 'rs!' before the last field request, as in:

& rs![Link_Waypoint_Pcode]

and this finally solved the entire problem!.

BUT... I don't know why? (I'm a complete novice at recordset stuff),
can
you
explain why and how this made the difference, and maybe how this whole
code
actually works.

regards

Eric



:

Graham,

I have resolved the problem, but now when I try to incorporate the
postcode;
only the last postcode in the recordset is passed to googlemaps. the
postcode
field is: [Link_Waypoint_Pcode]


this is the result i get from googlemaps:

from: Liverpool Road, London NW6 to: Abbey Road, London NW6

from this table:

Link_List_ID Run_No Run_Link_ID Link_waypoint Link_Waypoint_Pcode
158 1 1 Liverpool Road N1
159 1 1 Abbey Road NW6


Here is my present code

******************************

Private Sub Command17_Click()
'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select Link_waypoint, Link_Waypoint_Pcode
from
tbl_Link_Waypoints where [Run_No]=" & Me.Run_No & " order by
[Link_List_ID];", dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London " & [Link_Waypoint_Pcode]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

************************************

:

Congratulations! It's always good to see a new convert to the
righteous
path of normalisation

I think that you will need another field in your Waypoints table to
ensure
the waypoints are visited in the correct order. Let's call it
[WP_Seq].
Now your data will look like this:

[Run_No] [WP_Seq] [Link_waypoint]
5 1 Main St
5 2 South St
5 3 Narrow St
5 4 Wide St
5 5 Chester St

Now you need to open a recordset based on a SQL query, and loop
through
the
records for a given [Run_No], appending each [Link_waypoint] to the
string.
Code will look something like this:

'Assume lngRunNo is either declared elsewhere or passed as an
argument
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sRoute as String
Dim iWPCount as Integer
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"Select [Link_waypoint] from tblWaypoints where [Run_No]=" _
& lngRunNo & " order by [WP_Seq];", dbOpenForwardOnly)
Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf( iWPCount=1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
_
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If

This is "air code" (untested and written on the fly) so sorry if
there
are
any typos or other errors in it. Note that I'm using
"Application.FollowHyperlink" instead of shelling iexplore. Easier
and
more
reliable.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

PS: Grudging congrats to your rugby players ;-)

previously, I was able to do this by having seperate field names;
for
example:

stAppName = "C:\Program Files\Internet Explorer\iexplore.exe
http://maps.google.co.uk/maps?f=q&hl=en&q= " & "from: " &
Link_Waypoint_1
+
", London" & (" to: " + Link_Waypoint_2 + ", London") & (" to: " +
Link_Waypoint_3 + ", London") & (" to: " + Link_Waypoint_4 + ",
London") &
("
to: " + Link_Waypoint_5 + ", London")

But now, instead of a 'spreadsheet style table' I have a 'normal'
table
containing just the one (significant) field [Link_waypoint]
containg
a
series
of records bound by a common [Run_No].

So I need to have the event code go through each field record and
parse it
to the string as a sequence of records based on a master record No
[Run_No]

can anybody help me with this problem. I figured that I would need
some
kind
of loop, but can't get my head around the code.

the example table setup is:

[Run_No] [Link_waypoint]
5 Main St
5 South St
5 Narrow St
5 Wide St
5 Chester St

 

[Guest]

Graham,

Not quite on the same form...

The button is on the continous form that contains the address combo boxes
which is called: frm_Link_Waypoints

and the WebBrowser is on the parent form which is called: frm_Links

to summarise: frm_Links contains a subform called frm_Link_Waypoints

I tried this line:

If iWPCount >= 2 Then LinksMapBrowser.Navigate
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & sRoute


but I got this error message

compile error
Variable not defined

I placed the webrowser on the same for as the button (and data), and it
worked. But I need it on the parent form otherwise it looks a complete mess
and ruins the continous form layout.

Hi Eric

Of course, it's up to you whether or not you use an autonumber field to
ensure you visit your waypoints in the correct order, but I would
/certainly/ not do so!

On the other question, the problem is that you are referring to "Form" (the
form object in the current context) instead of "Forms" (the global
collection of all open forms).

Is the browser control on the same form that contains your "Command17"
command button? (I would rename the button, by the way, to cmdShowRunMap,
or some such) If it is, then you don't need to reference the form, because
the web control is in the local scope, so try:

LinksMapBrowser.Navigate ...

If it is on another form, then use "Forms!" instead of "Form."

Forms!frm_Links!LinksMapBrowser.Navigate ...

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Yes I do have an autonumber field, but it works just fine for my needs.
But
I do take your point about flexibility. But also bear in mind that these
fields are entered using a drop down combo, and are short lists. they are
not
entered in the traditional typed manner, the idea being least user input
the
better.

I also take your point about using it outside London. Funny you should say
that becuase this is exlusively for London (trust me on this...) and will
never be used elsewhere.


On another note though, I would like the browser to be inside a the
database
and not as an external app. I tried to revert back to my original browser
setup (using code from another section) but it doesn;t work. How can I
modify
your code call

Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q=" &
sRoute

to make it appear in a WebBrowser control on the form?

or can you tell me why this doesn't work:

Form.frm_Links!LinksMapBrowser.Navigate
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & sRoute

the error states:

Run_time error 2465 Application-defines or object defined error

regards

Eric

Hi Eric

Ah... the difficulties of time zones! You did all this work while I was
asleep - well done!

I suspect you have on your form a textbox named "Link_Waypoint_Pcode".
Therefore, when you use this expression:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") _
& rs![Link_waypoint] & ", " & "London, " & [Link_Waypoint_Pcode]

(note the absence if the "rs!") you are using the value in that /textbox/
every time around the loop, and therefore getting the same postcode
repeated
each time.

When you use rs![Link_Waypoint_Pcode] you are referencing the /field/
named
[Link_Waypoint_Pcode] in your recordset, and that changes for each loop
iteration.

If you will allow me to suggest a few improvements:

1) Link_List_ID sounds suspiciously like an autonumber primary key. The
problem with this is that you have no control over its value, and
therefore
the order of the waypoints. This means you will run into problems if you
(a) change the order of the waypoints in a run, or (b) wish to add a new
waypoint part way through a run. My suggestion was that you use a
non-unique numeric field named "Link_Waypoint_Order" (or similar) and
number
the waypoints /within/ a run in the required order. Then you can change
the
numbers when you want to change the order or insert a new waypoint. Note
that you should use this field in the "order by" clause when you open the
recordset.

2) It may not happen this year, but some time your database might be used
outside London. I suggest you add a "Link_Waypoint_City" field to your
table to give you that flexibility. If you wish, you can set its default
value to "London".

And a question to satisfy my curiosity: I thought the old "N1", "SW4"
postcodes were now obsolete in the UK. Does Google fully understand
them,
and wouldn't you be better to use the new postcode format?

Best of luck to the lads for Saturday. I'm not sure who I want to win -
it's all too depressing! ;-)
--
Cheers,

Graham Mandeno [Access MVP]
Auckland, New Zealand



Graham,

sorry for the succesive posts, but they are really a timeline to how I
solved the problem (with your massive input...)

the key line seemed to be:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London, " & rs![Link_Waypoint_Pcode]

the previous attempt, kept giving me the same postcode for all
addresses,
so
I added an additional 'rs!' before the last field request, as in:

& rs![Link_Waypoint_Pcode]

and this finally solved the entire problem!.

BUT... I don't know why? (I'm a complete novice at recordset stuff),
can
you
explain why and how this made the difference, and maybe how this whole
code
actually works.

regards

Eric



:

Graham,

I have resolved the problem, but now when I try to incorporate the
postcode;
only the last postcode in the recordset is passed to googlemaps. the
postcode
field is: [Link_Waypoint_Pcode]


this is the result i get from googlemaps:

from: Liverpool Road, London NW6 to: Abbey Road, London NW6

from this table:

Link_List_ID Run_No Run_Link_ID Link_waypoint Link_Waypoint_Pcode
158 1 1 Liverpool Road N1
159 1 1 Abbey Road NW6


Here is my present code

******************************

Private Sub Command17_Click()
'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select Link_waypoint, Link_Waypoint_Pcode
from
tbl_Link_Waypoints where [Run_No]=" & Me.Run_No & " order by
[Link_List_ID];", dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London " & [Link_Waypoint_Pcode]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

************************************

:

Congratulations! It's always good to see a new convert to the
righteous
path of normalisation

I think that you will need another field in your Waypoints table to
ensure
the waypoints are visited in the correct order. Let's call it
[WP_Seq].
Now your data will look like this:

[Run_No] [WP_Seq] [Link_waypoint]
5 1 Main St
5 2 South St
5 3 Narrow St
5 4 Wide St
5 5 Chester St

Now you need to open a recordset based on a SQL query, and loop
through
the
records for a given [Run_No], appending each [Link_waypoint] to the
string.
Code will look something like this:

'Assume lngRunNo is either declared elsewhere or passed as an
argument
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sRoute as String
Dim iWPCount as Integer
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"Select [Link_waypoint] from tblWaypoints where [Run_No]=" _
& lngRunNo & " order by [WP_Seq];", dbOpenForwardOnly)
Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf( iWPCount=1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
_
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If

This is "air code" (untested and written on the fly) so sorry if
there
are
any typos or other errors in it. Note that I'm using
"Application.FollowHyperlink" instead of shelling iexplore. Easier
and
more
reliable.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

PS: Grudging congrats to your rugby players ;-)

previously, I was able to do this by having seperate field names;
for
example:

stAppName = "C:\Program Files\Internet Explorer\iexplore.exe
http://maps.google.co.uk/maps?f=q&hl=en&q= " & "from: " &
Link_Waypoint_1
+
", London" & (" to: " + Link_Waypoint_2 + ", London") & (" to: " +

 

[Guest]

Graham,

I tried your solution (see previous post), but it failed, but your comments
were very helpful in steering me in the right direction, so I had a look
around the forum and made this modification, which worked!.

If iWPCount >= 2 Then

Parent.Form.LinksMapBrowser.Navigate
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & sRoute

the problem I have is that i did some learning on previous versions of
access, and as time as passed some of the syntax has made things simpler, but
confusing because I haven't been able to keep pace with the changes; things
like 'Me. & Parent. type stuff where I was always used to using the builder
app to construct long formal syntax Forms!form.[blah].[blah]...

anyway, I'm smiling greatly now, so...

Thanks so much for your help on this, gratefully appreciated.

regards

Eric

Hi Eric

Of course, it's up to you whether or not you use an autonumber field to
ensure you visit your waypoints in the correct order, but I would
/certainly/ not do so!

On the other question, the problem is that you are referring to "Form" (the
form object in the current context) instead of "Forms" (the global
collection of all open forms).

Is the browser control on the same form that contains your "Command17"
command button? (I would rename the button, by the way, to cmdShowRunMap,
or some such) If it is, then you don't need to reference the form, because
the web control is in the local scope, so try:

LinksMapBrowser.Navigate ...

If it is on another form, then use "Forms!" instead of "Form."

Forms!frm_Links!LinksMapBrowser.Navigate ...

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Yes I do have an autonumber field, but it works just fine for my needs.
But
I do take your point about flexibility. But also bear in mind that these
fields are entered using a drop down combo, and are short lists. they are
not
entered in the traditional typed manner, the idea being least user input
the
better.

I also take your point about using it outside London. Funny you should say
that becuase this is exlusively for London (trust me on this...) and will
never be used elsewhere.


On another note though, I would like the browser to be inside a the
database
and not as an external app. I tried to revert back to my original browser
setup (using code from another section) but it doesn;t work. How can I
modify
your code call

Application.FollowHyperlink "http://maps.google.co.uk/maps?f=q&hl=en&q=" &
sRoute

to make it appear in a WebBrowser control on the form?

or can you tell me why this doesn't work:

Form.frm_Links!LinksMapBrowser.Navigate
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & sRoute

the error states:

Run_time error 2465 Application-defines or object defined error

regards

Eric

Hi Eric

Ah... the difficulties of time zones! You did all this work while I was
asleep - well done!

I suspect you have on your form a textbox named "Link_Waypoint_Pcode".
Therefore, when you use this expression:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") _
& rs![Link_waypoint] & ", " & "London, " & [Link_Waypoint_Pcode]

(note the absence if the "rs!") you are using the value in that /textbox/
every time around the loop, and therefore getting the same postcode
repeated
each time.

When you use rs![Link_Waypoint_Pcode] you are referencing the /field/
named
[Link_Waypoint_Pcode] in your recordset, and that changes for each loop
iteration.

If you will allow me to suggest a few improvements:

1) Link_List_ID sounds suspiciously like an autonumber primary key. The
problem with this is that you have no control over its value, and
therefore
the order of the waypoints. This means you will run into problems if you
(a) change the order of the waypoints in a run, or (b) wish to add a new
waypoint part way through a run. My suggestion was that you use a
non-unique numeric field named "Link_Waypoint_Order" (or similar) and
number
the waypoints /within/ a run in the required order. Then you can change
the
numbers when you want to change the order or insert a new waypoint. Note
that you should use this field in the "order by" clause when you open the
recordset.

2) It may not happen this year, but some time your database might be used
outside London. I suggest you add a "Link_Waypoint_City" field to your
table to give you that flexibility. If you wish, you can set its default
value to "London".

And a question to satisfy my curiosity: I thought the old "N1", "SW4"
postcodes were now obsolete in the UK. Does Google fully understand
them,
and wouldn't you be better to use the new postcode format?

Best of luck to the lads for Saturday. I'm not sure who I want to win -
it's all too depressing! ;-)
--
Cheers,

Graham Mandeno [Access MVP]
Auckland, New Zealand



Graham,

sorry for the succesive posts, but they are really a timeline to how I
solved the problem (with your massive input...)

the key line seemed to be:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London, " & rs![Link_Waypoint_Pcode]

the previous attempt, kept giving me the same postcode for all
addresses,
so
I added an additional 'rs!' before the last field request, as in:

& rs![Link_Waypoint_Pcode]

and this finally solved the entire problem!.

BUT... I don't know why? (I'm a complete novice at recordset stuff),
can
you
explain why and how this made the difference, and maybe how this whole
code
actually works.

regards

Eric



:

Graham,

I have resolved the problem, but now when I try to incorporate the
postcode;
only the last postcode in the recordset is passed to googlemaps. the
postcode
field is: [Link_Waypoint_Pcode]


this is the result i get from googlemaps:

from: Liverpool Road, London NW6 to: Abbey Road, London NW6

from this table:

Link_List_ID Run_No Run_Link_ID Link_waypoint Link_Waypoint_Pcode
158 1 1 Liverpool Road N1
159 1 1 Abbey Road NW6


Here is my present code

******************************

Private Sub Command17_Click()
'Assume lngRunNo is either declared elsewhere or passed as an argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select Link_waypoint, Link_Waypoint_Pcode
from
tbl_Link_Waypoints where [Run_No]=" & Me.Run_No & " order by
[Link_List_ID];", dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London " & [Link_Waypoint_Pcode]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

************************************

:

Congratulations! It's always good to see a new convert to the
righteous
path of normalisation

I think that you will need another field in your Waypoints table to
ensure
the waypoints are visited in the correct order. Let's call it
[WP_Seq].
Now your data will look like this:

[Run_No] [WP_Seq] [Link_waypoint]
5 1 Main St
5 2 South St
5 3 Narrow St
5 4 Wide St
5 5 Chester St

Now you need to open a recordset based on a SQL query, and loop
through
the
records for a given [Run_No], appending each [Link_waypoint] to the
string.
Code will look something like this:

'Assume lngRunNo is either declared elsewhere or passed as an
argument
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sRoute as String
Dim iWPCount as Integer
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"Select [Link_waypoint] from tblWaypoints where [Run_No]=" _
& lngRunNo & " order by [WP_Seq];", dbOpenForwardOnly)
Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf( iWPCount=1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
_
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If

This is "air code" (untested and written on the fly) so sorry if
there
are
any typos or other errors in it. Note that I'm using
"Application.FollowHyperlink" instead of shelling iexplore. Easier
and
more
reliable.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

PS: Grudging congrats to your rugby players ;-)

previously, I was able to do this by having seperate field names;
for
example:

stAppName = "C:\Program Files\Internet Explorer\iexplore.exe
http://maps.google.co.uk/maps?f=q&hl=en&q= " & "from: " &
Link_Waypoint_1
+
", London" & (" to: " + Link_Waypoint_2 + ", London") & (" to: " +

 

[Graham Mandeno]

Hi Eric

I'm glad you've got all working.

Note in my last reply that I said the problem was the absence of the "s" on
"Forms". That alone would have fixed it, but when you are referring to a
parent form from a subform it is certainly preferable to use Me.Parent
instead of Forms![ParentFormName]. The expression builder often constructs
very clunky expressions that are far more complex than they need to be. I
never touch it myself, but I can see how useful it would be to a newbie!

Hope you're still smiling after Saturday ;-)
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I tried your solution (see previous post), but it failed, but your
comments
were very helpful in steering me in the right direction, so I had a look
around the forum and made this modification, which worked!.

If iWPCount >= 2 Then

Parent.Form.LinksMapBrowser.Navigate
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & sRoute

the problem I have is that i did some learning on previous versions of
access, and as time as passed some of the syntax has made things simpler,
but
confusing because I haven't been able to keep pace with the changes;
things
like 'Me. & Parent. type stuff where I was always used to using the
builder
app to construct long formal syntax Forms!form.[blah].[blah]...

anyway, I'm smiling greatly now, so...

Thanks so much for your help on this, gratefully appreciated.

regards

Eric

Hi Eric

Of course, it's up to you whether or not you use an autonumber field to
ensure you visit your waypoints in the correct order, but I would
/certainly/ not do so!

On the other question, the problem is that you are referring to "Form"
(the
form object in the current context) instead of "Forms" (the global
collection of all open forms).

Is the browser control on the same form that contains your "Command17"
command button? (I would rename the button, by the way, to
cmdShowRunMap,
or some such) If it is, then you don't need to reference the form,
because
the web control is in the local scope, so try:

LinksMapBrowser.Navigate ...

If it is on another form, then use "Forms!" instead of "Form."

Forms!frm_Links!LinksMapBrowser.Navigate ...

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Yes I do have an autonumber field, but it works just fine for my needs.
But
I do take your point about flexibility. But also bear in mind that
these
fields are entered using a drop down combo, and are short lists. they
are
not
entered in the traditional typed manner, the idea being least user
input
the
better.

I also take your point about using it outside London. Funny you should
say
that becuase this is exlusively for London (trust me on this...) and
will
never be used elsewhere.


On another note though, I would like the browser to be inside a the
database
and not as an external app. I tried to revert back to my original
browser
setup (using code from another section) but it doesn;t work. How can I
modify
your code call

Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q=" &
sRoute

to make it appear in a WebBrowser control on the form?

or can you tell me why this doesn't work:

Form.frm_Links!LinksMapBrowser.Navigate
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & sRoute

the error states:

Run_time error 2465 Application-defines or object defined error

regards

Eric


:

Hi Eric

Ah... the difficulties of time zones! You did all this work while I
was
asleep - well done!

I suspect you have on your form a textbox named "Link_Waypoint_Pcode".
Therefore, when you use this expression:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") _
& rs![Link_waypoint] & ", " & "London, " &
[Link_Waypoint_Pcode]

(note the absence if the "rs!") you are using the value in that
/textbox/
every time around the loop, and therefore getting the same postcode
repeated
each time.

When you use rs![Link_Waypoint_Pcode] you are referencing the /field/
named
[Link_Waypoint_Pcode] in your recordset, and that changes for each
loop
iteration.

If you will allow me to suggest a few improvements:

1) Link_List_ID sounds suspiciously like an autonumber primary key.
The
problem with this is that you have no control over its value, and
therefore
the order of the waypoints. This means you will run into problems if
you
(a) change the order of the waypoints in a run, or (b) wish to add a
new
waypoint part way through a run. My suggestion was that you use a
non-unique numeric field named "Link_Waypoint_Order" (or similar) and
number
the waypoints /within/ a run in the required order. Then you can
change
the
numbers when you want to change the order or insert a new waypoint.
Note
that you should use this field in the "order by" clause when you open
the
recordset.

2) It may not happen this year, but some time your database might be
used
outside London. I suggest you add a "Link_Waypoint_City" field to
your
table to give you that flexibility. If you wish, you can set its
default
value to "London".

And a question to satisfy my curiosity: I thought the old "N1", "SW4"
postcodes were now obsolete in the UK. Does Google fully understand
them,
and wouldn't you be better to use the new postcode format?

Best of luck to the lads for Saturday. I'm not sure who I want to
win -
it's all too depressing! ;-)
--
Cheers,

Graham Mandeno [Access MVP]
Auckland, New Zealand



Graham,

sorry for the succesive posts, but they are really a timeline to how
I
solved the problem (with your massive input...)

the key line seemed to be:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London, " & rs![Link_Waypoint_Pcode]

the previous attempt, kept giving me the same postcode for all
addresses,
so
I added an additional 'rs!' before the last field request, as in:

& rs![Link_Waypoint_Pcode]

and this finally solved the entire problem!.

BUT... I don't know why? (I'm a complete novice at recordset stuff),
can
you
explain why and how this made the difference, and maybe how this
whole
code
actually works.

regards

Eric



:

Graham,

I have resolved the problem, but now when I try to incorporate the
postcode;
only the last postcode in the recordset is passed to googlemaps.
the
postcode
field is: [Link_Waypoint_Pcode]


this is the result i get from googlemaps:

from: Liverpool Road, London NW6 to: Abbey Road, London NW6

from this table:

Link_List_ID Run_No Run_Link_ID Link_waypoint Link_Waypoint_Pcode
158 1 1 Liverpool Road N1
159 1 1 Abbey Road NW6


Here is my present code

******************************

Private Sub Command17_Click()
'Assume lngRunNo is either declared elsewhere or passed as an
argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select Link_waypoint,
Link_Waypoint_Pcode
from
tbl_Link_Waypoints where [Run_No]=" & Me.Run_No & " order by
[Link_List_ID];", dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London " & [Link_Waypoint_Pcode]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

************************************

:

Congratulations! It's always good to see a new convert to the
righteous
path of normalisation

I think that you will need another field in your Waypoints table
to
ensure
the waypoints are visited in the correct order. Let's call it
[WP_Seq].
Now your data will look like this:

[Run_No] [WP_Seq] [Link_waypoint]
5 1 Main St
5 2 South St
5 3 Narrow St
5 4 Wide St
5 5 Chester St

Now you need to open a recordset based on a SQL query, and loop
through
the
records for a given [Run_No], appending each [Link_waypoint] to
the
string.
Code will look something like this:

'Assume lngRunNo is either declared elsewhere or passed as an
argument
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sRoute as String
Dim iWPCount as Integer
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"Select [Link_waypoint] from tblWaypoints where [Run_No]=" _
& lngRunNo & " order by [WP_Seq];", dbOpenForwardOnly)
Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf( iWPCount=1, "from: ", " to: ") &
rs![Link_waypoint]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
_
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If

This is "air code" (untested and written on the fly) so sorry if
there
are
any typos or other errors in it. Note that I'm using
"Application.FollowHyperlink" instead of shelling iexplore.
Easier
and
more
reliable.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

PS: Grudging congrats to your rugby players ;-)

message
previously, I was able to do this by having seperate field
names;
for
example:

stAppName = "C:\Program Files\Internet Explorer\iexplore.exe
http://maps.google.co.uk/maps?f=q&hl=en&q= " & "from: " &
Link_Waypoint_1
+
", London" & (" to: " + Link_Waypoint_2 + ", London") & (" to:
" +

 

[Guest]

Hi Graham,

do you sknow much about functions?. I Shave a different problem for the same
Application (I have posted it on the forum but have had no replies, so
thought I would try my luck with you).


The problem:

I don't know how to implement a function. I have a table with Lat/Long
coordinates for a number of locations and want to use the function to compare
one master Lat/Long against the list of other addresses and get it to deliver
any address within a 3 mile radius.


this following function from microsoft uses the Great Arc Circle which will
return the shorted distance between two points on the globe


Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As Double, Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 - Z2) *
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then
GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function

Hi Eric

I'm glad you've got all working.

Note in my last reply that I said the problem was the absence of the "s" on
"Forms". That alone would have fixed it, but when you are referring to a
parent form from a subform it is certainly preferable to use Me.Parent
instead of Forms![ParentFormName]. The expression builder often constructs
very clunky expressions that are far more complex than they need to be. I
never touch it myself, but I can see how useful it would be to a newbie!

Hope you're still smiling after Saturday ;-)
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I tried your solution (see previous post), but it failed, but your
comments
were very helpful in steering me in the right direction, so I had a look
around the forum and made this modification, which worked!.

If iWPCount >= 2 Then

Parent.Form.LinksMapBrowser.Navigate
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & sRoute

the problem I have is that i did some learning on previous versions of
access, and as time as passed some of the syntax has made things simpler,
but
confusing because I haven't been able to keep pace with the changes;
things
like 'Me. & Parent. type stuff where I was always used to using the
builder
app to construct long formal syntax Forms!form.[blah].[blah]...

anyway, I'm smiling greatly now, so...

Thanks so much for your help on this, gratefully appreciated.

regards

Eric

Hi Eric

Of course, it's up to you whether or not you use an autonumber field to
ensure you visit your waypoints in the correct order, but I would
/certainly/ not do so!

On the other question, the problem is that you are referring to "Form"
(the
form object in the current context) instead of "Forms" (the global
collection of all open forms).

Is the browser control on the same form that contains your "Command17"
command button? (I would rename the button, by the way, to
cmdShowRunMap,
or some such) If it is, then you don't need to reference the form,
because
the web control is in the local scope, so try:

LinksMapBrowser.Navigate ...

If it is on another form, then use "Forms!" instead of "Form."

Forms!frm_Links!LinksMapBrowser.Navigate ...

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Graham,

Yes I do have an autonumber field, but it works just fine for my needs.
But
I do take your point about flexibility. But also bear in mind that
these
fields are entered using a drop down combo, and are short lists. they
are
not
entered in the traditional typed manner, the idea being least user
input
the
better.

I also take your point about using it outside London. Funny you should
say
that becuase this is exlusively for London (trust me on this...) and
will
never be used elsewhere.


On another note though, I would like the browser to be inside a the
database
and not as an external app. I tried to revert back to my original
browser
setup (using code from another section) but it doesn;t work. How can I
modify
your code call

Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q=" &
sRoute

to make it appear in a WebBrowser control on the form?

or can you tell me why this doesn't work:

Form.frm_Links!LinksMapBrowser.Navigate
"http://maps.google.co.uk/maps?f=q&hl=en&q=" & sRoute

the error states:

Run_time error 2465 Application-defines or object defined error

regards

Eric


:

Hi Eric

Ah... the difficulties of time zones! You did all this work while I
was
asleep - well done!

I suspect you have on your form a textbox named "Link_Waypoint_Pcode".
Therefore, when you use this expression:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") _
& rs![Link_waypoint] & ", " & "London, " &
[Link_Waypoint_Pcode]

(note the absence if the "rs!") you are using the value in that
/textbox/
every time around the loop, and therefore getting the same postcode
repeated
each time.

When you use rs![Link_Waypoint_Pcode] you are referencing the /field/
named
[Link_Waypoint_Pcode] in your recordset, and that changes for each
loop
iteration.

If you will allow me to suggest a few improvements:

1) Link_List_ID sounds suspiciously like an autonumber primary key.
The
problem with this is that you have no control over its value, and
therefore
the order of the waypoints. This means you will run into problems if
you
(a) change the order of the waypoints in a run, or (b) wish to add a
new
waypoint part way through a run. My suggestion was that you use a
non-unique numeric field named "Link_Waypoint_Order" (or similar) and
number
the waypoints /within/ a run in the required order. Then you can
change
the
numbers when you want to change the order or insert a new waypoint.
Note
that you should use this field in the "order by" clause when you open
the
recordset.

2) It may not happen this year, but some time your database might be
used
outside London. I suggest you add a "Link_Waypoint_City" field to
your
table to give you that flexibility. If you wish, you can set its
default
value to "London".

And a question to satisfy my curiosity: I thought the old "N1", "SW4"
postcodes were now obsolete in the UK. Does Google fully understand
them,
and wouldn't you be better to use the new postcode format?

Best of luck to the lads for Saturday. I'm not sure who I want to
win -
it's all too depressing! ;-)
--
Cheers,

Graham Mandeno [Access MVP]
Auckland, New Zealand



Graham,

sorry for the succesive posts, but they are really a timeline to how
I
solved the problem (with your massive input...)

the key line seemed to be:

sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London, " & rs![Link_Waypoint_Pcode]

the previous attempt, kept giving me the same postcode for all
addresses,
so
I added an additional 'rs!' before the last field request, as in:

& rs![Link_Waypoint_Pcode]

and this finally solved the entire problem!.

BUT... I don't know why? (I'm a complete novice at recordset stuff),
can
you
explain why and how this made the difference, and maybe how this
whole
code
actually works.

regards

Eric



:

Graham,

I have resolved the problem, but now when I try to incorporate the
postcode;
only the last postcode in the recordset is passed to googlemaps.
the
postcode
field is: [Link_Waypoint_Pcode]


this is the result i get from googlemaps:

from: Liverpool Road, London NW6 to: Abbey Road, London NW6

from this table:

Link_List_ID Run_No Run_Link_ID Link_waypoint Link_Waypoint_Pcode
158 1 1 Liverpool Road N1
159 1 1 Abbey Road NW6


Here is my present code

******************************

Private Sub Command17_Click()
'Assume lngRunNo is either declared elsewhere or passed as an
argument
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select Link_waypoint,
Link_Waypoint_Pcode
from
tbl_Link_Waypoints where [Run_No]=" & Me.Run_No & " order by
[Link_List_ID];", dbOpenForwardOnly)

Do Until rs.EOF
iWPCount = iWPCount + 1
sRoute = sRoute & IIf(iWPCount = 1, "from: ", " to: ") &
rs![Link_waypoint] & ", " & "London " & [Link_Waypoint_Pcode]
rs.MoveNext
Loop
rs.Close
If iWPCount >= 2 Then
Application.FollowHyperlink
"http://maps.google.co.uk/maps?f=q&hl=en&q="
& sRoute
Else
MsgBox "Run must have at least two waypoints"
End If
End Sub

************************************