Passing textbox data into String for URL

[ScubaSteve]

I'm trying to take an address from a textbox in a form "Address, City,
State, Zip" and create a url string from the data and pass it into
google maps that is in a webbrowser within an access tab.

The hyperlink should look like this:

"http://maps.google.com/maps?q=111+West+Gable+Lane+Dayton+OH+45410&iwl..."


So I just need Address to go in first, then city, state, zip etc. How
would I do that from a textbox? I get really confused with all of the
& and " in these statements. Is it easier to do from a table? I
imagine I'd have to pull up the primary key first so it passes the
right data along - which is why I thought it would be easier to do from
a textbox since it is already showing that data.

Form is [frmCase].[frmPatient] (which is a subform) and the textboxes
are txtaddress, txtcity, txt state, and txt zip.

Thanks!

 

[ScubaSteve]

Daniel,

Thanks so much for the response. I understand how that works, but now
I've run into a problem I think with the hyperlink. The way the
address is displayed in the hyperlink is with a + between each word.
So 111 + Pheasant + Lane .. etc. My txtaddress only shows 111 Pheasant
Lane.

So the URL would read

http://maps.google.com/maps?q=111 Pheasant
Lane+txtcity+txtstate+txtzip. Any way to insert + into the spaces of
the address so that it comes out right? Or any way to display this url
into another textbox? I tried to make a textbox with =[myGoogleMapURL]
in it and I get a #Name.

My code:
Private Sub CmdGet_click()

Dim strAdd As String
Dim strCity As String
Dim strState As String
Dim strZip As String
Dim MyGoogleMapURL As String
Dim strHypPre As String 'Google prefix url
Dim strHypCri As String


strAdd = Me.txtaddress
strCity = Me.txtcity
strState = Me.txtstate
strZip = Me.txtzip


strHypPre = "http://maps.google.com/maps?q="
strHypCri = strAdd & "+" & strCity & "+" & strState & "+" & strZip
MyGoogleMapURL = strHypPre & strHypCri
End Sub

This is on the patient subform of the tabbed record. And then on the
map tab, I have a button to pass this string to the webbrowser.

Private Sub cmdgotourl_Click()
Me.WebBrowser0.Navigate MyGoogleMapURL
End Sub

Again, thanks for your help!

 

[ScubaSteve]

Oh, the reason I think there may be a problem with the address is I get
this error:

This program cannot display the webpage

Most likely causes:
You are not connected to the Internet.
The website is encountering problems.
There might be a typing error in the address.


When I set the me.navigate to a normal webpage "http://maps.google.com"
it works. So it is not my internet. I can access the webpage outside
of access so the website is not the problem. So it must be the
address...

 

[Douglas J. Steele]

Try using the Replace function:

strHypCri = Replace(strAdd & "+" & strCity & "+" & strState & "+" & strZip,
" ", "+")

 

[ScubaSteve]

Well, the string looks perfect, but the webbrowser is just not taking
it.

When I copy and paste the string into an actual browser, it works.

Is it because I have the creation of the string in a subform and then
the webpage in another form? Does it have to be on the same page?
Would I just reference the textboxes as
Forms![frmCase].[frmpatient]![txtaddress]?

String looks good though in the patient form!

Try using the Replace function:

strHypCri = Replace(strAdd & "+" & strCity & "+" & strState & "+" & strZip,
" ", "+")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Daniel,

Thanks so much for the response. I understand how that works, but now
I've run into a problem I think with the hyperlink. The way the
address is displayed in the hyperlink is with a + between each word.
So 111 + Pheasant + Lane .. etc. My txtaddress only shows 111 Pheasant
Lane.

So the URL would read

http://maps.google.com/maps?q=111 Pheasant
Lane+txtcity+txtstate+txtzip. Any way to insert + into the spaces of
the address so that it comes out right? Or any way to display this url
into another textbox? I tried to make a textbox with =[myGoogleMapURL]
in it and I get a #Name.

My code:
Private Sub CmdGet_click()

Dim strAdd As String
Dim strCity As String
Dim strState As String
Dim strZip As String
Dim MyGoogleMapURL As String
Dim strHypPre As String 'Google prefix url
Dim strHypCri As String


strAdd = Me.txtaddress
strCity = Me.txtcity
strState = Me.txtstate
strZip = Me.txtzip


strHypPre = "http://maps.google.com/maps?q="
strHypCri = strAdd & "+" & strCity & "+" & strState & "+" & strZip
MyGoogleMapURL = strHypPre & strHypCri
End Sub

This is on the patient subform of the tabbed record. And then on the
map tab, I have a button to pass this string to the webbrowser.

Private Sub cmdgotourl_Click()
Me.WebBrowser0.Navigate MyGoogleMapURL
End Sub

Again, thanks for your help!

 

[Douglas J. Steele]

Can you navigate to other websites using your current setup?

To refer to a control on a subform, you need to use

Forms!NameOfParentForm!NameOfSubformControlOnParentForm.Form!NameOfControlOnSubform

Depending on how you created the subform, the name of the subform control on
the parent form may not be the same as the name of the form being used as a
subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well, the string looks perfect, but the webbrowser is just not taking
it.

When I copy and paste the string into an actual browser, it works.

Is it because I have the creation of the string in a subform and then
the webpage in another form? Does it have to be on the same page?
Would I just reference the textboxes as
Forms![frmCase].[frmpatient]![txtaddress]?

String looks good though in the patient form!

Try using the Replace function:

strHypCri = Replace(strAdd & "+" & strCity & "+" & strState & "+" &
strZip,
" ", "+")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Daniel,

Thanks so much for the response. I understand how that works, but now
I've run into a problem I think with the hyperlink. The way the
address is displayed in the hyperlink is with a + between each word.
So 111 + Pheasant + Lane .. etc. My txtaddress only shows 111 Pheasant
Lane.

So the URL would read

http://maps.google.com/maps?q=111 Pheasant
Lane+txtcity+txtstate+txtzip. Any way to insert + into the spaces of
the address so that it comes out right? Or any way to display this url
into another textbox? I tried to make a textbox with =[myGoogleMapURL]
in it and I get a #Name.

My code:
Private Sub CmdGet_click()

Dim strAdd As String
Dim strCity As String
Dim strState As String
Dim strZip As String
Dim MyGoogleMapURL As String
Dim strHypPre As String 'Google prefix url
Dim strHypCri As String


strAdd = Me.txtaddress
strCity = Me.txtcity
strState = Me.txtstate
strZip = Me.txtzip


strHypPre = "http://maps.google.com/maps?q="
strHypCri = strAdd & "+" & strCity & "+" & strState & "+" & strZip
MyGoogleMapURL = strHypPre & strHypCri
End Sub

This is on the patient subform of the tabbed record. And then on the
map tab, I have a button to pass this string to the webbrowser.

Private Sub cmdgotourl_Click()
Me.WebBrowser0.Navigate MyGoogleMapURL
End Sub

Again, thanks for your help!

 

[ScubaSteve]

Yeah, I can browse to another webpage no problem.

I created a textbox that is unbound, input an address into it, and had
the browser display the page I wanted. I also moved the box to the
proper form using the reference to the subform you stated below.

It's almost as if the string isn't storing anywhere. I tried changing
the textbox to =([strHyp]) and I get a #Name in the textbox.

I input at the end of the GetMapClick a MsgBox to display the hyperlink
and it looks good. Just not passing it to the browser right.

Can you navigate to other websites using your current setup?

To refer to a control on a subform, you need to use

Forms!NameOfParentForm!NameOfSubformControlOnParentForm.Form!NameOfControlOnSubform

Depending on how you created the subform, the name of the subform control on
the parent form may not be the same as the name of the form being used as a
subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well, the string looks perfect, but the webbrowser is just not taking
it.

When I copy and paste the string into an actual browser, it works.

Is it because I have the creation of the string in a subform and then
the webpage in another form? Does it have to be on the same page?
Would I just reference the textboxes as
Forms![frmCase].[frmpatient]![txtaddress]?

String looks good though in the patient form!

Try using the Replace function:

strHypCri = Replace(strAdd & "+" & strCity & "+" & strState & "+" &
strZip,
" ", "+")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Daniel,

Thanks so much for the response. I understand how that works, but now
I've run into a problem I think with the hyperlink. The way the
address is displayed in the hyperlink is with a + between each word.
So 111 + Pheasant + Lane .. etc. My txtaddress only shows 111 Pheasant
Lane.

So the URL would read

http://maps.google.com/maps?q=111 Pheasant
Lane+txtcity+txtstate+txtzip. Any way to insert + into the spaces of
the address so that it comes out right? Or any way to display this url
into another textbox? I tried to make a textbox with =[myGoogleMapURL]
in it and I get a #Name.

My code:
Private Sub CmdGet_click()

Dim strAdd As String
Dim strCity As String
Dim strState As String
Dim strZip As String
Dim MyGoogleMapURL As String
Dim strHypPre As String 'Google prefix url
Dim strHypCri As String


strAdd = Me.txtaddress
strCity = Me.txtcity
strState = Me.txtstate
strZip = Me.txtzip


strHypPre = "http://maps.google.com/maps?q="
strHypCri = strAdd & "+" & strCity & "+" & strState & "+" & strZip
MyGoogleMapURL = strHypPre & strHypCri
End Sub

This is on the patient subform of the tabbed record. And then on the
map tab, I have a button to pass this string to the webbrowser.

Private Sub cmdgotourl_Click()
Me.WebBrowser0.Navigate MyGoogleMapURL
End Sub

Again, thanks for your help!

 

[Douglas J. Steele]

You're absolutely right. The string ISN'T stored anywhere!

You're building MyGoogleMapURL in sub CmdGet_click(), with MyGoogleMapURL
being defined as a local variable in that routine.

You're trying to navigate to the URL in sub cmdgotourl_Click, which doesn't
know anything about the variable MyGoogleMapURL.

The fact that you're not getting any error message implies that you have not
told VBA to insist that all variables be defined. Go to the top of the
module. Is there

Option Explicit

as either the 1st or 2nd line? If not, put it there (and in every other
module in your application!)

To have Access automatically put Option Explicit into all future modules, go
into the VB Editor, choose Tools | Options from the menu bar, go to the
Module tab and check the "Require Variable Declaration" in the "Coding
Options" section. (I've never figured out why that isn't the default!).

While you may suddenly need to go and add a bunch of declarations in your
application, it'll save you countless debug hours in the future!

To solve your immediate issue, declare MyGoogleMapURL as a module-level
variable, as opposed to a variable inside a routine, or (probably better)
write it to a text box on the form (the text box doesn't even have to be
visible)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yeah, I can browse to another webpage no problem.

I created a textbox that is unbound, input an address into it, and had
the browser display the page I wanted. I also moved the box to the
proper form using the reference to the subform you stated below.

It's almost as if the string isn't storing anywhere. I tried changing
the textbox to =([strHyp]) and I get a #Name in the textbox.

I input at the end of the GetMapClick a MsgBox to display the hyperlink
and it looks good. Just not passing it to the browser right.

Can you navigate to other websites using your current setup?

To refer to a control on a subform, you need to use

Forms!NameOfParentForm!NameOfSubformControlOnParentForm.Form!NameOfControlOnSubform

Depending on how you created the subform, the name of the subform control
on
the parent form may not be the same as the name of the form being used as
a
subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well, the string looks perfect, but the webbrowser is just not taking
it.

When I copy and paste the string into an actual browser, it works.

Is it because I have the creation of the string in a subform and then
the webpage in another form? Does it have to be on the same page?
Would I just reference the textboxes as
Forms![frmCase].[frmpatient]![txtaddress]?

String looks good though in the patient form!


Douglas J. Steele wrote:
Try using the Replace function:

strHypCri = Replace(strAdd & "+" & strCity & "+" & strState & "+" &
strZip,
" ", "+")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Daniel,

Thanks so much for the response. I understand how that works, but
now
I've run into a problem I think with the hyperlink. The way the
address is displayed in the hyperlink is with a + between each word.
So 111 + Pheasant + Lane .. etc. My txtaddress only shows 111
Pheasant
Lane.

So the URL would read

http://maps.google.com/maps?q=111 Pheasant
Lane+txtcity+txtstate+txtzip. Any way to insert + into the spaces
of
the address so that it comes out right? Or any way to display this
url
into another textbox? I tried to make a textbox with
=[myGoogleMapURL]
in it and I get a #Name.

My code:
Private Sub CmdGet_click()

Dim strAdd As String
Dim strCity As String
Dim strState As String
Dim strZip As String
Dim MyGoogleMapURL As String
Dim strHypPre As String 'Google prefix url
Dim strHypCri As String


strAdd = Me.txtaddress
strCity = Me.txtcity
strState = Me.txtstate
strZip = Me.txtzip


strHypPre = "http://maps.google.com/maps?q="
strHypCri = strAdd & "+" & strCity & "+" & strState & "+" & strZip
MyGoogleMapURL = strHypPre & strHypCri
End Sub

This is on the patient subform of the tabbed record. And then on
the
map tab, I have a button to pass this string to the webbrowser.

Private Sub cmdgotourl_Click()
Me.WebBrowser0.Navigate MyGoogleMapURL
End Sub

Again, thanks for your help!

 

[ScubaSteve]

Just wanted to say thanks for all of your help.

I couldn't get the string to store correctly, but I passed the string
to a textbox and ran the navigate through the textbox and it worked
perfectly!

Thanks so much!

You're absolutely right. The string ISN'T stored anywhere!

You're building MyGoogleMapURL in sub CmdGet_click(), with MyGoogleMapURL
being defined as a local variable in that routine.

You're trying to navigate to the URL in sub cmdgotourl_Click, which doesn't
know anything about the variable MyGoogleMapURL.

The fact that you're not getting any error message implies that you have not
told VBA to insist that all variables be defined. Go to the top of the
module. Is there

Option Explicit

as either the 1st or 2nd line? If not, put it there (and in every other
module in your application!)

To have Access automatically put Option Explicit into all future modules, go
into the VB Editor, choose Tools | Options from the menu bar, go to the
Module tab and check the "Require Variable Declaration" in the "Coding
Options" section. (I've never figured out why that isn't the default!).

While you may suddenly need to go and add a bunch of declarations in your
application, it'll save you countless debug hours in the future!

To solve your immediate issue, declare MyGoogleMapURL as a module-level
variable, as opposed to a variable inside a routine, or (probably better)
write it to a text box on the form (the text box doesn't even have to be
visible)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yeah, I can browse to another webpage no problem.

I created a textbox that is unbound, input an address into it, and had
the browser display the page I wanted. I also moved the box to the
proper form using the reference to the subform you stated below.

It's almost as if the string isn't storing anywhere. I tried changing
the textbox to =([strHyp]) and I get a #Name in the textbox.

I input at the end of the GetMapClick a MsgBox to display the hyperlink
and it looks good. Just not passing it to the browser right.

Can you navigate to other websites using your current setup?

To refer to a control on a subform, you need to use

Forms!NameOfParentForm!NameOfSubformControlOnParentForm.Form!NameOfControlOnSubform

Depending on how you created the subform, the name of the subform control
on
the parent form may not be the same as the name of the form being used as
a
subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Well, the string looks perfect, but the webbrowser is just not taking
it.

When I copy and paste the string into an actual browser, it works.

Is it because I have the creation of the string in a subform and then
the webpage in another form? Does it have to be on the same page?
Would I just reference the textboxes as
Forms![frmCase].[frmpatient]![txtaddress]?

String looks good though in the patient form!


Douglas J. Steele wrote:
Try using the Replace function:

strHypCri = Replace(strAdd & "+" & strCity & "+" & strState & "+" &
strZip,
" ", "+")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Daniel,

Thanks so much for the response. I understand how that works, but
now
I've run into a problem I think with the hyperlink. The way the
address is displayed in the hyperlink is with a + between each word.
So 111 + Pheasant + Lane .. etc. My txtaddress only shows 111
Pheasant
Lane.

So the URL would read

http://maps.google.com/maps?q=111 Pheasant
Lane+txtcity+txtstate+txtzip. Any way to insert + into the spaces
of
the address so that it comes out right? Or any way to display this
url
into another textbox? I tried to make a textbox with
=[myGoogleMapURL]
in it and I get a #Name.

My code:
Private Sub CmdGet_click()

Dim strAdd As String
Dim strCity As String
Dim strState As String
Dim strZip As String
Dim MyGoogleMapURL As String
Dim strHypPre As String 'Google prefix url
Dim strHypCri As String


strAdd = Me.txtaddress
strCity = Me.txtcity
strState = Me.txtstate
strZip = Me.txtzip


strHypPre = "http://maps.google.com/maps?q="
strHypCri = strAdd & "+" & strCity & "+" & strState & "+" & strZip
MyGoogleMapURL = strHypPre & strHypCri
End Sub

This is on the patient subform of the tabbed record. And then on
the
map tab, I have a button to pass this string to the webbrowser.

Private Sub cmdgotourl_Click()
Me.WebBrowser0.Navigate MyGoogleMapURL
End Sub

Again, thanks for your help!