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

    Module Error (Access2000)

    Sub ClassicFax()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim objWord As Object
    Set db = CurrentDb
    'Run append query to add records to the Classic table (system warnings = off)
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE [tblClassic].* FROM [tblClassic] WITH OWNERACCESS OPTION;", -1
    DoCmd.OpenQuery "AppendToClassic", acNormal, acEdit
    DoCmd.SetWarnings True
    Set rst = db.OpenRecordset("ClassicTicket")
    If rst.BOF And rst.EOF Then
    '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~1TRADEA~1classi~1.doc"
    End If
    Set rst = Nothing
    Set db = Nothing
    End Sub

    I have the above code for opening a mail merged document. As you can see, if there are no records in the source then it displays an error message saying that there are no records. What I want to do is, After displaying this message, I want to open another documnet in word (i could do that) but I want to merge it to a record which has classic in the fund column of table Recepient. Can this be done?
    Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Module Error (Access2000)

    <hr>I want to merge it to a record which has classic in the fund column of table Recepient<hr>
    Sorry, but it isn't clear what you mean by this. You referenced a query, but didn't mention what table or fields are involved in that, you deleted records from a different table, and there is nothing in your code to open a different document if the recordset is empty. Also, is there a reason you're using the particular CreateObject syntax you show?

    Here's what I usually do:

    <pre> Dim objWord As Word.Document
    'instantiate the template document
    Set objWord = GetObject(strTemplateDir & "" & strTmpltNm, "Word.Document")
    ' Make Word visible and maximize
    objWord.Application.WindowState = wdWindowStateMaximize
    objWord.Application.Visible = True
    ' Mail merge to new document
    objWord.MailMerge.Destination = wdSendToNewDocument
    objWord.MailMerge.Execute</pre>


    The template is already bound to a datasource file, so using GetObject opens a new document based on that template and automatically bound to the same data source.
    Charlotte

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

    Re: Module Error (Access2000)

    Sorry charlotte but I have problem explaining (I'm not very good at that)
    Anywayz, let me start from the begining:
    My mail merge is working but I wanted to add something to that code which would allow the user to open another mail merged document which is a fax cover, this document is linked to an access table called Receipient which has the address and tel no of the supliers.
    The following code is for a fund called Classic, if this fund hasn't been entered today, then I don't have to send a ticket to the supplier (Receipient) in this case I want to send a fax stating that we don't have such fund today. I've got about 7 different funds and each have different suppliers so I've designed a general fax cover and mail merged it with the Recipient table, instead of merging all the record on the table to the document, I want to merge the one which is appropriiate for this document, in this case the classic fund. In the Recepient table there is a column which has the fund name. Is there anyway I could state what fund this code is for, if it's empty fetch the record which has the same fund name in the recepient table and open it?

    I need a code which would compare the fund I declare in this code with the fund on the recepient table and merge only that record to the document.

    Sub ClassicFax()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim objWord As Object
    Set db = CurrentDb
    'Run append query to add records to the Classic table (system warnings = off)
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE [tblClassic].* FROM [tblClassic] WITH OWNERACCESS OPTION;", -1
    DoCmd.OpenQuery "AppendToClassic", acNormal, acEdit
    DoCmd.SetWarnings True
    Set rst = db.OpenRecordset("tblClassic")
    If rst.BOF And rst.EOF Then
    MsgBox "There are no records", vbOKOnly
    Set rst = Nothing
    Set db = Nothing
    'Open Fax cover
    Set objWord = CreateObject("Word.Basic")
    objWord.AppShow
    'objWord.AppMaximize "", 1 (optional)
    objWord.FileOpen "S:SRI_WO~1TRADEA~1Fax.doc"
    Exit Sub
    Else
    Set objWord = CreateObject("Word.Basic")
    objWord.AppShow
    'objWord.AppMaximize "", 1 (optional)
    objWord.FileOpen "S:SRI_WO~1TRADEA~1classi~1.doc"
    End If
    Set rst = Nothing
    Set db = Nothing
    End Sub

Posting Permissions

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