Select Query to Update (2 Viewers)
- Thread starter Jord
- Start date
Hi,
I got it to work. Glad you reminded me to post what I did. Im not sure if this was the most efficient way, but it works.
Jord
I got it to work. Glad you reminded me to post what I did. Im not sure if this was the most efficient way, but it works.
Code:
Private Sub Command257_Click()
Dim dbsMyMap As DAO.Database
Dim rstPositions As DAO.Recordset
Dim qdf As QueryDef
Dim prm As Parameter
Dim strURLBeg As String
Dim strURLEnd As String
Dim strMyURL As String
Dim strURL As String
Set dbsMyMap = CurrentDb()
Set qdf = dbsMyMap.QueryDefs("Top10 Closest")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rstPositions = qdf.OpenRecordset(dbOpenDynaset)
If rstPositions.EOF Then Exit Sub
strMyURL = ""
With rstPositions
Do Until .EOF
MyLat = rstPositions![CUSTLAT]
MyLong = rstPositions![CUSTLONG]
strMyURL = strMyURL & "+to:" & MyLat & "," & MyLong
.MoveNext
Loop
End With
strStrt = DLookup("[CUSTLAT]", "Customers", "[CUSTID] = Form![StartID]") & "," & DLookup("[CUSTLONG]", "Customers", "[CUSTID] = Form![StartID]") & "%20(" & DLookup("[CUSTNAME]", "Customers", "[CUSTID] = Form![StartID]") & ")"
strURLBeg = "<body scroll=""no""><iframe width=""478"" height=""570"" frameborder=""0"" scrolling=""no"" marginheight=""0"" marginwidth=""0"" src=""http://maps.google.co.uk/maps?f=d&source=s_d&saddr=" & strStrt
strURLEnd = "&hl=en&aq=&doflg=ptk&mra=ls&ie=UTF8&t=m&z=7&output=embed""""></iframe><br /><body />"
strURL = strURLBeg & strMyURL & strURLEnd
' Create output file
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\map1.htm", True)
a.WriteLine ([strURL])
a.Close
' View description in WebBrowser
If Len([strURL]) > 0 Then
ClosestWeb.Navigate URL:="file:///C:/Map1.htm"
Else
ClosestWeb.Navigate URL:="www.google.co.uk"
End If
rstPositions.Close
dbsMyMap.Close
Set dbsMyMap = Nothing
Set rstPositions = Nothing
End SubJord