Linking from a mailing address in the database to maps.google.com

[Guest]

I am trying to place a control on one of my forms that links automatically to
maps.google.com (or any driving directions web site for that matter) by
taking the street address, city, state, and/or zip in the database and
building that into the web address. For example,

http://maps.google.com/maps?q=jfk+to+350+5th+ave,+new+york&spn=0.121913,0.233854&hl=en

gives directions from JFK airport (q=jfk) to 350 5th Ave, New York, NY
10118. In my database, the street address is all in one field [address] and
would contain "350 5th Ave". SOOO....

1) How do I replace the space between the street number and the multiple
words in the street name with a "+" or a "%20" as appropriate?

2) Can the whole thing be done with an inserted hyperlink on the form, or
do I need to do something else?

 

[Ron Weiner]

This is a VB solution, but at a glance looks like it will be a direct port
to Access VBA.

http://www.vb-helper.com/howto_url_encode_string.html

Ron W
www.WorksRite.com

 

[Guest]

Ron,
Definately a setp in the right direction. I think I can get URLEncode to
look at all four fields as one by writing it like:
URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " " &[stZip])

and assuming I can assign it to a string like this:

stFullAddress = URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " "
&[stZip])

how can I plug stFullAddress into a line of code directing it to open a URL
which doesn't take stFullAddress literally, but understands that it's a
variable that means something else?

This is a VB solution, but at a glance looks like it will be a direct port
to Access VBA.

http://www.vb-helper.com/howto_url_encode_string.html

Ron W
www.WorksRite.com

I am trying to place a control on one of my forms that links automatically to
maps.google.com (or any driving directions web site for that matter) by
taking the street address, city, state, and/or zip in the database and
building that into the web address. For example,

http://maps.google.com/maps?q=jfk+to+350+5th+ave,+new+york&spn=0.121913,0.233854&hl=en

gives directions from JFK airport (q=jfk) to 350 5th Ave, New York, NY
10118. In my database, the street address is all in one field [address] and
would contain "350 5th Ave". SOOO....

1) How do I replace the space between the street number and the multiple
words in the street name with a "+" or a "%20" as appropriate?

2) Can the whole thing be done with an inserted hyperlink on the form, or
do I need to do something else?

 

[Ron Weiner]

I am not sure that I understand your question. If you want to open the
users default browser and have it navigate to the URL that you have stuffed
in a variable all you need to do is to use the Access.FollowHyperlink
command.

stFullAddress = "http://www.google.com"
access.FollowHyperlink stFullAddress

will open a new browser window showing the Google home page.

Does this answer your question???

Ron W
www.WorksRite.com

Ron,
Definately a setp in the right direction. I think I can get URLEncode to
look at all four fields as one by writing it like:
URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " " &[stZip])

and assuming I can assign it to a string like this:

stFullAddress = URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " "
&[stZip])

how can I plug stFullAddress into a line of code directing it to open a URL
which doesn't take stFullAddress literally, but understands that it's a
variable that means something else?

This is a VB solution, but at a glance looks like it will be a direct port
to Access VBA.

http://www.vb-helper.com/howto_url_encode_string.html

Ron W
www.WorksRite.com

I am trying to place a control on one of my forms that links

automatically
to

maps.google.com (or any driving directions web site for that matter) by
taking the street address, city, state, and/or zip in the database and
building that into the web address. For example,

http://maps.google.com/maps?q=jfk+to+350+5th+ave,+new+york&spn=0.121913,0.233854&hl=en

gives directions from JFK airport (q=jfk) to 350 5th Ave, New York, NY
10118. In my database, the street address is all in one field

[address]
and

would contain "350 5th Ave". SOOO....

1) How do I replace the space between the street number and the multiple
words in the street name with a "+" or a "%20" as appropriate?

2) Can the whole thing be done with an inserted hyperlink on the form, or
do I need to do something else?

 

[Guest]

Ron,
I did not know about access.FollowHyperlink, so that helps a lot. I think
the only part standing in my way is that I would like it to open up the
default browser and go to a URL that contains a variable in the address. In
other words I want to write something like:

http://maps.google.com/maps?q=stFullAddress...

and have it understand that "stFullAddress" is a variable that stands for
the actual street address I want it to look up. I want it to actually send
this to the browser:

http://maps.google.com/maps?q=311+Main+St,+new+york...

I don't want it to take "stFullAddress" literally and try to find a place
called stFullAddress on the map. I hope that clarifies what I'm trying to do
and what my question is. Thanks again for the help.

-Cevin

I am not sure that I understand your question. If you want to open the
users default browser and have it navigate to the URL that you have stuffed
in a variable all you need to do is to use the Access.FollowHyperlink
command.

stFullAddress = "http://www.google.com"
access.FollowHyperlink stFullAddress

will open a new browser window showing the Google home page.

Does this answer your question???

Ron W
www.WorksRite.com

Ron,
Definately a setp in the right direction. I think I can get URLEncode to
look at all four fields as one by writing it like:
URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " " &[stZip])

and assuming I can assign it to a string like this:

stFullAddress = URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " "
&[stZip])

how can I plug stFullAddress into a line of code directing it to open a URL
which doesn't take stFullAddress literally, but understands that it's a
variable that means something else?

This is a VB solution, but at a glance looks like it will be a direct port
to Access VBA.

http://www.vb-helper.com/howto_url_encode_string.html

Ron W
www.WorksRite.com

I am trying to place a control on one of my forms that links automatically
to
maps.google.com (or any driving directions web site for that matter) by
taking the street address, city, state, and/or zip in the database and
building that into the web address. For example,


http://maps.google.com/maps?q=jfk+to+350+5th+ave,+new+york&spn=0.121913,0.233854&hl=en

gives directions from JFK airport (q=jfk) to 350 5th Ave, New York, NY
10118. In my database, the street address is all in one field [address]
and
would contain "350 5th Ave". SOOO....

1) How do I replace the space between the street number and the multiple
words in the street name with a "+" or a "%20" as appropriate?

2) Can the whole thing be done with an inserted hyperlink on the form, or
do I need to do something else?

 

[Ron Weiner]

-Cevin

Based on your pre vious posts the line of code to open the broser might look
like this:

access.FollowHyperlink "http://maps.google.com/maps?q=" & _
URLEncode(& me!stAddress & "+" & me!stcity & "+" _
& me!stState & "+" & me!stZip)

Assuming you have fields with those names bound to the form.

Ron W
www.WorksRite.com

Ron,
I did not know about access.FollowHyperlink, so that helps a lot. I think
the only part standing in my way is that I would like it to open up the
default browser and go to a URL that contains a variable in the address. In
other words I want to write something like:

http://maps.google.com/maps?q=stFullAddress...

and have it understand that "stFullAddress" is a variable that stands for
the actual street address I want it to look up. I want it to actually send
this to the browser:

http://maps.google.com/maps?q=311+Main+St,+new+york...

I don't want it to take "stFullAddress" literally and try to find a place
called stFullAddress on the map. I hope that clarifies what I'm trying to do
and what my question is. Thanks again for the help.

-Cevin

I am not sure that I understand your question. If you want to open the
users default browser and have it navigate to the URL that you have stuffed
in a variable all you need to do is to use the Access.FollowHyperlink
command.

stFullAddress = "http://www.google.com"
access.FollowHyperlink stFullAddress

will open a new browser window showing the Google home page.

Does this answer your question???

Ron W
www.WorksRite.com

Ron,
Definately a setp in the right direction. I think I can get

URLEncode
to

look at all four fields as one by writing it like:
URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " " &[stZip])

and assuming I can assign it to a string like this:

stFullAddress = URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " "
&[stZip])

how can I plug stFullAddress into a line of code directing it to open

a
URL

which doesn't take stFullAddress literally, but understands that it's a
variable that means something else?

:

This is a VB solution, but at a glance looks like it will be a

direct
port

to Access VBA.

http://www.vb-helper.com/howto_url_encode_string.html

Ron W
www.WorksRite.com

I am trying to place a control on one of my forms that links automatically
to
maps.google.com (or any driving directions web site for that

matter)
by

taking the street address, city, state, and/or zip in the database and
building that into the web address. For example,

http://maps.google.com/maps?q=jfk+to+350+5th+ave,+new+york&spn=0.121913,0.233854&hl=en

gives directions from JFK airport (q=jfk) to 350 5th Ave, New York, NY
10118. In my database, the street address is all in one field [address]
and
would contain "350 5th Ave". SOOO....

1) How do I replace the space between the street number and the multiple
words in the street name with a "+" or a "%20" as appropriate?

2) Can the whole thing be done with an inserted hyperlink on the form, or
do I need to do something else?

 

[Guest]

Ron,
Ok, I tried to use access.FollowHyperlink and here's what I came up with:

Private Sub Strip_Map_Enter()
On Error GoTo Err_Strip_Map_Enter

Dim stAddress As String
Dim stCity As String
Dim stState As String
Dim stZip As String
Dim stFullAddress As String

stAddress = [ADDRESS]
stCity = [CITY]
stState = [STATE]
stZip = [ZIP]
stFullAddress = URLEncode(Trim([stAddress] & " " & [stCity] & " " &
[stState] & " " & [stZip]))

Access.FollowHyperlink "www.mapquest.com/directions/main.adp", , -1,
False, stFullAddress, msoMethodPost

Exit_Strip_Map_Enter:
Exit Sub

Err_Strip_Map_Enter:
MsgBox Err.Description
Resume Exit_Strip_Map_Enter

End Sub

I thought that I could use the "ExtraInfo" variant to plug the
"stFullAddress" string into, thinking that I had to set the "Method" to
"MsoMethodPost" in order to get it to resolve the "stFullAddress" variable
into what the variable represented, again, instead of it taking
"stFullAddress" as a literal string. What I get is an error message telling
me:

The expression On Click you entered as the event property setting produced
the following error: Sub or Function not defined
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

Ideas? Feels like I'm getting closser.

I am not sure that I understand your question. If you want to open the
users default browser and have it navigate to the URL that you have stuffed
in a variable all you need to do is to use the Access.FollowHyperlink
command.

stFullAddress = "http://www.google.com"
access.FollowHyperlink stFullAddress

will open a new browser window showing the Google home page.

Does this answer your question???

Ron W
www.WorksRite.com

Ron,
Definately a setp in the right direction. I think I can get URLEncode to
look at all four fields as one by writing it like:
URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " " &[stZip])

and assuming I can assign it to a string like this:

stFullAddress = URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " "
&[stZip])

how can I plug stFullAddress into a line of code directing it to open a URL
which doesn't take stFullAddress literally, but understands that it's a
variable that means something else?

This is a VB solution, but at a glance looks like it will be a direct port
to Access VBA.

http://www.vb-helper.com/howto_url_encode_string.html

Ron W
www.WorksRite.com

I am trying to place a control on one of my forms that links automatically
to
maps.google.com (or any driving directions web site for that matter) by
taking the street address, city, state, and/or zip in the database and
building that into the web address. For example,


http://maps.google.com/maps?q=jfk+to+350+5th+ave,+new+york&spn=0.121913,0.233854&hl=en

gives directions from JFK airport (q=jfk) to 350 5th Ave, New York, NY
10118. In my database, the street address is all in one field [address]
and
would contain "350 5th Ave". SOOO....

1) How do I replace the space between the street number and the multiple
words in the street name with a "+" or a "%20" as appropriate?

2) Can the whole thing be done with an inserted hyperlink on the form, or
do I need to do something else?

 

[Ron Weiner]

This Worked.

Access.FollowHyperlink
"http://www.mapquest.com/maps/map.adp?formtype=address&searchtype=address&ad
dress=1600%20pennsylvania%20Ave&city=Washington&state=DC&zip="

So Therefore ......

Access.FollowHyperlink "http://www.mapquest.com/maps/map.adp?" & _
"formtype=address&searchtype=address" & _
URLEncode("&address=" & Trim(nz([stAddress],"") & _
"&city=" & trim(nz([stCity],"")) & _
"&state=" & trim(nz([stState],"")) & _
"&zip=" & trim(nz([stZip],"")))

will also work as it should pproduce the same URL as the one above. To help
you debug the process you can add the following clode just below the
FolowHyperlink method

Debug.Print ""http://www.mapquest.com/maps/map.adp?" & _
"formtype=address&searchtype=address" & _
URLEncode("&address=" & trim(nz([stAddress],"") & _
"&city=" & trim(nz([stCity],"")) & _
"&state=" & trim(nz([stState],"")) & _
"&zip=" & trim(nz([stZip],"")))

Which will print the string that is being passed to the FollowHyperlink
method in the Immediate window.

Ron W
www.WorksRite.com

Ron,
Ok, I tried to use access.FollowHyperlink and here's what I came up with:

Private Sub Strip_Map_Enter()
On Error GoTo Err_Strip_Map_Enter

Dim stAddress As String
Dim stCity As String
Dim stState As String
Dim stZip As String
Dim stFullAddress As String

stAddress = [ADDRESS]
stCity = [CITY]
stState = [STATE]
stZip = [ZIP]
stFullAddress = URLEncode(Trim([stAddress] & " " & [stCity] & " " &
[stState] & " " & [stZip]))

Access.FollowHyperlink "www.mapquest.com/directions/main.adp", , -1,
False, stFullAddress, msoMethodPost

Exit_Strip_Map_Enter:
Exit Sub

Err_Strip_Map_Enter:
MsgBox Err.Description
Resume Exit_Strip_Map_Enter

End Sub

I thought that I could use the "ExtraInfo" variant to plug the
"stFullAddress" string into, thinking that I had to set the "Method" to
"MsoMethodPost" in order to get it to resolve the "stFullAddress" variable
into what the variable represented, again, instead of it taking
"stFullAddress" as a literal string. What I get is an error message telling
me:

The expression On Click you entered as the event property setting produced
the following error: Sub or Function not defined
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

Ideas? Feels like I'm getting closser.

I am not sure that I understand your question. If you want to open the
users default browser and have it navigate to the URL that you have stuffed
in a variable all you need to do is to use the Access.FollowHyperlink
command.

stFullAddress = "http://www.google.com"
access.FollowHyperlink stFullAddress

will open a new browser window showing the Google home page.

Does this answer your question???

Ron W
www.WorksRite.com

Ron,
Definately a setp in the right direction. I think I can get

URLEncode
to

look at all four fields as one by writing it like:
URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " " &[stZip])

and assuming I can assign it to a string like this:

stFullAddress = URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " "
&[stZip])

how can I plug stFullAddress into a line of code directing it to open

a
URL

which doesn't take stFullAddress literally, but understands that it's a
variable that means something else?

:

This is a VB solution, but at a glance looks like it will be a

direct
port

to Access VBA.

http://www.vb-helper.com/howto_url_encode_string.html

Ron W
www.WorksRite.com

I am trying to place a control on one of my forms that links automatically
to
maps.google.com (or any driving directions web site for that

matter)
by

taking the street address, city, state, and/or zip in the database and
building that into the web address. For example,

http://maps.google.com/maps?q=jfk+to+350+5th+ave,+new+york&spn=0.121913,0.233854&hl=en

gives directions from JFK airport (q=jfk) to 350 5th Ave, New York, NY
10118. In my database, the street address is all in one field [address]
and
would contain "350 5th Ave". SOOO....

1) How do I replace the space between the street number and the multiple
words in the street name with a "+" or a "%20" as appropriate?

2) Can the whole thing be done with an inserted hyperlink on the form, or
do I need to do something else?

 

[Guest]

Hmmm. First off, the underscores after the ampersands ("&_") resulted in
compiling errors: "unknown character", so i took them out and replaced them
with spaces. That gave me this:

Private Sub Strip_Map_Click()
On Error GoTo Err_Strip_Map_Click

Dim stAddress As String
Dim stCity As String
Dim stState As String
Dim stZip As String
Dim stFullAddress As String

stAddress = [ADDRESS]
stCity = [CITY]
stState = [STATE]
stZip = [ZIP]
stFullAddress = URLEncode(Trim([stAddress] & " " & [stCity] & " " &
[stState] & " " & [stZip]))

Access.FollowHyperlink "http://www.mapquest.com/maps/map.adp?" &
"formtype=address&searchtype=address" & URLEncode("&address=" &
Trim(Nz([stAddress], "") & "&city=" & Trim(Nz([stCity], "")) & "&state=" &
Trim(Nz([stState], "")) & "&zip=" & Trim(Nz([stZip], ""))))
Debug.Print "http://www.mapquest.com/maps/map.adp?" &
"formtype=address&searchtype=address" & URLEncode("&address=" &
Trim(Nz([stAddress], "") & "&city=" & Trim(Nz([stCity], "")) & "&state=" &
Trim(Nz([stState], "")) & "&zip=" & Trim(Nz([stZip], ""))))

Exit_Strip_Map_Click:
Exit Sub

Err_Strip_Map_Click:
MsgBox Err.Description
Resume Exit_Strip_Map_Click

End Sub

Which resulted in the same "Sub or Function not defined" error as before.
What is it I'm still missing?

This Worked.

Access.FollowHyperlink
"http://www.mapquest.com/maps/map.adp?formtype=address&searchtype=address&ad
dress=1600%20pennsylvania%20Ave&city=Washington&state=DC&zip="

So Therefore ......

Access.FollowHyperlink "http://www.mapquest.com/maps/map.adp?" & _
"formtype=address&searchtype=address" & _
URLEncode("&address=" & Trim(nz([stAddress],"") & _
"&city=" & trim(nz([stCity],"")) & _
"&state=" & trim(nz([stState],"")) & _
"&zip=" & trim(nz([stZip],"")))

will also work as it should pproduce the same URL as the one above. To help
you debug the process you can add the following clode just below the
FolowHyperlink method

Debug.Print ""http://www.mapquest.com/maps/map.adp?" & _
"formtype=address&searchtype=address" & _
URLEncode("&address=" & trim(nz([stAddress],"") & _
"&city=" & trim(nz([stCity],"")) & _
"&state=" & trim(nz([stState],"")) & _
"&zip=" & trim(nz([stZip],"")))

Which will print the string that is being passed to the FollowHyperlink
method in the Immediate window.

Ron W
www.WorksRite.com

Ron,
Ok, I tried to use access.FollowHyperlink and here's what I came up with:

Private Sub Strip_Map_Enter()
On Error GoTo Err_Strip_Map_Enter

Dim stAddress As String
Dim stCity As String
Dim stState As String
Dim stZip As String
Dim stFullAddress As String

stAddress = [ADDRESS]
stCity = [CITY]
stState = [STATE]
stZip = [ZIP]
stFullAddress = URLEncode(Trim([stAddress] & " " & [stCity] & " " &
[stState] & " " & [stZip]))

Access.FollowHyperlink "www.mapquest.com/directions/main.adp", , -1,
False, stFullAddress, msoMethodPost

Exit_Strip_Map_Enter:
Exit Sub

Err_Strip_Map_Enter:
MsgBox Err.Description
Resume Exit_Strip_Map_Enter

End Sub

I thought that I could use the "ExtraInfo" variant to plug the
"stFullAddress" string into, thinking that I had to set the "Method" to
"MsoMethodPost" in order to get it to resolve the "stFullAddress" variable
into what the variable represented, again, instead of it taking
"stFullAddress" as a literal string. What I get is an error message telling
me:

The expression On Click you entered as the event property setting produced
the following error: Sub or Function not defined
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

Ideas? Feels like I'm getting closser.

I am not sure that I understand your question. If you want to open the
users default browser and have it navigate to the URL that you have stuffed
in a variable all you need to do is to use the Access.FollowHyperlink
command.

stFullAddress = "http://www.google.com"
access.FollowHyperlink stFullAddress

will open a new browser window showing the Google home page.

Does this answer your question???

Ron W
www.WorksRite.com
Ron,
Definately a setp in the right direction. I think I can get URLEncode
to
look at all four fields as one by writing it like:
URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " " &[stZip])

and assuming I can assign it to a string like this:

stFullAddress = URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " "
&[stZip])

how can I plug stFullAddress into a line of code directing it to open a
URL
which doesn't take stFullAddress literally, but understands that it's a
variable that means something else?

:

This is a VB solution, but at a glance looks like it will be a direct
port
to Access VBA.

http://www.vb-helper.com/howto_url_encode_string.html

Ron W
www.WorksRite.com

I am trying to place a control on one of my forms that links
automatically
to
maps.google.com (or any driving directions web site for that matter)
by
taking the street address, city, state, and/or zip in the database and
building that into the web address. For example,



http://maps.google.com/maps?q=jfk+to+350+5th+ave,+new+york&spn=0.121913,0.233854&hl=en

gives directions from JFK airport (q=jfk) to 350 5th Ave, New York, NY
10118. In my database, the street address is all in one field
[address]
and
would contain "350 5th Ave". SOOO....

1) How do I replace the space between the street number and the
multiple
words in the street name with a "+" or a "%20" as appropriate?

2) Can the whole thing be done with an inserted hyperlink on the
form, or
do I need to do something else?

 

[Ron Weiner]

The underscore is the VB line continuetation character and if you removed
them then you need too make sure that everything after
Access.FollowHyperlink and Depug.Print is on ONE line.

Did you include the URLEncode() function in the form that the rest of this
code is in?

Have you tried to single step through your code to see what line is the
offending one?

What happens when you do a Debug | Compile?

Ron W
www.WorksRite.com

Hmmm. First off, the underscores after the ampersands ("&_") resulted in
compiling errors: "unknown character", so i took them out and replaced them
with spaces. That gave me this:

Private Sub Strip_Map_Click()
On Error GoTo Err_Strip_Map_Click

Dim stAddress As String
Dim stCity As String
Dim stState As String
Dim stZip As String
Dim stFullAddress As String

stAddress = [ADDRESS]
stCity = [CITY]
stState = [STATE]
stZip = [ZIP]
stFullAddress = URLEncode(Trim([stAddress] & " " & [stCity] & " " &
[stState] & " " & [stZip]))

Access.FollowHyperlink "http://www.mapquest.com/maps/map.adp?" &
"formtype=address&searchtype=address" & URLEncode("&address=" &
Trim(Nz([stAddress], "") & "&city=" & Trim(Nz([stCity], "")) & "&state=" &
Trim(Nz([stState], "")) & "&zip=" & Trim(Nz([stZip], ""))))
Debug.Print "http://www.mapquest.com/maps/map.adp?" &
"formtype=address&searchtype=address" & URLEncode("&address=" &
Trim(Nz([stAddress], "") & "&city=" & Trim(Nz([stCity], "")) & "&state=" &
Trim(Nz([stState], "")) & "&zip=" & Trim(Nz([stZip], ""))))

Exit_Strip_Map_Click:
Exit Sub

Err_Strip_Map_Click:
MsgBox Err.Description
Resume Exit_Strip_Map_Click

End Sub

Which resulted in the same "Sub or Function not defined" error as before.
What is it I'm still missing?

This Worked.

Access.FollowHyperlink
"http://www.mapquest.com/maps/map.adp?formtype=address&searchtype=address&ad
dress=1600%20pennsylvania%20Ave&city=Washington&state=DC&zip="

So Therefore ......

Access.FollowHyperlink "http://www.mapquest.com/maps/map.adp?" & _
"formtype=address&searchtype=address" & _
URLEncode("&address=" & Trim(nz([stAddress],"") & _
"&city=" & trim(nz([stCity],"")) & _
"&state=" & trim(nz([stState],"")) & _
"&zip=" & trim(nz([stZip],"")))

will also work as it should pproduce the same URL as the one above. To help
you debug the process you can add the following clode just below the
FolowHyperlink method

Debug.Print ""http://www.mapquest.com/maps/map.adp?" & _
"formtype=address&searchtype=address" & _
URLEncode("&address=" & trim(nz([stAddress],"") & _
"&city=" & trim(nz([stCity],"")) & _
"&state=" & trim(nz([stState],"")) & _
"&zip=" & trim(nz([stZip],"")))

Which will print the string that is being passed to the FollowHyperlink
method in the Immediate window.

Ron W
www.WorksRite.com

Ron,
Ok, I tried to use access.FollowHyperlink and here's what I came up with:

Private Sub Strip_Map_Enter()
On Error GoTo Err_Strip_Map_Enter

Dim stAddress As String
Dim stCity As String
Dim stState As String
Dim stZip As String
Dim stFullAddress As String

stAddress = [ADDRESS]
stCity = [CITY]
stState = [STATE]
stZip = [ZIP]
stFullAddress = URLEncode(Trim([stAddress] & " " & [stCity] & " " &
[stState] & " " & [stZip]))

Access.FollowHyperlink "www.mapquest.com/directions/main.adp", , -1,
False, stFullAddress, msoMethodPost

Exit_Strip_Map_Enter:
Exit Sub

Err_Strip_Map_Enter:
MsgBox Err.Description
Resume Exit_Strip_Map_Enter

End Sub

I thought that I could use the "ExtraInfo" variant to plug the
"stFullAddress" string into, thinking that I had to set the "Method" to
"MsoMethodPost" in order to get it to resolve the "stFullAddress" variable
into what the variable represented, again, instead of it taking
"stFullAddress" as a literal string. What I get is an error message telling
me:

The expression On Click you entered as the event property setting produced
the following error: Sub or Function not defined
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

Ideas? Feels like I'm getting closser.

:

I am not sure that I understand your question. If you want to open the
users default browser and have it navigate to the URL that you have stuffed
in a variable all you need to do is to use the Access.FollowHyperlink
command.

stFullAddress = "http://www.google.com"
access.FollowHyperlink stFullAddress

will open a new browser window showing the Google home page.

Does this answer your question???

Ron W
www.WorksRite.com
Ron,
Definately a setp in the right direction. I think I can get URLEncode
to
look at all four fields as one by writing it like:
URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " " &[stZip])

and assuming I can assign it to a string like this:

stFullAddress = URLEncode([stAddress]& " " &[stcity]& " "

&[stState]&
" "

&[stZip])

how can I plug stFullAddress into a line of code directing it to

open
a

URL
which doesn't take stFullAddress literally, but understands that

it's
a

variable that means something else?

:

This is a VB solution, but at a glance looks like it will be a direct
port
to Access VBA.

http://www.vb-helper.com/howto_url_encode_string.html

Ron W
www.WorksRite.com

I am trying to place a control on one of my forms that links
automatically
to
maps.google.com (or any driving directions web site for that matter)
by
taking the street address, city, state, and/or zip in the

database
and

building that into the web address. For example,

http://maps.google.com/maps?q=jfk+to+350+5th+ave,+new+york&spn=0.121913,0.233854&hl=en

gives directions from JFK airport (q=jfk) to 350 5th Ave, New York, NY
10118. In my database, the street address is all in one field
[address]
and
would contain "350 5th Ave". SOOO....

1) How do I replace the space between the street number and the
multiple
words in the street name with a "+" or a "%20" as appropriate?

2) Can the whole thing be done with an inserted hyperlink on the
form, or
do I need to do something else?

 

[Guest]

OoooKay. ALMOST there. Made some adjustments and got it to send the URL to
the browser, butwith one problem: it did not convert the hex values for the
"&" or the "=", instead it input "%26" and %3D" respectively. Now I've seen
"%20" in URL addresses before, and that seemed to work just fine (such as in
web searches, etc), but MapQuest came up with a default page and asked for
the address, so it obviously had a problem understanding it. I entered the
same address into the Mapquest form and it returned virtually the same
address, with the use of "&" and "=" being the only differences.

Here's what I've changed:

1) I did have to put it all on one line because VBA would not accept the
line continuation.

2) URLEncode was in the code, but the private sub SetSafeChars() followed
the URLEncode, so it came up as undefined when I did a Debug | Compile.
Switching the order solved that problem, so I'm assuming that the order was
in fact important.

That's it. All I need to do is figure out why it's not entering the
character itself but the hex value and it should be good. I'll keep
hammering away at it all day today. If you happen to check this today
(Saturday) and can figure out what's wrong, let me know. For the record,
here's the full code:

Option Compare Database
Option Explicit

Private m_SafeChar(0 To 255) As Boolean

' Set m_SafeChar(i) = True for characters that
' do not need protection.
Private Sub SetSafeChars()
Static done_before As Boolean
Dim i As Integer

If done_before Then Exit Sub
done_before = True

For i = 0 To 47
m_SafeChar(i) = False
Next i
For i = 48 To 57
m_SafeChar(i) = True
Next i
For i = 58 To 64
m_SafeChar(i) = False
Next i
For i = 65 To 90
m_SafeChar(i) = True
Next i
For i = 91 To 96
m_SafeChar(i) = False
Next i
For i = 97 To 122
m_SafeChar(i) = True
Next i
For i = 123 To 255
m_SafeChar(i) = False
Next i
End Sub

' Return a URL safe encoding of txt.
Private Function URLEncode(ByVal txt As String) As String
Dim i As Integer
Dim ch As String
Dim ch_asc As Integer
Dim result As String

SetSafeChars

result = ""
For i = 1 To Len(txt)
' Translate the next character.
ch = Mid$(txt, i, 1)
ch_asc = Asc(ch)
If ch_asc = vbKeySpace Then
' Use a plus.
result = result & "+"
ElseIf m_SafeChar(ch_asc) Then
' Use the character.
result = result & ch
Else
' Convert the character to hex.
result = result & "%" & Right$("0" & _
Hex$(ch_asc), 2)
End If
Next i

URLEncode = result
End Function

Private Sub Strip_Map_Click()
On Error GoTo Err_Strip_Map_Click

Dim stAddress As String
Dim stCity As String
Dim stState As String
Dim stZip As String

stAddress = [ADDRESS]
stCity = [CITY]
stState = [STATE]
stZip = [ZIP]

Access.FollowHyperlink "http://www.mapquest.com/maps/map.adp?" &
"formtype=address&searchtype=address" & URLEncode("&address=" &
Trim(Nz([stAddress], "") & "&city=" & Trim(Nz([stCity], "")) & "&state=" &
Trim(Nz([stState], "")) & "&zip=" & Trim(Nz([stZip], ""))))

Debug.Print "http://www.mapquest.com/maps/map.adp?" &
"formtype=address&searchtype=address" & URLEncode("&address=" &
Trim(Nz([stAddress], "") & "&city=" & Trim(Nz([stCity], "")) & "&state=" &
Trim(Nz([stState], "")) & "&zip=" & Trim(Nz([stZip], ""))))

Exit_Strip_Map_Click:
Exit Sub

Err_Strip_Map_Click:
MsgBox Err.Description
Resume Exit_Strip_Map_Click

End Sub


Thanks again for the help,
Cevin

The underscore is the VB line continuetation character and if you removed
them then you need too make sure that everything after
Access.FollowHyperlink and Depug.Print is on ONE line.

Did you include the URLEncode() function in the form that the rest of this
code is in?

Have you tried to single step through your code to see what line is the
offending one?

What happens when you do a Debug | Compile?

Ron W
www.WorksRite.com

Hmmm. First off, the underscores after the ampersands ("&_") resulted in
compiling errors: "unknown character", so i took them out and replaced them
with spaces. That gave me this:

Private Sub Strip_Map_Click()
On Error GoTo Err_Strip_Map_Click

Dim stAddress As String
Dim stCity As String
Dim stState As String
Dim stZip As String
Dim stFullAddress As String

stAddress = [ADDRESS]
stCity = [CITY]
stState = [STATE]
stZip = [ZIP]
stFullAddress = URLEncode(Trim([stAddress] & " " & [stCity] & " " &
[stState] & " " & [stZip]))

Access.FollowHyperlink "http://www.mapquest.com/maps/map.adp?" &
"formtype=address&searchtype=address" & URLEncode("&address=" &
Trim(Nz([stAddress], "") & "&city=" & Trim(Nz([stCity], "")) & "&state=" &
Trim(Nz([stState], "")) & "&zip=" & Trim(Nz([stZip], ""))))
Debug.Print "http://www.mapquest.com/maps/map.adp?" &
"formtype=address&searchtype=address" & URLEncode("&address=" &
Trim(Nz([stAddress], "") & "&city=" & Trim(Nz([stCity], "")) & "&state=" &
Trim(Nz([stState], "")) & "&zip=" & Trim(Nz([stZip], ""))))

Exit_Strip_Map_Click:
Exit Sub

Err_Strip_Map_Click:
MsgBox Err.Description
Resume Exit_Strip_Map_Click

End Sub

Which resulted in the same "Sub or Function not defined" error as before.
What is it I'm still missing?

This Worked.

Access.FollowHyperlink
"http://www.mapquest.com/maps/map.adp?formtype=address&searchtype=address&ad
dress=1600%20pennsylvania%20Ave&city=Washington&state=DC&zip="

So Therefore ......

Access.FollowHyperlink "http://www.mapquest.com/maps/map.adp?" & _
"formtype=address&searchtype=address" & _
URLEncode("&address=" & Trim(nz([stAddress],"") & _
"&city=" & trim(nz([stCity],"")) & _
"&state=" & trim(nz([stState],"")) & _
"&zip=" & trim(nz([stZip],"")))

will also work as it should pproduce the same URL as the one above. To help
you debug the process you can add the following clode just below the
FolowHyperlink method

Debug.Print ""http://www.mapquest.com/maps/map.adp?" & _
"formtype=address&searchtype=address" & _
URLEncode("&address=" & trim(nz([stAddress],"") & _
"&city=" & trim(nz([stCity],"")) & _
"&state=" & trim(nz([stState],"")) & _
"&zip=" & trim(nz([stZip],"")))

Which will print the string that is being passed to the FollowHyperlink
method in the Immediate window.

Ron W
www.WorksRite.com

Ron,
Ok, I tried to use access.FollowHyperlink and here's what I came up with:

Private Sub Strip_Map_Enter()
On Error GoTo Err_Strip_Map_Enter

Dim stAddress As String
Dim stCity As String
Dim stState As String
Dim stZip As String
Dim stFullAddress As String

stAddress = [ADDRESS]
stCity = [CITY]
stState = [STATE]
stZip = [ZIP]
stFullAddress = URLEncode(Trim([stAddress] & " " & [stCity] & " " &
[stState] & " " & [stZip]))

Access.FollowHyperlink "www.mapquest.com/directions/main.adp", , -1,
False, stFullAddress, msoMethodPost

Exit_Strip_Map_Enter:
Exit Sub

Err_Strip_Map_Enter:
MsgBox Err.Description
Resume Exit_Strip_Map_Enter

End Sub

I thought that I could use the "ExtraInfo" variant to plug the
"stFullAddress" string into, thinking that I had to set the "Method" to
"MsoMethodPost" in order to get it to resolve the "stFullAddress" variable
into what the variable represented, again, instead of it taking
"stFullAddress" as a literal string. What I get is an error message
telling
me:

The expression On Click you entered as the event property setting produced
the following error: Sub or Function not defined
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

Ideas? Feels like I'm getting closser.

:

I am not sure that I understand your question. If you want to open the
users default browser and have it navigate to the URL that you have
stuffed
in a variable all you need to do is to use the Access.FollowHyperlink
command.

stFullAddress = "http://www.google.com"
access.FollowHyperlink stFullAddress

will open a new browser window showing the Google home page.

Does this answer your question???

Ron W
www.WorksRite.com
Ron,
Definately a setp in the right direction. I think I can get
URLEncode
to
look at all four fields as one by writing it like:
URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " " &[stZip])

and assuming I can assign it to a string like this:

stFullAddress = URLEncode([stAddress]& " " &[stcity]& " " &[stState]&
" "
&[stZip])

how can I plug stFullAddress into a line of code directing it to open
a
URL
which doesn't take stFullAddress literally, but understands that it's
a
variable that means something else?

:

This is a VB solution, but at a glance looks like it will be a
direct
port
to Access VBA.

http://www.vb-helper.com/howto_url_encode_string.html

Ron W
www.WorksRite.com

I am trying to place a control on one of my forms that links
automatically
to
maps.google.com (or any driving directions web site for that
matter)
by
taking the street address, city, state, and/or zip in the database
and
building that into the web address. For example,




http://maps.google.com/maps?q=jfk+to+350+5th+ave,+new+york&spn=0.121913,0.233854&hl=en

gives directions from JFK airport (q=jfk) to 350 5th Ave, New
York, NY
10118. In my database, the street address is all in one field
[address]
and
would contain "350 5th Ave". SOOO....

1) How do I replace the space between the street number and the
multiple
words in the street name with a "+" or a "%20" as appropriate?

2) Can the whole thing be done with an inserted hyperlink on the
form, or
do I need to do something else?

 

[Guest]

TOUCHDOWN!!!!

Ok, I removed the conversion of unsafe characters to hex value portion of
URLEncode and it worked. Since SetSafeChar() was defining "&" and "=" as
unsafe, it was inputting the hex value, which wasn't working. All I'm really
concerned with is the spaces in the address and city fields being converted
to "+" for the URL. I wanted the "&" and the "=" which were actually written
into the Access.FollowHyperlink as literal text, i.e. where they appear on
either side of the word "address" here: ("&address=" &
Trim(Nz([stAddress],"")). I don't expect any other characters to interfere
with this particular event procedure, given the limited characters included
in a street address, so I should be good from here.

Again, I can't thank you enough. This has been a pretty monumental feature
to add to my database that serves as more of a "look what I can do" that
serving any great benefit. Anyone can just as easily type it in themselves,
but DAMN this makes it cool. Only problem is that a very select few will
ever, if ever, appreciate the beauty of it and understand how hard it was to
encode. Then again, that's the way it is will everything in the IT field.
I'm not a school trained programmer by any means, but I do enjoy fighting
through it to gain that appreciation for all of the work that goes into even
the simple things like clicking an icon that shows you a map to your house.

Thanks again,
Cevin

The underscore is the VB line continuetation character and if you removed
them then you need too make sure that everything after
Access.FollowHyperlink and Depug.Print is on ONE line.

Did you include the URLEncode() function in the form that the rest of this
code is in?

Have you tried to single step through your code to see what line is the
offending one?

What happens when you do a Debug | Compile?

Ron W
www.WorksRite.com

Hmmm. First off, the underscores after the ampersands ("&_") resulted in
compiling errors: "unknown character", so i took them out and replaced them
with spaces. That gave me this:

Private Sub Strip_Map_Click()
On Error GoTo Err_Strip_Map_Click

Dim stAddress As String
Dim stCity As String
Dim stState As String
Dim stZip As String
Dim stFullAddress As String

stAddress = [ADDRESS]
stCity = [CITY]
stState = [STATE]
stZip = [ZIP]
stFullAddress = URLEncode(Trim([stAddress] & " " & [stCity] & " " &
[stState] & " " & [stZip]))

Access.FollowHyperlink "http://www.mapquest.com/maps/map.adp?" &
"formtype=address&searchtype=address" & URLEncode("&address=" &
Trim(Nz([stAddress], "") & "&city=" & Trim(Nz([stCity], "")) & "&state=" &
Trim(Nz([stState], "")) & "&zip=" & Trim(Nz([stZip], ""))))
Debug.Print "http://www.mapquest.com/maps/map.adp?" &
"formtype=address&searchtype=address" & URLEncode("&address=" &
Trim(Nz([stAddress], "") & "&city=" & Trim(Nz([stCity], "")) & "&state=" &
Trim(Nz([stState], "")) & "&zip=" & Trim(Nz([stZip], ""))))

Exit_Strip_Map_Click:
Exit Sub

Err_Strip_Map_Click:
MsgBox Err.Description
Resume Exit_Strip_Map_Click

End Sub

Which resulted in the same "Sub or Function not defined" error as before.
What is it I'm still missing?

This Worked.

Access.FollowHyperlink
"http://www.mapquest.com/maps/map.adp?formtype=address&searchtype=address&ad
dress=1600%20pennsylvania%20Ave&city=Washington&state=DC&zip="

So Therefore ......

Access.FollowHyperlink "http://www.mapquest.com/maps/map.adp?" & _
"formtype=address&searchtype=address" & _
URLEncode("&address=" & Trim(nz([stAddress],"") & _
"&city=" & trim(nz([stCity],"")) & _
"&state=" & trim(nz([stState],"")) & _
"&zip=" & trim(nz([stZip],"")))

will also work as it should pproduce the same URL as the one above. To help
you debug the process you can add the following clode just below the
FolowHyperlink method

Debug.Print ""http://www.mapquest.com/maps/map.adp?" & _
"formtype=address&searchtype=address" & _
URLEncode("&address=" & trim(nz([stAddress],"") & _
"&city=" & trim(nz([stCity],"")) & _
"&state=" & trim(nz([stState],"")) & _
"&zip=" & trim(nz([stZip],"")))

Which will print the string that is being passed to the FollowHyperlink
method in the Immediate window.

Ron W
www.WorksRite.com

Ron,
Ok, I tried to use access.FollowHyperlink and here's what I came up with:

Private Sub Strip_Map_Enter()
On Error GoTo Err_Strip_Map_Enter

Dim stAddress As String
Dim stCity As String
Dim stState As String
Dim stZip As String
Dim stFullAddress As String

stAddress = [ADDRESS]
stCity = [CITY]
stState = [STATE]
stZip = [ZIP]
stFullAddress = URLEncode(Trim([stAddress] & " " & [stCity] & " " &
[stState] & " " & [stZip]))

Access.FollowHyperlink "www.mapquest.com/directions/main.adp", , -1,
False, stFullAddress, msoMethodPost

Exit_Strip_Map_Enter:
Exit Sub

Err_Strip_Map_Enter:
MsgBox Err.Description
Resume Exit_Strip_Map_Enter

End Sub

I thought that I could use the "ExtraInfo" variant to plug the
"stFullAddress" string into, thinking that I had to set the "Method" to
"MsoMethodPost" in order to get it to resolve the "stFullAddress" variable
into what the variable represented, again, instead of it taking
"stFullAddress" as a literal string. What I get is an error message
telling
me:

The expression On Click you entered as the event property setting produced
the following error: Sub or Function not defined
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

Ideas? Feels like I'm getting closser.

:

I am not sure that I understand your question. If you want to open the
users default browser and have it navigate to the URL that you have
stuffed
in a variable all you need to do is to use the Access.FollowHyperlink
command.

stFullAddress = "http://www.google.com"
access.FollowHyperlink stFullAddress

will open a new browser window showing the Google home page.

Does this answer your question???

Ron W
www.WorksRite.com
Ron,
Definately a setp in the right direction. I think I can get
URLEncode
to
look at all four fields as one by writing it like:
URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " " &[stZip])

and assuming I can assign it to a string like this:

stFullAddress = URLEncode([stAddress]& " " &[stcity]& " " &[stState]&
" "
&[stZip])

how can I plug stFullAddress into a line of code directing it to open
a
URL
which doesn't take stFullAddress literally, but understands that it's
a
variable that means something else?

:

This is a VB solution, but at a glance looks like it will be a
direct
port
to Access VBA.

http://www.vb-helper.com/howto_url_encode_string.html

Ron W
www.WorksRite.com

I am trying to place a control on one of my forms that links
automatically
to
maps.google.com (or any driving directions web site for that
matter)
by
taking the street address, city, state, and/or zip in the database
and
building that into the web address. For example,




http://maps.google.com/maps?q=jfk+to+350+5th+ave,+new+york&spn=0.121913,0.233854&hl=en

gives directions from JFK airport (q=jfk) to 350 5th Ave, New
York, NY
10118. In my database, the street address is all in one field
[address]
and
would contain "350 5th Ave". SOOO....

1) How do I replace the space between the street number and the
multiple
words in the street name with a "+" or a "%20" as appropriate?

2) Can the whole thing be done with an inserted hyperlink on the
form, or
do I need to do something else?

 

[Ron Weiner]

Cevin

Glad you got your 6 points. Congrats!

Ron W
www.WorksRite.com

TOUCHDOWN!!!!

Ok, I removed the conversion of unsafe characters to hex value portion of
URLEncode and it worked. Since SetSafeChar() was defining "&" and "=" as
unsafe, it was inputting the hex value, which wasn't working. All I'm really
concerned with is the spaces in the address and city fields being converted
to "+" for the URL. I wanted the "&" and the "=" which were actually written
into the Access.FollowHyperlink as literal text, i.e. where they appear on
either side of the word "address" here: ("&address=" &
Trim(Nz([stAddress],"")). I don't expect any other characters to interfere
with this particular event procedure, given the limited characters included
in a street address, so I should be good from here.

Again, I can't thank you enough. This has been a pretty monumental feature
to add to my database that serves as more of a "look what I can do" that
serving any great benefit. Anyone can just as easily type it in themselves,
but DAMN this makes it cool. Only problem is that a very select few will
ever, if ever, appreciate the beauty of it and understand how hard it was to
encode. Then again, that's the way it is will everything in the IT field.
I'm not a school trained programmer by any means, but I do enjoy fighting
through it to gain that appreciation for all of the work that goes into even
the simple things like clicking an icon that shows you a map to your house.

Thanks again,
Cevin

The underscore is the VB line continuetation character and if you removed
them then you need too make sure that everything after
Access.FollowHyperlink and Depug.Print is on ONE line.

Did you include the URLEncode() function in the form that the rest of th is
code is in?

Have you tried to single step through your code to see what line is the
offending one?

What happens when you do a Debug | Compile?

Ron W
www.WorksRite.com

Hmmm. First off, the underscores after the ampersands ("&_") resulted in
compiling errors: "unknown character", so i took them out and replaced them
with spaces. That gave me this:

Private Sub Strip_Map_Click()
On Error GoTo Err_Strip_Map_Click

Dim stAddress As String
Dim stCity As String
Dim stState As String
Dim stZip As String
Dim stFullAddress As String

stAddress = [ADDRESS]
stCity = [CITY]
stState = [STATE]
stZip = [ZIP]
stFullAddress = URLEncode(Trim([stAddress] & " " & [stCity] & " " &
[stState] & " " & [stZip]))

Access.FollowHyperlink "http://www.mapquest.com/maps/map.adp?" &
"formtype=address&searchtype=address" & URLEncode("&address=" &
Trim(Nz([stAddress], "") & "&city=" & Trim(Nz([stCity], "")) & "&state=" &
Trim(Nz([stState], "")) & "&zip=" & Trim(Nz([stZip], ""))))
Debug.Print "http://www.mapquest.com/maps/map.adp?" &
"formtype=address&searchtype=address" & URLEncode("&address=" &
Trim(Nz([stAddress], "") & "&city=" & Trim(Nz([stCity], "")) & "&state=" &
Trim(Nz([stState], "")) & "&zip=" & Trim(Nz([stZip], ""))))

Exit_Strip_Map_Click:
Exit Sub

Err_Strip_Map_Click:
MsgBox Err.Description
Resume Exit_Strip_Map_Click

End Sub

Which resulted in the same "Sub or Function not defined" error as before.
What is it I'm still missing?

:

This Worked.

Access.FollowHyperlink

"http://www.mapquest.com/maps/map.adp?formtype=address&searchtype=address&ad

dress=1600%20pennsylvania%20Ave&city=Washington&state=DC&zip="

So Therefore ......

Access.FollowHyperlink "http://www.mapquest.com/maps/map.adp?" & _
"formtype=address&searchtype=address" & _
URLEncode("&address=" & Trim(nz([stAddress],"") & _
"&city=" & trim(nz([stCity],"")) & _
"&state=" & trim(nz([stState],"")) & _
"&zip=" & trim(nz([stZip],"")))

will also work as it should pproduce the same URL as the one above.

To
help

you debug the process you can add the following clode just below the
FolowHyperlink method

Debug.Print ""http://www.mapquest.com/maps/map.adp?" & _
"formtype=address&searchtype=address" & _
URLEncode("&address=" & trim(nz([stAddress],"") & _
"&city=" & trim(nz([stCity],"")) & _
"&state=" & trim(nz([stState],"")) & _
"&zip=" & trim(nz([stZip],"")))

Which will print the string that is being passed to the FollowHyperlink
method in the Immediate window.

Ron W
www.WorksRite.com

Ron,
Ok, I tried to use access.FollowHyperlink and here's what I came

up
with:

Private Sub Strip_Map_Enter()
On Error GoTo Err_Strip_Map_Enter

Dim stAddress As String
Dim stCity As String
Dim stState As String
Dim stZip As String
Dim stFullAddress As String

stAddress = [ADDRESS]
stCity = [CITY]
stState = [STATE]
stZip = [ZIP]
stFullAddress = URLEncode(Trim([stAddress] & " " & [stCity] &

" "
&

[stState] & " " & [stZip]))

Access.FollowHyperlink "www.mapquest.com/directions/main.adp", , -1,
False, stFullAddress, msoMethodPost

Exit_Strip_Map_Enter:
Exit Sub

Err_Strip_Map_Enter:
MsgBox Err.Description
Resume Exit_Strip_Map_Enter

End Sub

I thought that I could use the "ExtraInfo" variant to plug the
"stFullAddress" string into, thinking that I had to set the

"Method"
to

"MsoMethodPost" in order to get it to resolve the "stFullAddress" variable
into what the variable represented, again, instead of it taking
"stFullAddress" as a literal string. What I get is an error message
telling
me:

The expression On Click you entered as the event property setting produced
the following error: Sub or Function not defined
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

Ideas? Feels like I'm getting closser.

:

I am not sure that I understand your question. If you want to

open
the

users default browser and have it navigate to the URL that you have
stuffed
in a variable all you need to do is to use the Access.FollowHyperlink
command.

stFullAddress = "http://www.google.com"
access.FollowHyperlink stFullAddress

will open a new browser window showing the Google home page.

Does this answer your question???

Ron W
www.WorksRite.com
Ron,
Definately a setp in the right direction. I think I can get
URLEncode
to
look at all four fields as one by writing it like:
URLEncode([stAddress]& " " &[stcity]& " " &[stState]& " " &[stZip])

and assuming I can assign it to a string like this:

stFullAddress = URLEncode([stAddress]& " " &[stcity]& " " &[stState]&
" "
&[stZip])

how can I plug stFullAddress into a line of code directing it

to
open

a
URL
which doesn't take stFullAddress literally, but understands

that
it's

a
variable that means something else?

:

This is a VB solution, but at a glance looks like it will be a
direct
port
to Access VBA.

http://www.vb-helper.com/howto_url_encode_string.html

Ron W
www.WorksRite.com

I am trying to place a control on one of my forms that links
automatically
to
maps.google.com (or any driving directions web site for that
matter)
by
taking the street address, city, state, and/or zip in the database
and
building that into the web address. For example,

http://maps.google.com/maps?q=jfk+to+350+5th+ave,+new+york&spn=0.121913,0.233854&hl=en

gives directions from JFK airport (q=jfk) to 350 5th Ave, New
York, NY
10118. In my database, the street address is all in one field
[address]
and
would contain "350 5th Ave". SOOO....

1) How do I replace the space between the street number

and
the

multiple
words in the street name with a "+" or a "%20" as appropriate?

2) Can the whole thing be done with an inserted hyperlink

on
the

form, or
do I need to do something else?

 

[Guest]

So for those of us that would like to see the final string outcome, what
would it happen to be?

 

[CevinMoses]

Wow, never saw the question added two years later. So, here I am, two MORE
years later. I'm not sure if I still have a copy of that or not, but I'll
see if I can dig it up. Let me know if you're still at all interested. I'm
sure you've moved on, though.

 

[CevinMoses]

Reply if you need it.