?
Solved

MS Access VBA code to launch Cyberhomes valuation...

Posted on 2009-04-27
11
Medium Priority
?
350 Views
Last Modified: 2013-11-28
Hi.
I need to have a button which will open the Cyberhomes.com website with the property information drawn from my form's controls.
The controls are: Address1, City, State, Zip

The code below works to launch the Google map, but I don't know how to modify it to launch the Cyberhomes valuation...

Thanks!
Private Sub Label41_Click()
     On Error Resume Next
     Me.Refresh
     Access.FollowHyperlink "http://www.google.com/maps?q=" & _
                Replace(Me.Address1.Value, " ", "+") & "+" & _
                Replace(Me.City.Value, " ", "+") & "+" & _
                Replace(Me.State.Value, " ", "+") & "+" & _
                Replace(Me.Zip.Value, " ", "+") & "&sa=X&oi=map&ct=title"
End Sub

Open in new window

0
Comment
Question by:Slicer58
  • 6
  • 5
11 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24243042
Replace the http link in the line below with the URL (Universal Resource Locator) for the Cyberhomes web site.

"http://www.google.com/maps?q=
0
 

Author Comment

by:Slicer58
ID: 24243062
Already tried that because it was so very obvious.  Didn't work.
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24243090
Does the Cyberhome site work like Google maps, that is does it accept address + city + state + ZIP in the URL?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:Slicer58
ID: 24243100
I believe it's more complex than that, although I can see the address in the URL.  It would make more sense to you if you actually visited the site and then tried the code out yourself.
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24243115
I would do that but unfortunately my corporate firewall won't let me. Can you post an example of the URL that works?
0
 

Author Comment

by:Slicer58
ID: 24243180
Here are two URLs from the site:
http://www.cyberhomes.com/homes-tustin-ca-92782/2947playerln/61214815.aspx
http://www.cyberhomes.com/homes-eastorange-nj-07017/160glenwoodave/10371460.aspx

Below is (I believe) an HTTP Post that works from our CRM.  Not sure how it would be translated into a VBA expression for Access 2007:  
http://cyberhomes.com/searchredirect.aspx?address={prop_street}&city={prop_city}&state={prop_state}


0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24243291
You might want to try something like this:

Private Sub Label41_Click()
     Dim strStreet As String, astrAddress() As String
     Dim intAddr As Integer
     ' split the address on spaces
     astrAddress = Split(Me.Address1.Value, " ")
     ' if the first part of the address is numeric
     If IsNumeric(astrAddress(0)) Then
        ' loop through the remaining parts of the address
        For intAddr = 1 To UBound(astrAddress, 1)
            ' add this part to the street
            strStreet = astrAddress(intAddr) & " "
        Next intAddr
     Else
        ' loop through all parts of the address
        For intAddr = 0 To UBound(astrAddress, 1)
            ' add this part to the street
            strStreet = astrAddress(intAddr) & " "
        Next intAddr
     End If
     strStreet = Trim(strStreet)
     On Error Resume Next
     Me.Refresh
    Access.FollowHyperlink "http://cyberhomes.com/searchredirect.aspx?address={" & _
        strStreet & "}&city={" & Me.City.Value & "}&state={" & Me.State.Value & "}"
End Sub

Open in new window

0
 

Author Comment

by:Slicer58
ID: 24243527
Thanks for going to all that trouble.  I tried it and kept getting error messages where it didn't recognize the fields.  I took out the brackets and that worked for the city and state, but it didn't recognize the street data.  So I pared it down to the following and it works fine.  My only question now is, can you help me split this code into the easy to read lines (I'm not sure where to put the ampersands and quotes).


Private Sub Label30_Click()
     On Error Resume Next
     Me.Refresh
    Access.FollowHyperlink "http://cyberhomes.com/searchredirect.aspx?address=" & Me.Address1.Value & "&city=" & Me.City.Value & "&state=" & Me.State.Value & "&zip=" & Me.Zip.Value & ""
End Sub
0
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 200 total points
ID: 24243563
Sure, I will be glad to do that.

Private Sub Label30_Click()
    On Error Resume Next
    Me.Refresh
    Access.FollowHyperlink _
        "http://cyberhomes.com/searchredirect.aspx?" & _
        "address=" & Me.Address1.Value & _
        "&city=" & Me.City.Value & _
        "&state=" & Me.State.Value & _
        "&zip=" & Me.Zip.Value & ""
End Sub

Open in new window

0
 

Author Closing Comment

by:Slicer58
ID: 31574924
Thanks for your help!
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24244570
You are very welcome.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question