Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro help. (office 2000)

    I am using the following macro to open a mail merged document. What I want to do is find a way to display a message when the query which am using for the mail merge contains no data and stop the module from opening the document. Is that possible, if yes please tell me how, coz I am no good when it comes to visual basic.

    Sub CitcoFax()
    Dim objWord As Object
    Set objWord = CreateObject("Word.Basic")
    objWord.AppShow
    'objWord.AppMaximize "", 1 (optional)
    objWord.FileOpen "S:SRI_WO~1NEWFOL~1citcof~1.doc"
    End Sub

    Advance thank you for the help.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help. (office 2000)

    You could open the query and look to the recordcount. If it is 0 then exit sub else do code to open the word document.
    Something like this:

    <pre>Sub CitcoFax()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim objWord As Object
    Set db = CurrentDb
    Set rst = db.OpenRecordset("YourQuery")
    If rst.RecordCount = 0 Then
    MsgBox "There are no records", vbOKOnly
    Set rst = Nothing
    Set db = Nothing
    Exit Sub
    Else
    Set objWord = CreateObject("Word.Basic")
    objWord.AppShow
    'objWord.AppMaximize "", 1 (optional)
    objWord.FileOpen "S:SRI_WO~1NEWFOL~1citcof~1.doc"
    End If
    Set rst = Nothing
    Set db = Nothing
    End Sub
    </pre>

    Success
    Francois

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help. (office 2000)

    Hi Farancois,
    I tried that but its giving me an error on Dim db As DAO.Database
    I know nothing about visual basic so I really don't know whats wrong so I'll bother you untill I get this right, hope you don't mind.
    Thanks again

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help. (office 2000)

    Open a module, go to Tools, References and in the Available References, look for MicroSoft DAO 3.6 Object Library and check it.
    Francois

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help. (office 2000)

    Thank you soooooo much Francois, if you have time would you please have a look at a post which is called "Query question" which was posted by me, PLEASE.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Macro help. (office 2000)

    Another way to deal with this problem without opening the recordset in VBA is to use error trapping in your Automation code. An error code 5631 says that no data was returned to Word - you can then display your own caution message or whatever when the error occurs.
    Wendell

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help. (office 2000)

    Hi Wendell!
    How do you use the error trapping method? Is there a macro command for that? Please let me know.
    Thanks all...

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Macro help. (office 2000)

    No - sorry, it can't be done in a macro, but it can be done with VBA. In VBA you use the On Error Goto XXX statement. You put a label in your code, and then you check to see what the error number was. There should be something in the VBA Help for that kind of thing, but I'm on an Office XP system at the moment, and can't verify it. For a more complete discussion of error handling, you might look at the Access Developer's Handbook by Ken Getz, et. al. We also expect to publish an article in WAW on controlling mail merges from Access in an upcoming issue.
    Wendell

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Macro help. (office 2000)

    Oops - I meant to add that Francois's post also uses VBA, not a macro.
    Wendell

  10. #10
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help. (office 2000)

    Thank you Wendell, It looks like more work and I have no idea when comes to VBA so I think I'll leave it as it is.
    But Thanks alot for the post.

Posting Permissions

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