Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query criteria and automation (2007)

    I use a code that opens a word mailmerge document based on a query. Recently I tried to use date criteria in the same query. The dates are based on combo boxes; start and end dates. The query works with the date criteria. When I try to open with merge document it fails (word was unable to open the datasource). If I try to open the document itself it fails (asks if I want to run the query, and then says it can't find the database. When it gives me the option of finding the datasource not all the queries or tables are available) . And if I try to create a new merge document based on the query it fails( can not open datasource).

    Is the problem that you cannot not use queries with changing criteria for the merge or am I missing something.

    thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query criteria and automation (2007)

    Is this related to the long thread starting at <post:=634,138>post 634,138</post:>?

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query criteria and automation (2007)

    I would like to revisit an old post (post 634,138) I have used automation to open a word document based on a query. I now want to set the parameters based on 2 date box combo boxes (startdate and enddate) If I set up the query criteria as either 'between startdate and enddate 'or as a 2 fields (the same field repeated with >startdate and <enddate criteria for each respective field than the query returns the correct results (the second approach is reorganized as the first by access).

    Un fortunately when I try to use this query as the mail merge source of the word document it fails-"can not open database". The same code works for a query with only one parameter.

    Any help would be appreciated.

    nebbia

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query criteria and automation (2007)

    That thread was very long and confusing, so I'd appreciate it if you could attach a stripped down and zipped copy of the database and document you're working with now.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query criteria and automation (2007)

    I have included a stripped down db and word template. I would like to 1) replace between ..and .. with startdate and enddate from the switchboard combo boxes. 2)use the output of the query to automate a mailmerge word document. The query itself appears to work but when I attempt the merge there is an error

    2nd post is stripped word doc

    nebbia

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query criteria and automation (2007)

    2nd attachment

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query criteria and automation (2007)

    In the attached version of the database, I have changed the query and the code in such a way that the document is opened successfully.
    - I removed the parameters from the query.
    - I added the city, state and zip fields because the document refers to them.
    - I specified the parameters in the code.

  8. #8
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query criteria and automation (2007)

    I haven't had the same good fortune. I do not get the word document to open. The reference to the word doc is correct. Was there anything in the attachment that needed to be changed on my end. I am using access 2007 if that would make a difference

    nebbia

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query criteria and automation (2007)

    I don't have Office 2007 so I cannot test there. It works OK in Office 2002 and Office 2003 - the only change I made when testing was the path to the document, which is obviously different on my PC.
    Important: if you modified anything in the database, you have to close and reopen it, otherwise Word won't be able to connect to the database.

  10. #10
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query criteria and automation (2007)

    The problem was mine. Initially the db was read only-when I copied it it worked fine Thanks

    nebbia

  11. #11
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query criteria and automation (2007)

    I thought that you had resolved all the issues. I tried to adapt the code to qryFollowUp and automate it to a word document FollowupPatient.doc. I used this code
    strSQL = "SELECT * FROM qryFollowUp WHERE Visit Between #" & _
    Format(Me.StartDate, "mm/dd/yyyy") & "# And #" & Format(Me.EndDate, "mm/dd/yyyy") & "#"
    The word document does not open, and there are two error messages. 1-word was unable to open the datasource ore 2-OBDC Microsoft access driver login failed Could not use 'l'; file already in use.
    I opened the word doc and in the immediate window entered ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument.
    This had no effect either.

    Am I missing something in the strSQL statement

    nebbia

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query criteria and automation (2007)

    Did you close and reopen the database after making the changes?

  13. #13
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query criteria and automation (2007)

    yes, several times

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query criteria and automation (2007)

    Did you remove the criteria for MaxOfVisit from qryFollowUp? You cannot have criteria referring to a control on a form in the query.

  15. #15
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query criteria and automation (2007)

    yes the criteria was removed. As you said the code will supply the criteria

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •