Link to MapQuest

Posted on 2005-05-09
Last Modified: 2008-03-04
I created a database which stores contacts (information gathered from business cards).  To query, users enter the data into fields on a standard form and then presented results via another form.

I wish to place a button next to the contacts address.  when pressed, the button would link to mapquest and provide directions.  The originating point will alway be the same soe it could be hard coded, the only dynamic portion would be the destination (which will come from the database)

Any Ideas?
Question by:chaddcarr
    LVL 6

    Expert Comment


    the above link will take you to directions from:
    101 StartAddress CityName,NC 27615

    101 EndAddress CityName,NC 27615

    try using that to dynamically build your link, remember, anywhere there is a space in your address you have to put "+" in instead of that, so you will have to parse out all of the spaces and add + instead
    LVL 6

    Expert Comment

    LVL 6

    Accepted Solution

    Here's the all the code that I used, it works! I tried it.  You may want to try it for different regions of the country.

    Private Sub btnGetDirections_Click()
    Dim link As String
    Dim address As String
    Dim city As String
    Dim state As String
    Dim zip As String
    Dim startaddress As String
    Dim startcity As String
    Dim startstate As String
    Dim startzip As String
    startaddress = "101+Start+Address+Ln"
    startcity = "Starting+City"
    startstate = "NC"
    startzip = "27587"
    address = DeleteWithin(Me.txtAddress, " ")
    city = DeleteWithin(Me.txtCity, " ")
    state = Me.txtState
    zip = Me.txtZip
    link = "" & startaddress & "&1c=" & startcity & "&1s=" & startstate & "&1z=" & startzip & "&2ahXX=&2y=US&2a=" & address & "&2c=" & city & "&2s=" & state & "&2z=" & zip
    FollowHyperlink link, , True, False
    End Sub

    Private Function DeleteWithin(ByVal TheString As String, ByVal BadTerm As String)
    If InStr(TheString, BadTerm) > 0 Then
        Dim oldlength As Integer, totallength As Integer, innerstringpos As Integer
        Dim leftside As String, rightside As String
        oldlength = Len(BadTerm)
        totallength = Len(TheString)
        innerstringpos = InStr(TheString, BadTerm)
        leftside = Left(TheString, innerstringpos - 1)
        rightside = Right(TheString, totallength - (innerstringpos + oldlength - 1))
        TheString = leftside & "+" & rightside
        DeleteWithin = TheString
    End If
    End Function
    LVL 6

    Expert Comment

    I would appreciate you upping the points on this question for all the work that was put into this answer, but it's up to you

    Author Comment

    No problem with the points.... however, one more question.  Am I simply cutting and pasting the above script into the code of a button?

    LVL 6

    Expert Comment

    That's what I'd do, I had that in a button click event, I also have a version of it that will pull both the start and the end date if you want both of them.
    LVL 6

    Expert Comment

    Let me clarify that...the function "DeleteWithin" needs to be by itself, it can't be within a button click event procedure.  only what you see between the
    "Private Sub btnGetDirections_Click()"
    and the
    "End Sub"

    should go in a button click.  Let me know if you have any more questions.

    by the way: the sub is assuming that you have 4 textboxes called:

    if you have something else let me know.  If I were you I'd make state a combobox of all the state names, to prevent errors.  if you do that, just name the combobox txtstate and it will all still work the same as long as the bound column will return the two letter equivalent of the state

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now