Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

"item not found in this collection" trying to use MailMerge 2

Status
Not open for further replies.
twomblml

twomblml

IS-IT--Management
Apr 17, 2001
42
US
I am following the code example for RunMailMerge in faq181-5088. This looks like a great way to accomplish mail merges in access. However, I am having some problems right of the bat. The Function CurrentBackendPath() is not working properly. It errors out on the following line:

str = CurrentDb().TableDefs(strTableName).Connect

with the "Item not found in this collection". I have referenced the DAO 3.6 objects. What am I missing? It appears to not like TableDefs. This is Access 2003 working with an Access 2000 format db.

Any help would be greatly appreciated! Here is the entire function for reference:

Private Function CurrentBackendPath() As String
Const JETDBPrefix As String = ";DATABASE="
Const strTableName As String = "GLOBALS"

Dim str As String
Dim idx As Integer

str = CurrentDb().TableDefs(strTableName).Connect
idx = InStr(1, str, JETDBPrefix, vbTextCompare)
str = Mid(str, idx + Len(JETDBPrefix))
CurrentBackendPath = GetPath(str)
End Function

Thanks!
 
Sort by date Sort by votes
Does your database have a linked table named GLOBALS ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
No it doesn't. I changed GLOBALS to the system table MSysObjects. Now it connects. str is then set to "" and I continue through the code to:

Private Function GetPath(FileName As String) As String
If Dir(FileName) = "" Then
GetPath = ""
Else
GetPath = Left(FileName, Len(FileName) - Len(Dir(FileName)))
End If
End Function

str is sent as "" to FileName. Any ideas?

Thanks!
 
Upvote 0 Downvote
MSysObjects is NEVER linked !
Choose the name of a well known linked table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
I think I'm missing the boat on this module. Forgive me for sounding stupid but I have no linked tables. I have my one MDB file with my forms and what not in it. I created the module based on the FAQ. I exported a query for my text export specification. (I don't know if this is correct). I have my mail merge document in Word. This is my first adventure into mail merge through access so any help is greatly appreciated! Do you recommend using another method? I have seen several up here. Here is what I am trying to do in a nut shell.

I have a db of contractors. I want to find a contractor record and then create a mail merge document for that contractor.

Thanks!
 
Upvote 0 Downvote
I have no linked tables. I have my one MDB file
So why boring with BackEnd stuff ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Sorry, when I wrote the module I wanted it to look at the server path, not necessarily the frontend path. Long story short, you can replace all the crazy 'where is the backend' code with a hardcoded path, like:

Code: 
Private Function GetStartDirectory() As String
    GetStartDirectory = "\\servername\sharename\etc\where_you_have_the_word_docs_at\"
End Function

I swear you'll love the thing once you get it working. :) Send me a FAQ comment if you have further questions.


Peter
 
Upvote 0 Downvote
OK, so I'm setting my GetStartDirectory = "c:\temp\merge\". This is working good. But the TransferText method is failing stating that it cannot find the file.

I created a test.txt file by exporting a query in the Word mail merge format as a text file. It creates the schema.ini file along with it. My button command sends the following to my mail merge module:

Private Sub Command24_Click()
RunMailmerge "SELECT * FROM ProjSubQuery ", "test", "CO.doc"
End Sub

Again, it fails at the TransferText method. Any ideas?

Thanks!
 
Upvote 0 Downvote
Hey if you are suing a macro to run the word, then this is what I would do, open the macro, under the action colum, select run and in the the comand line type the path eg: c:\winword.exe\file name

hope this works
 
Upvote 0 Downvote
Thanks but this is not a macro. I am trying to use pseales mail merge module. Thanks for the suggestion though...
 
Upvote 0 Downvote
Just saw your last post "OK, so I'm setting my GetStartDirectory = "c:\temp\merge\". This is working good. But the TransferText method is failing stating that it cannot find the file"


If at all the word comes up and says the file could not be found then there is only one possibility(as far as I know), in the query column you have selected one of the required field is not found. check the tables that you are using for that query.

Or the word is not liked with the query.

Hope this works
 
Upvote 0 Downvote
The error occurs here:

DoCmd.TransferText acExportDelim, ExportSpecificationName, strQdfName, FullPathAndFilename, True

The file in question is the ExportSpecificationName which is specified on the button click as

RunMailmerge "SELECT * FROM ProjSubQuery ", "test", "CO.doc"

The error is:

Microsoft Office Access
"The text file specification 'test' does not exist. You cannot import, export, or link using the specification."

It finds the Word document without any problem. I click OK to the error and the program continues to open the Word document specified in the button click as CO.doc.

Thanks!
 
Upvote 0 Downvote
Seems you haven't read (or at least made sense with) the requirement section of the FAQ.
 
Upvote 0 Downvote
PHV, I agree with your statement in parenthesis. I have read that thing a million times. I set the GetStartDirectory = "c:\temp\merge\". My test.txt file resides in that directory along with the database and doc file. It finds the doc file but not the test file. Could you shed any light on this? I know I'm missing something stupid here but I just can figure it out.

Thanks again...
 
Upvote 0 Downvote
When in VBE put the cursor inside the transfertext word in your code and then press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
I stumbled and bumbled and eventually found my way through the darkness. I was saving the files for the export specification but never went into the advanced button in the export wizard and did a save as to actually save the specification into the database. Help was of no help. I actually stumbled onto it in the last post of this thread thread705-908080. Thank you all for the help.

A star for PHV for getting me away from the backend.

A star for pseale for pointing me away from the backend as well.

Thanks!
 
Upvote 0 Downvote
I'm sure others would definitely appreciate it! I appreciate just having the FAQ! :)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

renigar
  • Locked
  • Question
Access 365: Search LastName in one table to put EmployeeID in another
Replies
8
Views
732
renigar
renigar
TheresAlwaysAWay
  • Locked
  • Question
Can Access be used to generate and respond to text messages? 1
Replies
6
Views
3K
TheresAlwaysAWay
TheresAlwaysAWay
atadbitslow
  • Locked
  • Question
how to search data and display matches that include the user's input, but not necessarily match
Replies
5
Views
437
atadbitslow
atadbitslow
Bubba002
  • Locked
  • Question
WHERE clause in the Set command of an db.OpenRecordset method
Replies
16
Views
827
dhookom
dhookom
Trob70
  • Locked
  • Question
There is no object in this control 1
Replies
9
Views
634
Trob70
Trob70

Part and Inventory Search

Sponsor

Back
Top