Creating a custom text export file (inc. header & footer) from a q

[Douglas J. Steele]

Not certain I understand what you mean "a criteria control wth the criteria
'Run No' based on a field called [Run_No]"

If you run the query manually, does it prompt the user to enter the
criteria?

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
' etc.
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

thanks for that article, I understand what it is implying, but don't
really
get how to implement the suggestions. It seems to me that it relates more
to
forms controls than underlying query criteria. I tried this: (you'll
probably
smile at my naivety...), but i can't figure out what or even how to
contextualise the code to reference my criteria field:

Set rsR = CurrentDb.OpenRecordset("Generate_KML", dbOpenSnapshot)
qdf![Generate_KML.Run_No] = [Run No]

but it didn't work.

Unlike that articles suggestion, I am not accessing a form's control, but
a
criteria control with the criteria 'Run No' based on a field called
[Run_No]

http://www.mvps.org/access/queries/qry0013.htm

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas, John.

Now that I have the code working, how can I adapt it to take a criteria
from
the underlying query.

Each set of addresses (16 addresses per set) within the query is based
on
a
Main Record field called [Run_No]. But when I try to use this criteria
in
the
query and then run your code, I get the following error message:

Run-time error '3061';
Too few parameters. Expected 1.

and the VBA throws a highlighted yellow error:

Set rsR = CurrentDb.OpenRecordset("Generate_KML", dbOpenSnapshot)



:

Print #lngFN, "<?xml version=""1.0"" encoding=""UTF-8""?>"

Is the expected string supposed to have two double quotes on either
side
of
UTF-8? If so, you need

Print #lngFN, "<?xml version=""1.0"" encoding=""""UTF-8""""?>"

(The rule is everytime you want a quote to appear inside a quoted
string,
you need to double the quotes.)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

Can you help advise on this VBA problem please?

When I paste or type this line:

Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"

I get

Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"

the 1.0 becomes 1#

I can't seem to get the VBA window to accept John's code, weird or
some
kind
of switch that is turned off?



:

Replace

Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

with

Set rsR = CurrentDb.Open("Generate_KML")
Do Until rsR.EOF = True
Print #lngFN, rsR.Fields("KML_Address")
rsR.MoveNext
Loop
Close rsR

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


help?...

:

Douglas/John,

that did the trick...

but I have a secondary problem (that i think is of my
making...).
The
.kml
file only contains the first address in the query, and I think
it's
because
of my badly worded sentence 'I have an address query which
ouputs a
single
field to text strings for
Google Earth .kml file format parameters.' in my opening post.
What
I
really
meant was it is a single field (among other fields in the
query),
but
that it
contains multiple records/data.

eg:

Main St, London, W1
South St, London, W2
East St, London, SE1

Is it possible to get the routine to print All the data for that
field
to
the kml file?





:

That's supposed to be rsR.Close (the Close #lngFN is correct
though)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
John,

I now get this error in the line: Close rsR:
(highlighting the word 'Close')

Compile error:
Type mismatch:



:

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")




On Thu, 10 May 2007 16:41:00 -0700, efandango

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; "
encoding=""UTF-8""?>"
Print #lngFN, "<kml
xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not
found'
on
the
word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong
reference,
so I
was
worried
that the query it was calling was two seperate words, so I
changed
it to
one
whole_word. But it still reports the same error message.

Can you help me with this?

:

There are many ways of skinning this cat. I'd probably
do
something
like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0"
encoding=""UTF-8""?>"
Print #lngFN, "<kml
xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

On Thu, 10 May 2007 10:35:01 -0700, efandango

I have an address query which ouputs a single field to
text
strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and
footer
text
to be
inserted. Does anyone know how I can pre-insert the
Header
and
Footer
into a
standard text file, and then save it with a '.kml'
extension,
for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

 

[efandango]

Douglas,

Yes, if i run it manually, it prompts the user for the Run No.

Your code?, do i paste it in, and then where you continue with 'etc', paste
in that part from my exisitng code, changing your 'Rs' references to John's
'rsR' references?

Not certain I understand what you mean "a criteria control wth the criteria
'Run No' based on a field called [Run_No]"

If you run the query manually, does it prompt the user to enter the
criteria?

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
' etc.
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

thanks for that article, I understand what it is implying, but don't
really
get how to implement the suggestions. It seems to me that it relates more
to
forms controls than underlying query criteria. I tried this: (you'll
probably
smile at my naivety...), but i can't figure out what or even how to
contextualise the code to reference my criteria field:

Set rsR = CurrentDb.OpenRecordset("Generate_KML", dbOpenSnapshot)
qdf![Generate_KML.Run_No] = [Run No]

but it didn't work.

Unlike that articles suggestion, I am not accessing a form's control, but
a
criteria control with the criteria 'Run No' based on a field called
[Run_No]

http://www.mvps.org/access/queries/qry0013.htm

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas, John.

Now that I have the code working, how can I adapt it to take a criteria
from
the underlying query.

Each set of addresses (16 addresses per set) within the query is based
on
a
Main Record field called [Run_No]. But when I try to use this criteria
in
the
query and then run your code, I get the following error message:

Run-time error '3061';
Too few parameters. Expected 1.

and the VBA throws a highlighted yellow error:

Set rsR = CurrentDb.OpenRecordset("Generate_KML", dbOpenSnapshot)



:

Print #lngFN, "<?xml version=""1.0"" encoding=""UTF-8""?>"

Is the expected string supposed to have two double quotes on either
side
of
UTF-8? If so, you need

Print #lngFN, "<?xml version=""1.0"" encoding=""""UTF-8""""?>"

(The rule is everytime you want a quote to appear inside a quoted
string,
you need to double the quotes.)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

Can you help advise on this VBA problem please?

When I paste or type this line:

Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"

I get

Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"

the 1.0 becomes 1#

I can't seem to get the VBA window to accept John's code, weird or
some
kind
of switch that is turned off?



:

Replace

Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

with

Set rsR = CurrentDb.Open("Generate_KML")
Do Until rsR.EOF = True
Print #lngFN, rsR.Fields("KML_Address")
rsR.MoveNext
Loop
Close rsR

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


help?...

:

Douglas/John,

that did the trick...

but I have a secondary problem (that i think is of my
making...).
The
.kml
file only contains the first address in the query, and I think
it's
because
of my badly worded sentence 'I have an address query which
ouputs a
single
field to text strings for
Google Earth .kml file format parameters.' in my opening post.
What
I
really
meant was it is a single field (among other fields in the
query),
but
that it
contains multiple records/data.

eg:

Main St, London, W1
South St, London, W2
East St, London, SE1

Is it possible to get the routine to print All the data for that
field
to
the kml file?





:

That's supposed to be rsR.Close (the Close #lngFN is correct
though)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
John,

I now get this error in the line: Close rsR:
(highlighting the word 'Close')

Compile error:
Type mismatch:



:

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")




On Thu, 10 May 2007 16:41:00 -0700, efandango

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; "
encoding=""UTF-8""?>"
Print #lngFN, "<kml
xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not
found'
on
the
word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong
reference,
so I
was
worried
that the query it was calling was two seperate words, so I
changed
it to

 

[efandango]

Douglas,

I pasted your code (with subtle changes to the 'rs' references...:

Private Sub Test_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub

When I run it, the dialog box for the user Run No pops up, but then I hit
OK, i get this error message:

Run-time error '3265':
Item not found in this collection.

and the vba highlights this line in the editor:

qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]


I tried that line to just say:
qdf![Run_No] = [Run_No]

but still the same error message.

 

[smash boyz]

yo whats craccin you will get every thing together

Douglas,

I pasted your code (with subtle changes to the 'rs' references...:

Private Sub Test_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub

When I run it, the dialog box for the user Run No pops up, but then I hit
OK, i get this error message:

Run-time error '3265':
Item not found in this collection.

and the vba highlights this line in the editor:

qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]


I tried that line to just say:
qdf![Run_No] = [Run_No]

but still the same error message.

I have an address query which ouputs a single field to text strings for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to be
inserted. Does anyone know how I can pre-insert the Header and Footer
into a
standard text file, and then save it with a '.kml' extension, for example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[Douglas J. Steele]

Sorry, my fault. I switched what I was going to tell you part way through,
and missed a correction.

It should have been:

qdf.Parameters("Run_No") = strRun_No

(or you could use qdf![Run_No] = strRun_No)

However, given the error you're getting, your parameter obviously isn't
named "Run_No".

What's the actual SQL of your query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I pasted your code (with subtle changes to the 'rs' references...:

Private Sub Test_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub

When I run it, the dialog box for the user Run No pops up, but then I hit
OK, i get this error message:

Run-time error '3265':
Item not found in this collection.

and the vba highlights this line in the editor:

qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]


I tried that line to just say:
qdf![Run_No] = [Run_No]

but still the same error message.

I have an address query which ouputs a single field to text strings for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to be
inserted. Does anyone know how I can pre-insert the Header and Footer
into a
standard text file, and then save it with a '.kml' extension, for example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Douglas,

Here is my sql: (I am using 'Run No' without the underscore for the field
[Run_No] with the underscore.



SELECT [Points with Run No].Run_No, [Points with Run No].Run_Point_Address_A
AS Address, [Points with Run No].Run_Point_Venue_A AS Venue,
"<Placemark><description>" & [Venue] & "</description><address>" & [address]
& ", London, UK </address><name>" & [address] & "</name></Placemark>" AS
KML_Address
FROM [Points with Run No]
WHERE ((([Points with Run No].Run_No)=[Run No]));

Sorry, my fault. I switched what I was going to tell you part way through,
and missed a correction.

It should have been:

qdf.Parameters("Run_No") = strRun_No

(or you could use qdf![Run_No] = strRun_No)

However, given the error you're getting, your parameter obviously isn't
named "Run_No".

What's the actual SQL of your query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I pasted your code (with subtle changes to the 'rs' references...:

Private Sub Test_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub

When I run it, the dialog box for the user Run No pops up, but then I hit
OK, i get this error message:

Run-time error '3265':
Item not found in this collection.

and the vba highlights this line in the editor:

qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]


I tried that line to just say:
qdf![Run_No] = [Run_No]

but still the same error message.

I have an address query which ouputs a single field to text strings for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to be
inserted. Does anyone know how I can pre-insert the Header and Footer
into a
standard text file, and then save it with a '.kml' extension, for example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Douglas,

I tried both lines (seperately), but neither worked.

they both throw the following error:

'Item not found in this collection'

I am using 'Run No' as my parameter name without the underscore for the
actual field, [Run_No] with the underscore.

Here is my SQL (with the parameter request)

SELECT [Points with Run No].Run_No, [Points with Run No].Run_Point_Address_A
AS Address, [Points with Run No].Run_Point_Venue_A AS Venue,
"<Placemark><description>" & [Venue] & "</description><address>" & [address]
& ", London, UK </address><name>" & [address] & "</name></Placemark>" AS
KML_Address
FROM [Points with Run No]
WHERE ((([Points with Run No].Run_No)=[Run No]));

Sorry, my fault. I switched what I was going to tell you part way through,
and missed a correction.

It should have been:

qdf.Parameters("Run_No") = strRun_No

(or you could use qdf![Run_No] = strRun_No)

However, given the error you're getting, your parameter obviously isn't
named "Run_No".

What's the actual SQL of your query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I pasted your code (with subtle changes to the 'rs' references...:

Private Sub Test_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub

When I run it, the dialog box for the user Run No pops up, but then I hit
OK, i get this error message:

Run-time error '3265':
Item not found in this collection.

and the vba highlights this line in the editor:

qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]


I tried that line to just say:
qdf![Run_No] = [Run_No]

but still the same error message.

I have an address query which ouputs a single field to text strings for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to be
inserted. Does anyone know how I can pre-insert the Header and Footer
into a
standard text file, and then save it with a '.kml' extension, for example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[Douglas J. Steele]

Since your parameter doesn't have an underscore in it, try:

qdf.Parameters("Run No") = strRun_No

or

qdf![Run No] = strRun _No

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I tried both lines (seperately), but neither worked.

they both throw the following error:

'Item not found in this collection'

I am using 'Run No' as my parameter name without the underscore for the
actual field, [Run_No] with the underscore.

Here is my SQL (with the parameter request)

SELECT [Points with Run No].Run_No, [Points with Run
No].Run_Point_Address_A
AS Address, [Points with Run No].Run_Point_Venue_A AS Venue,
"<Placemark><description>" & [Venue] & "</description><address>" &
[address]
& ", London, UK </address><name>" & [address] & "</name></Placemark>" AS
KML_Address
FROM [Points with Run No]
WHERE ((([Points with Run No].Run_No)=[Run No]));

Sorry, my fault. I switched what I was going to tell you part way
through,
and missed a correction.

It should have been:

qdf.Parameters("Run_No") = strRun_No

(or you could use qdf![Run_No] = strRun_No)

However, given the error you're getting, your parameter obviously isn't
named "Run_No".

What's the actual SQL of your query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I pasted your code (with subtle changes to the 'rs' references...:

Private Sub Test_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub

When I run it, the dialog box for the user Run No pops up, but then I
hit
OK, i get this error message:

Run-time error '3265':
Item not found in this collection.

and the vba highlights this line in the editor:

qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]


I tried that line to just say:
qdf![Run_No] = [Run_No]

but still the same error message.




:

I have an address query which ouputs a single field to text strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to
be
inserted. Does anyone know how I can pre-insert the Header and Footer
into a
standard text file, and then save it with a '.kml' extension, for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Douglas,

this line: qdf.Parameters("Run No") = strRun_No

comes back with 'Bad file name or number (runtime error 52)

your other line 'qdf![Run No] = strRun _No' redlines in the VBA window

Since your parameter doesn't have an underscore in it, try:

qdf.Parameters("Run No") = strRun_No

or

qdf![Run No] = strRun _No

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I tried both lines (seperately), but neither worked.

they both throw the following error:

'Item not found in this collection'

I am using 'Run No' as my parameter name without the underscore for the
actual field, [Run_No] with the underscore.

Here is my SQL (with the parameter request)

SELECT [Points with Run No].Run_No, [Points with Run
No].Run_Point_Address_A
AS Address, [Points with Run No].Run_Point_Venue_A AS Venue,
"<Placemark><description>" & [Venue] & "</description><address>" &
[address]
& ", London, UK </address><name>" & [address] & "</name></Placemark>" AS
KML_Address
FROM [Points with Run No]
WHERE ((([Points with Run No].Run_No)=[Run No]));

Sorry, my fault. I switched what I was going to tell you part way
through,
and missed a correction.

It should have been:

qdf.Parameters("Run_No") = strRun_No

(or you could use qdf![Run_No] = strRun_No)

However, given the error you're getting, your parameter obviously isn't
named "Run_No".

What's the actual SQL of your query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I pasted your code (with subtle changes to the 'rs' references...:

Private Sub Test_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub

When I run it, the dialog box for the user Run No pops up, but then I
hit
OK, i get this error message:

Run-time error '3265':
Item not found in this collection.

and the vba highlights this line in the editor:

qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]


I tried that line to just say:
qdf![Run_No] = [Run_No]

but still the same error message.




:

I have an address query which ouputs a single field to text strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to
be
inserted. Does anyone know how I can pre-insert the Header and Footer
into a
standard text file, and then save it with a '.kml' extension, for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Douglas

I noticed your 2nd option 'qdf![Run No] = strRun _No'
had a space before the underscore '_No'

but it still comes back with the same error line ('Bad file name or number
(runtime error 52) as your first option


Here is my Current Code:


Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub

Douglas,

this line: qdf.Parameters("Run No") = strRun_No

comes back with 'Bad file name or number (runtime error 52)

your other line 'qdf![Run No] = strRun _No' redlines in the VBA window

Since your parameter doesn't have an underscore in it, try:

qdf.Parameters("Run No") = strRun_No

or

qdf![Run No] = strRun _No

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I tried both lines (seperately), but neither worked.

they both throw the following error:

'Item not found in this collection'

I am using 'Run No' as my parameter name without the underscore for the
actual field, [Run_No] with the underscore.

Here is my SQL (with the parameter request)

SELECT [Points with Run No].Run_No, [Points with Run
No].Run_Point_Address_A
AS Address, [Points with Run No].Run_Point_Venue_A AS Venue,
"<Placemark><description>" & [Venue] & "</description><address>" &
[address]
& ", London, UK </address><name>" & [address] & "</name></Placemark>" AS
KML_Address
FROM [Points with Run No]
WHERE ((([Points with Run No].Run_No)=[Run No]));






:

Sorry, my fault. I switched what I was going to tell you part way
through,
and missed a correction.

It should have been:

qdf.Parameters("Run_No") = strRun_No

(or you could use qdf![Run_No] = strRun_No)

However, given the error you're getting, your parameter obviously isn't
named "Run_No".

What's the actual SQL of your query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I pasted your code (with subtle changes to the 'rs' references...:

Private Sub Test_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub

When I run it, the dialog box for the user Run No pops up, but then I
hit
OK, i get this error message:

Run-time error '3265':
Item not found in this collection.

and the vba highlights this line in the editor:

qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]


I tried that line to just say:
qdf![Run_No] = [Run_No]

but still the same error message.




:

I have an address query which ouputs a single field to text strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to
be
inserted. Does anyone know how I can pre-insert the Header and Footer
into a
standard text file, and then save it with a '.kml' extension, for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Sorry, I forgot to add that this is the line that now highlight errors with
''Bad file name or number (runtime error 52)

Print #lngFN, rs.Fields("KML_Address")

Douglas

I noticed your 2nd option 'qdf![Run No] = strRun _No'
had a space before the underscore '_No'

but it still comes back with the same error line ('Bad file name or number
(runtime error 52) as your first option


Here is my Current Code:


Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub

Douglas,

this line: qdf.Parameters("Run No") = strRun_No

comes back with 'Bad file name or number (runtime error 52)

your other line 'qdf![Run No] = strRun _No' redlines in the VBA window

Since your parameter doesn't have an underscore in it, try:

qdf.Parameters("Run No") = strRun_No

or

qdf![Run No] = strRun _No

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I tried both lines (seperately), but neither worked.

they both throw the following error:

'Item not found in this collection'

I am using 'Run No' as my parameter name without the underscore for the
actual field, [Run_No] with the underscore.

Here is my SQL (with the parameter request)

SELECT [Points with Run No].Run_No, [Points with Run
No].Run_Point_Address_A
AS Address, [Points with Run No].Run_Point_Venue_A AS Venue,
"<Placemark><description>" & [Venue] & "</description><address>" &
[address]
& ", London, UK </address><name>" & [address] & "</name></Placemark>" AS
KML_Address
FROM [Points with Run No]
WHERE ((([Points with Run No].Run_No)=[Run No]));






:

Sorry, my fault. I switched what I was going to tell you part way
through,
and missed a correction.

It should have been:

qdf.Parameters("Run_No") = strRun_No

(or you could use qdf![Run_No] = strRun_No)

However, given the error you're getting, your parameter obviously isn't
named "Run_No".

What's the actual SQL of your query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I pasted your code (with subtle changes to the 'rs' references...:

Private Sub Test_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub

When I run it, the dialog box for the user Run No pops up, but then I
hit
OK, i get this error message:

Run-time error '3265':
Item not found in this collection.

and the vba highlights this line in the editor:

qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No]


I tried that line to just say:
qdf![Run_No] = [Run_No]

but still the same error message.




:

I have an address query which ouputs a single field to text strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to
be
inserted. Does anyone know how I can pre-insert the Header and Footer
into a
standard text file, and then save it with a '.kml' extension, for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[Brendan Reynolds]

The error message is, I believe, complaining about the value of the variable
'lngFN'. I don't see, in the posted code, where this variable is declared,
or where a value is assigned to it, or where the file gets opened for
output?

In other words, unless this is a global variable that is declared and
initialized elsewhere, the code seems to be missing something like this ...

Dim lngFN As Long

lngFN = FreeFile
Open "C:\SomeFolder\SomeFile.txt" For Output As #lngFN

You'll also want to close the file when you've finished writing to it ...

Close #lngFN

--
Brendan Reynolds

Sorry, I forgot to add that this is the line that now highlight errors
with
''Bad file name or number (runtime error 52)

Print #lngFN, rs.Fields("KML_Address")

Douglas

I noticed your 2nd option 'qdf![Run No] = strRun _No'
had a space before the underscore '_No'

but it still comes back with the same error line ('Bad file name or
number
(runtime error 52) as your first option


Here is my Current Code:


Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub

Douglas,

this line: qdf.Parameters("Run No") = strRun_No

comes back with 'Bad file name or number (runtime error 52)

your other line 'qdf![Run No] = strRun _No' redlines in the VBA window



:

Since your parameter doesn't have an underscore in it, try:

qdf.Parameters("Run No") = strRun_No

or

qdf![Run No] = strRun _No

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I tried both lines (seperately), but neither worked.

they both throw the following error:

'Item not found in this collection'

I am using 'Run No' as my parameter name without the underscore for
the
actual field, [Run_No] with the underscore.

Here is my SQL (with the parameter request)

SELECT [Points with Run No].Run_No, [Points with Run
No].Run_Point_Address_A
AS Address, [Points with Run No].Run_Point_Venue_A AS Venue,
"<Placemark><description>" & [Venue] & "</description><address>" &
[address]
& ", London, UK </address><name>" & [address] &
"</name></Placemark>" AS
KML_Address
FROM [Points with Run No]
WHERE ((([Points with Run No].Run_No)=[Run No]));






:

Sorry, my fault. I switched what I was going to tell you part way
through,
and missed a correction.

It should have been:

qdf.Parameters("Run_No") = strRun_No

(or you could use qdf![Run_No] = strRun_No)

However, given the error you're getting, your parameter obviously
isn't
named "Run_No".

What's the actual SQL of your query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I pasted your code (with subtle changes to the 'rs'
references...:

Private Sub Test_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] =
[Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub

When I run it, the dialog box for the user Run No pops up, but
then I
hit
OK, i get this error message:

Run-time error '3265':
Item not found in this collection.

and the vba highlights this line in the editor:

qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] =
[Run_No]


I tried that line to just say:
qdf![Run_No] = [Run_No]

but still the same error message.




:

I have an address query which ouputs a single field to text
strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer
text to
be
inserted. Does anyone know how I can pre-insert the Header and
Footer
into a
standard text file, and then save it with a '.kml' extension,
for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Brendan,

That worked.

It seems that while making changes to the code after Douglas's helpful
input, I missed out some of John's essential code snippets. Following your
suggestion, I went back to John's code, and incorporated them into Douglas's
latter code suggestions, and it all worked in the end. So a HUGE thank you to
John, Douglas and your good self for your help and guidance on this problem.

For the record, here is the succesful code that will allow the user to
choose a Run Number for a set of addresses and run it out to a Google Earth
KML file.

Cheers Fellas.

Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version=""1.0"" encoding=""UTF-8""?>"
'Print #lngFN, "<?xml version=""1.0"" encoding=""""UTF-8""""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"

Close #lngFN
End If
End Sub

The error message is, I believe, complaining about the value of the variable
'lngFN'. I don't see, in the posted code, where this variable is declared,
or where a value is assigned to it, or where the file gets opened for
output?

In other words, unless this is a global variable that is declared and
initialized elsewhere, the code seems to be missing something like this ...

Dim lngFN As Long

lngFN = FreeFile
Open "C:\SomeFolder\SomeFile.txt" For Output As #lngFN

You'll also want to close the file when you've finished writing to it ...

Close #lngFN

--
Brendan Reynolds

Sorry, I forgot to add that this is the line that now highlight errors
with
''Bad file name or number (runtime error 52)

Print #lngFN, rs.Fields("KML_Address")

Douglas

I noticed your 2nd option 'qdf![Run No] = strRun _No'
had a space before the underscore '_No'

but it still comes back with the same error line ('Bad file name or
number
(runtime error 52) as your first option


Here is my Current Code:


Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub


:

Douglas,

this line: qdf.Parameters("Run No") = strRun_No

comes back with 'Bad file name or number (runtime error 52)

your other line 'qdf![Run No] = strRun _No' redlines in the VBA window



:

Since your parameter doesn't have an underscore in it, try:

qdf.Parameters("Run No") = strRun_No

or

qdf![Run No] = strRun _No

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I tried both lines (seperately), but neither worked.

they both throw the following error:

'Item not found in this collection'

I am using 'Run No' as my parameter name without the underscore for
the
actual field, [Run_No] with the underscore.

Here is my SQL (with the parameter request)

SELECT [Points with Run No].Run_No, [Points with Run
No].Run_Point_Address_A
AS Address, [Points with Run No].Run_Point_Venue_A AS Venue,
"<Placemark><description>" & [Venue] & "</description><address>" &
[address]
& ", London, UK </address><name>" & [address] &
"</name></Placemark>" AS
KML_Address
FROM [Points with Run No]
WHERE ((([Points with Run No].Run_No)=[Run No]));






:

Sorry, my fault. I switched what I was going to tell you part way
through,
and missed a correction.

It should have been:

qdf.Parameters("Run_No") = strRun_No

(or you could use qdf![Run_No] = strRun_No)

However, given the error you're getting, your parameter obviously
isn't
named "Run_No".

What's the actual SQL of your query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I pasted your code (with subtle changes to the 'rs'
references...:

Private Sub Test_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] =
[Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub

When I run it, the dialog box for the user Run No pops up, but
then I
hit
OK, i get this error message:

Run-time error '3265':
Item not found in this collection.

and the vba highlights this line in the editor:

qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] =
[Run_No]


I tried that line to just say:
qdf![Run_No] = [Run_No]

but still the same error message.




:

I have an address query which ouputs a single field to text
strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer
text to
be
inserted. Does anyone know how I can pre-insert the Header and
Footer
into a
standard text file, and then save it with a '.kml' extension,
for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Another tweak?...

Does anyone know how I can get the 'Run no' based on the user input to
appear inside the kml ouput field

This is the current code line:

Print #lngFN, "<name>Run Points</name>" & strRun_No

Which gives this ouput in the KML file:

<name>Run Points</name>67

The problem is that it puts the Run No (67) outside of the KML name title;
but what I really need is for the Run No to be incorporated Inside the 'name'
field, like this:

<name>Run Points 67</name>

 

[Brendan Reynolds]

I'm glad you're making progress. I think you'll find, though, that the
resulting file is not, in fact, in UTF-8 encoding. You may find that this
causes XML parsers to reject the file when it contains characters outside
the standard ASCII range.

I'm not aware of any way to specify the encoding to be used when using VBA
IO commands. But you can probably avoid problems by changing the declaration
to match the actual encoding being used, rather than trying to change the
encoding to match the declaration.

On my system, when using the VBA IO commands, the resulting file is in
ISO-8859-1 encoding, so my declaration looks like this ...

Print #intFile, "<?xml version='1.0' encoding ='ISO-8859-1'?>"

I don't know whether VBA always uses this encoding, or whether it may vary
between OS versions or language versions.

If I remember correctly, I believe I used a utility or sample code that I
found on the 'net to determine the actual encoding that VBA was using. I'm
afraid, though, that it was some time ago, and I can't remember the details.

If you really need to specify an encoding other than the one that VBA uses
by default, I believe you can probably do that using Microsoft's XML parser,
MSXML.DLL. I haven't actually done this, so I can't provide details, but if
you want to look into it try searching on 'msxml' at MSDN
(http://msdn.microsoft.com)

--
Brendan Reynolds

Brendan,

That worked.

It seems that while making changes to the code after Douglas's helpful
input, I missed out some of John's essential code snippets. Following your
suggestion, I went back to John's code, and incorporated them into
Douglas's
latter code suggestions, and it all worked in the end. So a HUGE thank you
to
John, Douglas and your good self for your help and guidance on this
problem.

For the record, here is the succesful code that will allow the user to
choose a Run Number for a set of addresses and run it out to a Google
Earth
KML file.

Cheers Fellas.

Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version=""1.0"" encoding=""UTF-8""?>"
'Print #lngFN, "<?xml version=""1.0"" encoding=""""UTF-8""""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"

Close #lngFN
End If
End Sub

The error message is, I believe, complaining about the value of the
variable
'lngFN'. I don't see, in the posted code, where this variable is
declared,
or where a value is assigned to it, or where the file gets opened for
output?

In other words, unless this is a global variable that is declared and
initialized elsewhere, the code seems to be missing something like this
...

Dim lngFN As Long

lngFN = FreeFile
Open "C:\SomeFolder\SomeFile.txt" For Output As #lngFN

You'll also want to close the file when you've finished writing to it ...

Close #lngFN

--
Brendan Reynolds

Sorry, I forgot to add that this is the line that now highlight errors
with
''Bad file name or number (runtime error 52)

Print #lngFN, rs.Fields("KML_Address")

:

Douglas

I noticed your 2nd option 'qdf![Run No] = strRun _No'
had a space before the underscore '_No'

but it still comes back with the same error line ('Bad file name or
number
(runtime error 52) as your first option


Here is my Current Code:


Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub


:

Douglas,

this line: qdf.Parameters("Run No") = strRun_No

comes back with 'Bad file name or number (runtime error 52)

your other line 'qdf![Run No] = strRun _No' redlines in the VBA
window



:

Since your parameter doesn't have an underscore in it, try:

qdf.Parameters("Run No") = strRun_No

or

qdf![Run No] = strRun _No

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I tried both lines (seperately), but neither worked.

they both throw the following error:

'Item not found in this collection'

I am using 'Run No' as my parameter name without the underscore
for
the
actual field, [Run_No] with the underscore.

Here is my SQL (with the parameter request)

SELECT [Points with Run No].Run_No, [Points with Run
No].Run_Point_Address_A
AS Address, [Points with Run No].Run_Point_Venue_A AS Venue,
"<Placemark><description>" & [Venue] & "</description><address>"
&
[address]
& ", London, UK </address><name>" & [address] &
"</name></Placemark>" AS
KML_Address
FROM [Points with Run No]
WHERE ((([Points with Run No].Run_No)=[Run No]));






:

Sorry, my fault. I switched what I was going to tell you part
way
through,
and missed a correction.

It should have been:

qdf.Parameters("Run_No") = strRun_No

(or you could use qdf![Run_No] = strRun_No)

However, given the error you're getting, your parameter
obviously
isn't
named "Run_No".

What's the actual SQL of your query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
Douglas,

I pasted your code (with subtle changes to the 'rs'
references...:

Private Sub Test_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] =
[Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub

When I run it, the dialog box for the user Run No pops up,
but
then I
hit
OK, i get this error message:

Run-time error '3265':
Item not found in this collection.

and the vba highlights this line in the editor:

qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] =
[Run_No]


I tried that line to just say:
qdf![Run_No] = [Run_No]

but still the same error message.




:

I have an address query which ouputs a single field to text
strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and
footer
text to
be
inserted. Does anyone know how I can pre-insert the Header
and
Footer
into a
standard text file, and then save it with a '.kml'
extension,
for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[Douglas J. Steele]

Print #lngFN, "<name>Run Points " & strRun_No & "</name>"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Another tweak?...

Does anyone know how I can get the 'Run no' based on the user input to
appear inside the kml ouput field

This is the current code line:

Print #lngFN, "<name>Run Points</name>" & strRun_No

Which gives this ouput in the KML file:

<name>Run Points</name>67

The problem is that it puts the Run No (67) outside of the KML name title;
but what I really need is for the Run No to be incorporated Inside the
'name'
field, like this:

<name>Run Points 67</name>

I have an address query which ouputs a single field to text strings for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to be
inserted. Does anyone know how I can pre-insert the Header and Footer
into a
standard text file, and then save it with a '.kml' extension, for example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Douglas,

Thanks very much for this. where can I look to find out more about mixing
user defined/ficked strings with variable data/fields. The Access help files
are not very good for this kind of thing (not enough examples)

Print #lngFN, "<name>Run Points " & strRun_No & "</name>"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Another tweak?...

Does anyone know how I can get the 'Run no' based on the user input to
appear inside the kml ouput field

This is the current code line:

Print #lngFN, "<name>Run Points</name>" & strRun_No

Which gives this ouput in the KML file:

<name>Run Points</name>67

The problem is that it puts the Run No (67) outside of the KML name title;
but what I really need is for the Run No to be incorporated Inside the
'name'
field, like this:

<name>Run Points 67</name>

I have an address query which ouputs a single field to text strings for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to be
inserted. Does anyone know how I can pre-insert the Header and Footer
into a
standard text file, and then save it with a '.kml' extension, for example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Funny you should mention this Brendan, because I have been having problems
with certains characters in the KML file such as these two: ' &. I had to
change the ampersand (&) character to 'and' before google earth would accept
them. I am not familiar with encoding formats, but assumed the KML was in
UTF-8 encoding format. Here's a link to googles guide on KML where you can
see that they use this type of encoding in their header file.

http://code.google.com/apis/kml/documentation/kml_tut.html

Below is my resultant KML file with adjusted characterss which works fine in
Google Earth, though it would be much better if it would take the & type
characters:

My KML example:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Run Points 5</name>
<Folder>
<name>Locations</name>
<open>1</open>
<Placemark><description>Ascot Hotel</description><address>11 Craven Road,
W2, London, UK </address><name>11 Craven Road, W2, London,
UK</name></Placemark>
</Folder>
</Document>
</kml>

I'm glad you're making progress. I think you'll find, though, that the
resulting file is not, in fact, in UTF-8 encoding. You may find that this
causes XML parsers to reject the file when it contains characters outside
the standard ASCII range.

I'm not aware of any way to specify the encoding to be used when using VBA
IO commands. But you can probably avoid problems by changing the declaration
to match the actual encoding being used, rather than trying to change the
encoding to match the declaration.

On my system, when using the VBA IO commands, the resulting file is in
ISO-8859-1 encoding, so my declaration looks like this ...

Print #intFile, "<?xml version='1.0' encoding ='ISO-8859-1'?>"

I don't know whether VBA always uses this encoding, or whether it may vary
between OS versions or language versions.

If I remember correctly, I believe I used a utility or sample code that I
found on the 'net to determine the actual encoding that VBA was using. I'm
afraid, though, that it was some time ago, and I can't remember the details.

If you really need to specify an encoding other than the one that VBA uses
by default, I believe you can probably do that using Microsoft's XML parser,
MSXML.DLL. I haven't actually done this, so I can't provide details, but if
you want to look into it try searching on 'msxml' at MSDN
(http://msdn.microsoft.com)

--
Brendan Reynolds

Brendan,

That worked.

It seems that while making changes to the code after Douglas's helpful
input, I missed out some of John's essential code snippets. Following your
suggestion, I went back to John's code, and incorporated them into
Douglas's
latter code suggestions, and it all worked in the end. So a HUGE thank you
to
John, Douglas and your good self for your help and guidance on this
problem.

For the record, here is the succesful code that will allow the user to
choose a Run Number for a set of addresses and run it out to a Google
Earth
KML file.

Cheers Fellas.

Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version=""1.0"" encoding=""UTF-8""?>"
'Print #lngFN, "<?xml version=""1.0"" encoding=""""UTF-8""""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"

Close #lngFN
End If
End Sub

The error message is, I believe, complaining about the value of the
variable
'lngFN'. I don't see, in the posted code, where this variable is
declared,
or where a value is assigned to it, or where the file gets opened for
output?

In other words, unless this is a global variable that is declared and
initialized elsewhere, the code seems to be missing something like this
...

Dim lngFN As Long

lngFN = FreeFile
Open "C:\SomeFolder\SomeFile.txt" For Output As #lngFN

You'll also want to close the file when you've finished writing to it ...

Close #lngFN

--
Brendan Reynolds

Sorry, I forgot to add that this is the line that now highlight errors
with
''Bad file name or number (runtime error 52)

Print #lngFN, rs.Fields("KML_Address")

:

Douglas

I noticed your 2nd option 'qdf![Run No] = strRun _No'
had a space before the underscore '_No'

but it still comes back with the same error line ('Bad file name or
number
(runtime error 52) as your first option


Here is my Current Code:


Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub


:

Douglas,

this line: qdf.Parameters("Run No") = strRun_No

comes back with 'Bad file name or number (runtime error 52)

your other line 'qdf![Run No] = strRun _No' redlines in the VBA
window



:

Since your parameter doesn't have an underscore in it, try:

qdf.Parameters("Run No") = strRun_No

or

qdf![Run No] = strRun _No

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I tried both lines (seperately), but neither worked.

they both throw the following error:

'Item not found in this collection'

I am using 'Run No' as my parameter name without the underscore
for
the
actual field, [Run_No] with the underscore.

Here is my SQL (with the parameter request)

SELECT [Points with Run No].Run_No, [Points with Run
No].Run_Point_Address_A
AS Address, [Points with Run No].Run_Point_Venue_A AS Venue,
"<Placemark><description>" & [Venue] & "</description><address>"
&
[address]
& ", London, UK </address><name>" & [address] &
"</name></Placemark>" AS
KML_Address
FROM [Points with Run No]
WHERE ((([Points with Run No].Run_No)=[Run No]));






:

Sorry, my fault. I switched what I was going to tell you part
way
through,
and missed a correction.

It should have been:

qdf.Parameters("Run_No") = strRun_No

(or you could use qdf![Run_No] = strRun_No)

However, given the error you're getting, your parameter
obviously
isn't
named "Run_No".

What's the actual SQL of your query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
Douglas,

I pasted your code (with subtle changes to the 'rs'
references...:

Private Sub Test_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] =
[Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"

 

[Douglas J. Steele]

If I recall correctly, there's a chapter about this in the Access
Developer's Handbook.

See http://www.developershandbook.com/ for details about this indispensible
resource.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Thanks very much for this. where can I look to find out more about mixing
user defined/ficked strings with variable data/fields. The Access help
files
are not very good for this kind of thing (not enough examples)

Print #lngFN, "<name>Run Points " & strRun_No & "</name>"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Another tweak?...

Does anyone know how I can get the 'Run no' based on the user input to
appear inside the kml ouput field

This is the current code line:

Print #lngFN, "<name>Run Points</name>" & strRun_No

Which gives this ouput in the KML file:

<name>Run Points</name>67

The problem is that it puts the Run No (67) outside of the KML name
title;
but what I really need is for the Run No to be incorporated Inside the
'name'
field, like this:

<name>Run Points 67</name>


:

I have an address query which ouputs a single field to text strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to
be
inserted. Does anyone know how I can pre-insert the Header and Footer
into a
standard text file, and then save it with a '.kml' extension, for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[Brendan Reynolds]

That's a separate issue, the & and < characters have to be escaped because
they have special meaning to the parser. See the section "2.4 Character Data
and Markup" at the following URL ...

http://www.w3.org/TR/REC-xml/

In short, you need to replace "&" with "&amp;" and "<" with "&lt;"

The issue of encoding is described in the same article at section "4.3.3
Character Encoding in Entities"

I know, it's not an easy read, and I can't explain it all. I'm not an expert
on these issues, it's just something I had to muddle through as best I could
for a specific project that I worked on about two years ago. If you think
the code you have now will do what you need, that's great, but make sure to
test it with characters outside the standard ASCII range if there is any
possibility that such characters may ever be included in your real-world
data.

--
Brendan Reynolds

Funny you should mention this Brendan, because I have been having problems
with certains characters in the KML file such as these two: ' &. I had to
change the ampersand (&) character to 'and' before google earth would
accept
them. I am not familiar with encoding formats, but assumed the KML was in
UTF-8 encoding format. Here's a link to googles guide on KML where you can
see that they use this type of encoding in their header file.

http://code.google.com/apis/kml/documentation/kml_tut.html

Below is my resultant KML file with adjusted characterss which works fine
in
Google Earth, though it would be much better if it would take the & type
characters:

My KML example:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Run Points 5</name>
<Folder>
<name>Locations</name>
<open>1</open>
<Placemark><description>Ascot Hotel</description><address>11 Craven Road,
W2, London, UK </address><name>11 Craven Road, W2, London,
UK</name></Placemark>
</Folder>
</Document>
</kml>

I'm glad you're making progress. I think you'll find, though, that the
resulting file is not, in fact, in UTF-8 encoding. You may find that this
causes XML parsers to reject the file when it contains characters outside
the standard ASCII range.

I'm not aware of any way to specify the encoding to be used when using
VBA
IO commands. But you can probably avoid problems by changing the
declaration
to match the actual encoding being used, rather than trying to change the
encoding to match the declaration.

On my system, when using the VBA IO commands, the resulting file is in
ISO-8859-1 encoding, so my declaration looks like this ...

Print #intFile, "<?xml version='1.0' encoding ='ISO-8859-1'?>"

I don't know whether VBA always uses this encoding, or whether it may
vary
between OS versions or language versions.

If I remember correctly, I believe I used a utility or sample code that I
found on the 'net to determine the actual encoding that VBA was using.
I'm
afraid, though, that it was some time ago, and I can't remember the
details.

If you really need to specify an encoding other than the one that VBA
uses
by default, I believe you can probably do that using Microsoft's XML
parser,
MSXML.DLL. I haven't actually done this, so I can't provide details, but
if
you want to look into it try searching on 'msxml' at MSDN
(http://msdn.microsoft.com)

--
Brendan Reynolds

Brendan,

That worked.

It seems that while making changes to the code after Douglas's helpful
input, I missed out some of John's essential code snippets. Following
your
suggestion, I went back to John's code, and incorporated them into
Douglas's
latter code suggestions, and it all worked in the end. So a HUGE thank
you
to
John, Douglas and your good self for your help and guidance on this
problem.

For the record, here is the succesful code that will allow the user to
choose a Run Number for a set of addresses and run it out to a Google
Earth
KML file.

Cheers Fellas.

Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version=""1.0"" encoding=""UTF-8""?>"
'Print #lngFN, "<?xml version=""1.0"" encoding=""""UTF-8""""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"

Close #lngFN
End If
End Sub

:


The error message is, I believe, complaining about the value of the
variable
'lngFN'. I don't see, in the posted code, where this variable is
declared,
or where a value is assigned to it, or where the file gets opened for
output?

In other words, unless this is a global variable that is declared and
initialized elsewhere, the code seems to be missing something like
this
...

Dim lngFN As Long

lngFN = FreeFile
Open "C:\SomeFolder\SomeFile.txt" For Output As #lngFN

You'll also want to close the file when you've finished writing to it
...

Close #lngFN

--
Brendan Reynolds

Sorry, I forgot to add that this is the line that now highlight
errors
with
''Bad file name or number (runtime error 52)

Print #lngFN, rs.Fields("KML_Address")

:

Douglas

I noticed your 2nd option 'qdf![Run No] = strRun _No'
had a space before the underscore '_No'

but it still comes back with the same error line ('Bad file name or
number
(runtime error 52) as your first option


Here is my Current Code:


Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN
End If
End Sub


:

Douglas,

this line: qdf.Parameters("Run No") = strRun_No

comes back with 'Bad file name or number (runtime error 52)

your other line 'qdf![Run No] = strRun _No' redlines in the VBA
window



:

Since your parameter doesn't have an underscore in it, try:

qdf.Parameters("Run No") = strRun_No

or

qdf![Run No] = strRun _No

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
Douglas,

I tried both lines (seperately), but neither worked.

they both throw the following error:

'Item not found in this collection'

I am using 'Run No' as my parameter name without the
underscore
for
the
actual field, [Run_No] with the underscore.

Here is my SQL (with the parameter request)

SELECT [Points with Run No].Run_No, [Points with Run
No].Run_Point_Address_A
AS Address, [Points with Run No].Run_Point_Venue_A AS Venue,
"<Placemark><description>" & [Venue] &
"</description><address>"
&
[address]
& ", London, UK </address><name>" & [address] &
"</name></Placemark>" AS
KML_Address
FROM [Points with Run No]
WHERE ((([Points with Run No].Run_No)=[Run No]));






:

Sorry, my fault. I switched what I was going to tell you
part
way
through,
and missed a correction.

It should have been:

qdf.Parameters("Run_No") = strRun_No

(or you could use qdf![Run_No] = strRun_No)

However, given the error you're getting, your parameter
obviously
isn't
named "Run_No".

What's the actual SQL of your query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
Douglas,

I pasted your code (with subtle changes to the 'rs'
references...:

Private Sub Test_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) > 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run_No") = [Run_No] ' or
qdf![Run_No] =
[Run_No]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


'Output footer
Print #lngFN, "</Folder>"