kml from Query

[rexmorgan]

I have a couple of different forms. On one form I have cmd button linked to the following code.

Private Sub Command5_Click()

'export data to text file

Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String
Dim QryOrTblDef As String
Dim iFile As Integer
    QryOrTblDef = "DanielsKMLtest"
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset(QryOrTblDef)
    iFile = FreeFile
    
    Open "//ggw-s-win/home/rex.morgan/My Documents/KML help/KMLTest.kml" For Output Shared As #iFile
    
    Print #iFile, "<?xml version=""1.0"" encoding=""UTF-8""?>"
    Print #iFile, "<kml xmlns=""http://earth.google.com/kml/2.1"">"
    Print #iFile, "<Document>"
    Print #iFile, " <name>KMLTest.kml</name>"
    Print #iFile, " <Folder>"
    Print #iFile, " <name>Spotters</name>"
    Print #iFile, " <open>1</open>"
    Print #iFile, " <Folder>"
    Print #iFile, " <name>Daniels County</name>"
    Print #iFile, " <open>1</open>"
    'Print #iFile, " <Snippet maxLines=""2"">Whatever</Snippet>"
    Print #iFile, " <description><![CDATA[]]></description>"
    With MyRS
        Do Until .EOF
            Print #iFile, " <Placemark>"
            strText = " <description><![CDATA[Name: " & MyRS.Fields(0) & "<br>City: " & MyRS.Fields(1) & "<br>Phone: " & MyRS.Fields(3) & "]]></description>"
            Print #iFile, strText
            
            strText = " <name>" & MyRS.Fields(0) & "</name>"
            Print #iFile, strText
            
        
            
        Print #iFile, "<Point>"
        strText = "<coordinates>" & MyRS.Fields(4) & "</coordinates>"
        Print #iFile, strText
        Print #iFile, " </Point>"
            Print #iFile, " </Placemark>"
            .MoveNext
        Loop
    End With
    Print #iFile, " </Folder>"
    Print #iFile, " </Folder>"
    Print #iFile, " </Document>"
    Print #iFile, "</kml>"
    Close #iFile
    MyRS.Close
    Set MyRS = Nothing
    Set MyDB = Nothing
    Call Shell("explorer.exe " & "z:\My Documents\KML help\KMLTest.kml", vbNormalFocus)
End Sub

That code works fine. However when I try to reuse the code (on a different form with a different query) with a few changes I get the following error.

Run-Time error '3061':
Too few parameters. Expected 2.

When I select debug the line highlighted is the Set MyRS= MyDB.OpenRecordset (QryOrTblDef)

Don't know what is going on here. Any help is greatly appreciated.

 

[HiTechCoach]

I have a couple of different forms. On one form I have cmd button linked to the following code.

Private Sub Command5_Click()

'export data to text file

Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String
Dim QryOrTblDef As String
Dim iFile As Integer
    QryOrTblDef = "DanielsKMLtest"
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset(QryOrTblDef)
    iFile = FreeFile
    
    Open "//ggw-s-win/home/rex.morgan/My Documents/KML help/KMLTest.kml" For Output Shared As #iFile
    
    Print #iFile, "<?xml version=""1.0"" encoding=""UTF-8""?>"
    Print #iFile, "<kml xmlns=""http://earth.google.com/kml/2.1"">"
    Print #iFile, "<Document>"
    Print #iFile, " <name>KMLTest.kml</name>"
    Print #iFile, " <Folder>"
    Print #iFile, " <name>Spotters</name>"
    Print #iFile, " <open>1</open>"
    Print #iFile, " <Folder>"
    Print #iFile, " <name>Daniels County</name>"
    Print #iFile, " <open>1</open>"
    'Print #iFile, " <Snippet maxLines=""2"">Whatever</Snippet>"
    Print #iFile, " <description><![CDATA[]]></description>"
    With MyRS
        Do Until .EOF
            Print #iFile, " <Placemark>"
            strText = " <description><![CDATA[Name: " & MyRS.Fields(0) & "<br>City: " & MyRS.Fields(1) & "<br>Phone: " & MyRS.Fields(3) & "]]></description>"
            Print #iFile, strText
            
            strText = " <name>" & MyRS.Fields(0) & "</name>"
            Print #iFile, strText
            
        
            
        Print #iFile, "<Point>"
        strText = "<coordinates>" & MyRS.Fields(4) & "</coordinates>"
        Print #iFile, strText
        Print #iFile, " </Point>"
            Print #iFile, " </Placemark>"
            .MoveNext
        Loop
    End With
    Print #iFile, " </Folder>"
    Print #iFile, " </Folder>"
    Print #iFile, " </Document>"
    Print #iFile, "</kml>"
    Close #iFile
    MyRS.Close
    Set MyRS = Nothing
    Set MyDB = Nothing
    Call Shell("explorer.exe " & "z:\My Documents\KML help\KMLTest.kml", vbNormalFocus)
End Sub

That code works fine. However when I try to reuse the code (on a different form with a different query) with a few changes I get the following error.

Run-Time error '3061':
Too few parameters. Expected 2.

When I select debug the line highlighted is the Set MyRS= MyDB.OpenRecordset (QryOrTblDef)

Don't know what is going on here. Any help is greatly appreciated.

Is "DanielsKMLtest" a query that uses form references or pop ups as parameters?

The use of form references and pop ups as parameters is not supported in the record source for a DAO Recordset. You will need to use Eval(), modify the QueryDef(), or build the SQL in VBA code and substitute the value(s) into the SQL.

 

[rexmorgan]

"DanielsKMLtest" is a query. The code posted above actually works. I was modifying it for use with a different query.

I did finally accomplish part of what I was trying to. I will post the code shortly for any interested parties. I am still trying to sort out a few other things. Namely, trying to get another subroutine to run inside of this one. I am not sure of exactly how I want to do this however.

I have a form with several command buttons on it. There are a number of combo boxes and text boxes on the form as well. The command buttons launch a query based on the input in the combo boxes or the text boxes, depending on which is selected. But basically the database is a list of people with address, city, county, phone, state, country, lat, long, etc. The command buttons launch queries and append the info gathered from the query into a kml (google earth) file. It is pretty slick.

I still have a lot of cleanup to do in my code. I will post a sample database with the forms after I get it all cleaned up. It will probably take me at least another week or so to get it all straight. Thanks for the reply.

 

[rexmorgan]

I am attaching a sample database. On the form 'SpotterForm' some of the addresses may not map correctly. The reason for this is I had to change names and some addresses are just made up. So it will launch but may come up as address unknown or something like that.

Also on the form 'frmReports' it will create the kml file for viewing in google earth but these are just random points (for protecting personal info) and will not match the address or even the city possibly not even the state.

If you find any of the code does not work please let me know. I have tested it but it always helps to have someone else look at things. I hope this helps out someone else as much as I have been helped on this forum. Again if you have any questions don't hesitate to ask.

 

[HiTechCoach]

Thank you for sharing.

 

[jdraw]

Could you provide the sample as an mdb? Many of us don't have Acc2007.
I'm running Acc2003 on xp.
Thanks.

 

[rexmorgan]

Could you provide the sample as an mdb? Many of us don't have Acc2007.
I'm running Acc2003 on xp.
Thanks.

I am not sure if all of the functionality will be there with the mdb filetype but here goes nothing. Please disregard the login screen that comes up. I am working on this still but to use the functionality of the google earth mapping open up the form titled 'frmReports' there are a number of way to lookup the values in the database. The primary database containing user info is titled 'Spotter'. Some of the queries are obsolete now. I hope this helps explain things, if not do not hesitate to ask and I will do my best to help.