Yahoo maps is great at mapping out a route you entered through the browser, but what if you need to map the addresses using a pre-defined route in Access? This tip will show you how to build the URL on the fly and then pass it along to the default browser on your PC. Note: for the same technique using Google maps please visit here.
Here’s the code:
Private Sub cmdGoogleMap_Click()
Dim strHyperlink As String
Dim Company As clsCompanyInfo
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim i As Byte
‘Starting address in this case is the address of the company, just replace the code with whatever address you wish, here I use the Company class to get the company’s address.
Set Company = New clsCompanyInfo
strHyperlink = «http://maps.yahoo.com/dd#mvt=m&q1=» & .Address & «, » & .City & «, » & Val(.Zip)
‘Now I need to get the list of addresses for the route, in this case it’s CrewID defined on my firm and for a particular day, just replace strSQL with your own sql code to fetch the addresses you need to map:
strSQL = «SELECT Address, City, State, Zip » & _
«FROM Customers INNER JOIN jobs ON Customers.CustomerID = Jobs.CustomerID » & _
«WHERE JobDate = ‘» & Me.txtScheduleDate & «‘ And Jobs.CrewID = » & Me.cboMapCrew & » Order By Jobs.Position»
‘OpenMyRecordset is a custom procedure I use to open all of my ADODB recordsets using a default static cursor, replace with your code to open the cursor:
OpenMyRecordset rs, strSQL
i = 2
Do While .EOF = False
strHyperlink = strHyperlink & IIf(IsNull(!Address), «», «&q» & i & «=» & !Address & «,» & !State & «,» & Val(!Zip))
i = i + 1
Set rs = Nothing
Route optimization is a different matter all together
As of this writing there is no way to due route optimization for free that I’m aware of, usually you have to pay for a web service that does that.