473,322 Members | 1,526 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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
5 1923
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
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
"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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Ray Cassick \(Home\) | last post by:
Ok, I have been looking all over fro answers to these two questions and can't seem to locate anything... I am still looking but figured that I would drop a query here also.... 1) The...
20
by: Olav.NET | last post by:
I am a .NET/C++ developer who is supposed to do some work with Access. I do not know much about it except for the DB part. Questions: *1* I am looking for INTENSIVE books to get quickly up to...
9
by: Don Sealer | last post by:
I've got to questions. First. I have a form that works off of dates. Plug in a start and end date and it triggers a query then displays a report. The problem is when I close the report it goes...
3
by: Ken Varn | last post by:
I am just starting the process of creating ASP.NET server controls. I have created controls for .NET applications, but have just started with ASP.NET. I am a little confused about some areas that...
2
by: amith.srinivas | last post by:
Hi all, From a word macro in VBA, I am trying to create a report in access DB. The report is based on a query with a parameter. I am using Set rpt = Application.CreateReport rpt.RecordSource =...
2
by: ChrisR | last post by:
Howdy all. I posted this in the SQL newsgroups but got no response, so I thought I'd try here. I'm a SQL DBA, not a .NET developer, so please forgive me if my concepts/ verbage are slightly...
14
by: shamirza | last post by:
Question Do ActiveX DLLs made in VB still need the VB runtimes on the machine? ________________________________________ Answer In a word, Yes. Visual Basic does not support what is known...
1
by: Bhujanga | last post by:
I am creating letters from selected Access records using a Mail Merge document in Word. The mail merge document gets its data from a one-record table that is created from a Make-Table query that gets...
102
by: dreamznatcher | last post by:
Hello, I'm considering a career switch to a more database-related job, but need help on a few questions and issues. I'm a Computer Engineering graduate and have always felt most comfortable...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.