KML file with several data fields (1 Viewer)

[fsc23]

Hi, I am trying to create a kml file after an Access query/table that contains one or more geographic points. The associated data is listed in the subsequent columns/fields although they do not always contain values and can be more than 150. I would like the resulting kml file to produce a box with a table or list that is populated only with those fields that actually have a value and that shows when one clicks the corresponding icon in GoogleEarth as it is doing now, except that my example only has two fields that I typed myself. Is this possible? What I am trying to avoid is the typing of each and every field in the description part. Thanks for your help and suggestions.

The code I have so far is as follows:

Option Compare Database
Private Sub Command33_Click()
DoCmd.OpenQuery "qryCoords_Values"
Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim strText As String
Dim QryDef As String
QryDef = "qryCoords_Values"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(QryDef)
Open "K:\Map.kml" For Output Shared As #1
Print #1, "<?xml version=""1.0"" encoding= ""UTF-8""?>"
Print #1, "<kml xmlns="" .... earth.google.com/kml/2.1"">"
Print #1, "<Document>"
Print #1, " <name>Map.kml</name>"
With MyRS
Do Until .EOF
Print #1, " <Placemark>"
Print #1, strText
Print #1, " <name>" & MyRS.Fields(0) & MyRS.Fields(1) & "</name>"
Print #1, strText
Print #1, " <visibility>1</visibility>"
Print #1, strText
Print #1, " <description><![CDATA[Value1 = " & MyRS.Fields(7) & "<br>" & "Value2 = " & MyRS.Fields(8) & "]]></description> "     'This is where I need to include all the data fields.
Print #1, strText
Print #1, " <Point>"
Print #1, strText
Print #1, " <coordinates>" & MyRS.Fields(2) & "," & MyRS.Fields(3) & ", 0</coordinates>"
Print #1, " </Point>"
Print #1, " </Placemark>"
.MoveNext
Loop
End With
Print #1, "</Document>"
Print #1, "</kml>"
Close #1
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
Dim retval
retval = Shell("RUNDLL32.EXE URL.DLL,FileProtocolHandler " & "K:\Map.kml", vbNormalFocus)
End Sub

 

[gemma-the-husky]

i would try and do this with a standard procedure, using recursion - as a KML/XML file is essentially recursive in nature

basically, you want to let the structure build itself so it does this sort of thing, with a recursive code

for each item to output
   processtag "roottag"
next

sub processtag(thistag as string)
write opening tag
if leaftag then 
     write details
     else for each subtag
[COLOR="Red"]
          'here is the recursion - the procedure calls itself for the next level of tags[/COLOR]
          processtag "subtag"
     end if
write closing tag

so the things to consider in this scenario
- how to determine a "leaftag" - ie a final tag with a value
- how to determine a "subroot tag" - ie a tag that has further descendants.

and, you need a table, or some other structure to determine the kml "tree" structure, and then you can interrogate this structure to determine the status of the tag.

This way, all the code becomes re-usable - you just have to design the "master table/structure" - and your standard code does the rest.