By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,087 Members | 1,508 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,087 IT Pros & Developers. It's quick & easy.

Two Questions: Seeing if my query is done, and creating a RTF/Word doc

P: n/a
Hi,

I have been creating a database to keep track of press clippings, but I
have hit a couple stumbling blocks. Any help is much appreciate.

1) Seeing if my query is done

I have the following code to define a query. I run the query and then
get a record deleted errors (3167) when I go to export to a
spreadsheet. It works if I put in a delay between the query and the
execute, but that's obviously a bad way to do it. Is there a way to
check if the query has finished populating? Here is the relevant
snippet:
dbsCurrent.QueryDefs("qryTemp").SQL = "" & _
"SELECT Publications.Publication, Publications.Medium,
Clips.[Publication Date], Publications.Circulation, Clips.Headline,
Authors.Author, Clips.Description, Clips.URL, Clips.Value" & _
" FROM Clips, Publications, Authors " & _
" WHERE [Publication Date] BETWEEN #" & DteStartDate & "#
AND # " & DteEndDate & "#" & _
" AND Client = '" & strClient & "'" & _
" AND Publications.PublicationID = Clips.PublicationID" & _
" AND Authors.AuthorID = Clips.AuthorID" & _
" ORDER BY Clips.[Clip Date]"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"qryTemp", strPath

Else...

2) Creating an RTF or Word document.

I also want to take the fields from the query and create a Word of RTF
document. I assume I should create a recordset and loop through it...

Say a record in the query looks like: Publication, Date, Headline,
Author

I want to create a Word document that looks like:

Publication1, Date1.
"Headline1," by Author1.

Publication2, Date2.
"Headline2," by Author2.

Any help? I hope I even know enough to ask the question properly.

Thanks for any help!

Regards,
Andrew

Jul 2 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
darnnews wrote:
Hi,

I have been creating a database to keep track of press clippings, but I
have hit a couple stumbling blocks. Any help is much appreciate.

1) Seeing if my query is done

I have the following code to define a query. I run the query and then
get a record deleted errors (3167) when I go to export to a
spreadsheet. It works if I put in a delay between the query and the
execute, but that's obviously a bad way to do it. Is there a way to
check if the query has finished populating? Here is the relevant
snippet:
dbsCurrent.QueryDefs("qryTemp").SQL = "" & _
"SELECT Publications.Publication, Publications.Medium,
Clips.[Publication Date], Publications.Circulation, Clips.Headline,
Authors.Author, Clips.Description, Clips.URL, Clips.Value" & _
" FROM Clips, Publications, Authors " & _
" WHERE [Publication Date] BETWEEN #" & DteStartDate & "#
AND # " & DteEndDate & "#" & _
" AND Client = '" & strClient & "'" & _
" AND Publications.PublicationID = Clips.PublicationID" & _
" AND Authors.AuthorID = Clips.AuthorID" & _
" ORDER BY Clips.[Clip Date]"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"qryTemp", strPath

Else...

2) Creating an RTF or Word document.

I also want to take the fields from the query and create a Word of RTF
document. I assume I should create a recordset and loop through it...

Say a record in the query looks like: Publication, Date, Headline,
Author

I want to create a Word document that looks like:

Publication1, Date1.
"Headline1," by Author1.

Publication2, Date2.
"Headline2," by Author2.

Any help? I hope I even know enough to ask the question properly.

Thanks for any help!

Regards,
Andrew
1. Rewriting your query with LEFT JOIN ((LEFT JOIN Authors ON
Clips.AuthorID = Authors.AuthorID) LEFT JOIN Publications ...) might
speed up its execution so much you won't need to care about recognizing
when it's complete.

2. I suppose you could create a Report formatted as you will and export
it as RTF without a lot of trouble.

Jul 2 '06 #2

P: n/a
Anyone else on question 1?

I think that even if I speed up the query, when my tables grow larger,
the logic will break. Is there a property of the QueryDef or something
that I can check to see if it's done?

As for Question 2, now I am neck deep in reports. Thanks!

Thanks,
Andrew
Lyle Fairfield wrote:
darnnews wrote:
Hi,

I have been creating a database to keep track of press clippings, but I
have hit a couple stumbling blocks. Any help is much appreciate.

1) Seeing if my query is done

I have the following code to define a query. I run the query and then
get a record deleted errors (3167) when I go to export to a
spreadsheet. It works if I put in a delay between the query and the
execute, but that's obviously a bad way to do it. Is there a way to
check if the query has finished populating? Here is the relevant
snippet:
dbsCurrent.QueryDefs("qryTemp").SQL = "" & _
"SELECT Publications.Publication, Publications.Medium,
Clips.[Publication Date], Publications.Circulation, Clips.Headline,
Authors.Author, Clips.Description, Clips.URL, Clips.Value" & _
" FROM Clips, Publications, Authors " & _
" WHERE [Publication Date] BETWEEN #" & DteStartDate & "#
AND # " & DteEndDate & "#" & _
" AND Client = '" & strClient & "'" & _
" AND Publications.PublicationID = Clips.PublicationID" & _
" AND Authors.AuthorID = Clips.AuthorID" & _
" ORDER BY Clips.[Clip Date]"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"qryTemp", strPath

Else...

2) Creating an RTF or Word document.

I also want to take the fields from the query and create a Word of RTF
document. I assume I should create a recordset and loop through it...

Say a record in the query looks like: Publication, Date, Headline,
Author

I want to create a Word document that looks like:

Publication1, Date1.
"Headline1," by Author1.

Publication2, Date2.
"Headline2," by Author2.

Any help? I hope I even know enough to ask the question properly.

Thanks for any help!

Regards,
Andrew

1. Rewriting your query with LEFT JOIN ((LEFT JOIN Authors ON
Clips.AuthorID = Authors.AuthorID) LEFT JOIN Publications ...) might
speed up its execution so much you won't need to care about recognizing
when it's complete.

2. I suppose you could create a Report formatted as you will and export
it as RTF without a lot of trouble.
Jul 2 '06 #3

P: n/a
"darnnews" <an************@gmail.comwrote
Anyone else on question 1?
I think that even if I speed up the query,
when my tables grow larger, the logic
will break. Is there a property of the
QueryDef or something that I can
check to see if it's done?
There's nothing in your code that runs the Query except the
TransferSpreadsheet, and it certainly just building it should be complete
before you execute the next statement that refers to it. How are you
"executing" the Query?

But instead of setting the SQL property of a Query, you could just build the
SQL Statement in a variable, for example, strSQL, and refer to that variable
in the Transfer Spreadsheet.
As for Question 2, now I am neck deep in reports. Thanks!
How many is "neck deep", and whatever your answer is, what difference will
one more make? As an alternative, you could use a mail merge and save the
Word document as RTF, but my guess is "The report would be easier," but
mail-merging to a document in Word may provide you better control over the
appearance.

Larry Linson
Microsoft Access MVP


Jul 3 '06 #4

P: n/a
Thanks Larry. My grasp of Access and VB is a little shaky, as I haven't
used them in about eight years.

I am now storing the SQL string as you suggest.

And I am looking into creating a mail merge, as the RTF snapshots that
I have implemented are not acceptable. I need to be able to edit and
copy/paste.

If anyone has any suggestions on the best place to look at programmatic
mail merge examples for Access/Word 2003, that would be very helpful.

Regards,
Andrew
Larry Linson wrote:
"darnnews" <an************@gmail.comwrote
Anyone else on question 1?
I think that even if I speed up the query,
when my tables grow larger, the logic
will break. Is there a property of the
QueryDef or something that I can
check to see if it's done?

There's nothing in your code that runs the Query except the
TransferSpreadsheet, and it certainly just building it should be complete
before you execute the next statement that refers to it. How are you
"executing" the Query?

But instead of setting the SQL property of a Query, you could just build the
SQL Statement in a variable, for example, strSQL, and refer to that variable
in the Transfer Spreadsheet.
As for Question 2, now I am neck deep in reports. Thanks!

How many is "neck deep", and whatever your answer is, what difference will
one more make? As an alternative, you could use a mail merge and save the
Word document as RTF, but my guess is "The report would be easier," but
mail-merging to a document in Word may provide you better control over the
appearance.

Larry Linson
Microsoft Access MVP
Jul 3 '06 #5

P: n/a
"darnnews" wrote
And I am looking into creating a mail
merge, as the RTF snapshots that I have
implemented are not acceptable. I need
to be able to edit and copy/paste.
I am one of the many who has not been overly happy with how formatting is
maintained when outputting Reports in formats other than Print, Snapshot, or
PDF. My best luck in maintaining formatting has been with HTML and even that
is not "exact."
If anyone has any suggestions on the best
place to look at programmatic mail merge
examples for Access/Word 2003, that
would be very helpful.
Generally, I'd visit the following, probably in this order (though I have
not searched any of them for that particular subject):

http://www.mvps.org/access -- The Access Web, which is also
the FAQ site for this
newsgroup
http://support.microsoft.com -- home of the searchable Microsoft
Knowledge Base
http://office.microsoft.com -- the Office Online site

and, because MVP Jeff Conrad has an extensive compendium of information and
links, I'd next visit his site at

http://home.bendbroadband.com/conrad...essjunkie.html

and there are many, many other good sites maintained by participants here.

Larry Linson
Microsoft Access MVP
Jul 4 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.