Unqualified Reference Causing Error

[GaryCanuck]

Hi All,

I have a somewhat messy chunk of code to run a mailmerge for 2 documents. I've attempted to set up and qualify all of my variables but it appears that I haven't succeeded. The code will run as desired the first time (on click event of a button). And then error the second time which is usually from unqualified variables. The error says the remote server in unavailable. I'm using access 2007 and word 2007. If anyone can spy the coding error I would be grateful. My guess is that it has something to do with the recordset because the error comes after the second query runs. Any help always appreciated!

On Error GoTo Err_Command16_Click

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Discharge"
    DoCmd.SetWarnings True
                
                Dim db As DAO.Database
                Set db = OpenDatabase("\\Taxerver\taxervice\Access\TaxSale_be.mdb")                                 
                District = InputBox("Enter District", "Name")
                db.Execute "UPDATE Discharge SET District = '" & District & "' "
                dischargeno = InputBox("Enter Discharge of Instrument No.", "Name")
                
                numthree = InputBox("Who is Applicant? Donna (d) or Muni (m)?")
                    
                    If numthree = "d" Then
                    db.Execute "UPDATE Discharge SET numthree2 = 'I am a designated officer and have authority to bind same pursuant to by-law Registered as No. ' "
                    db.Execute "UPDATE Discharge SET Signer = 'Don' "
                    db.Execute "UPDATE Discharge SET Title = 'Sale Manager' "
                    End If
                    If numthree = "m" Then
                    db.Execute "UPDATE Discharge SET numthree3 = '' "
                    db.Execute "UPDATE Discharge SET numthree1 = 'I am an employee of the Corporation and have authority to bind same' "
                    End If
                    
                    fp = InputBox("Full (f) or Partial (p) Discharge?")
        
                    If fp = "f" Then
                    db.Execute "UPDATE Discharge SET Dischargex1 = 'X' "
                    db.Execute "UPDATE Discharge SET Otherx1 = 'X' "
                    db.Execute "UPDATE Discharge SET fulldisno = '" & dischargeno & "' "
                    db.Execute "UPDATE Discharge SET specify1 = 'Notice of Sale' "
                    End If
                    If fp = "p" Then
                    db.Execute "UPDATE Discharge SET Dischargex2 = 'X' "
                    db.Execute "UPDATE Discharge SET Otherx2 = 'X' "
                    db.Execute "UPDATE Discharge SET partdisno = '" & dischargeno & "' "
                    db.Execute "UPDATE Discharge SET specify2 = 'Notice of Tax Sale' "
                    db.Execute "UPDATE Discharge SET seesched = 'A' "
                    End If
                    
        Dim objWord As Word.Document
        Set objWord = GetObject("\\Taxerver\taxervice\Templates\Discharge.doc", "Word.Document")

        objWord.Application.Visible = True
        If fp = "f" Then
        objWord.Application.Run "ScratchMacro"              
        End If
        If fp = "p" Then
        objWord.Application.Run "ScratchMacro2"
        End If
        objWord.Application.Visible = False
        objWord.MailMerge.Execute
        objWord.Close False
        
        
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "Discharge Schedule"
        DoCmd.SetWarnings True
                
                Dim dbs As Database
                Dim Lrs As DAO.Recordset
                
                Set dbs = OpenDatabase("\\Taxerver\taxervice\Access\TaxSale_be.mdb")
                Set Lrs = db.OpenRecordset("Discharge Schedule", dbOpenDynaset)
              
            
                If Lrs.RecordCount = 0 Then
                
                MsgBox ("There are no records for the schedule.")
                Word.Application.Quit SaveChanges:=wdDoNotSaveChanges
                
                Else
                               
                Dim bWord As Word.Document
                Set bWord = GetObject("\\Taxerver\taxervice\Templates\Discharge Schedule.doc", "Word.Document")

                bWord.Application.Visible = True
                bWord.MailMerge.Execute
                bWord.Close False
                End If
        
        Lrs.Close
        Set bWord = Nothing
        Set Lrs = Nothing
        Set dbs = Nothing
        Set objWord = Nothing
        Set db = Nothing
                
 
Exit_Command16_Click:
    Exit Sub

Err_Command16_Click:
    MsgBox Err.Description
    Resume Exit_Command16_Click

 

[Andrzejek]

Which line of your code couses the problem?

This is not the answer to your question but when you do:

db.Execute "UPDATE Discharge SET Dischargex2 = 'X' "
db.Execute "UPDATE Discharge SET Otherx2 = 'X' "
db.Execute "UPDATE Discharge SET partdisno = '" & dischargeno & "' "
db.Execute "UPDATE Discharge SET specify2 = 'Notice of Tax Sale' "
db.Execute "UPDATE Discharge SET seesched = 'A' "

does your [tt]Discharge[/tt] table have just 1 record? You do not have any WHERE part of your Update statement, so if you have more than just 1 record - you are updating ALL the records in your table.

And - if so - why so many Updates, why not just:

db.Execute "UPDATE Discharge SET Dischargex2 = 'X', " & _
   " Otherx2 = 'X', " & _
   " partdisno = '" & dischargeno & "', " & _
   " specify2 = 'Notice of Tax Sale', " & _
   " seesched = 'A' "

Have fun.

---- Andy

 

[GaryCanuck]

I do not know which line of the code is causing the problem. Just that the error occurs after the call to run the second query. I can't seem to figure out how to step through it.

As for your suggestion, yes there is only one record and so it functions without a where clause. Thanks for the suggestion of combining the updates.

 

[Andrzejek]

When you are in VBA editor, go to Tools - Options... - General Tab and in Error Trapping frame select the Break on All Errors option

This should stop on the problem line of code.

BTW - do you have Option Explicit on the top of your code?

Have fun.

---- Andy

 

[PHV]

Replace this:
Word.Application.Quit
with this:
objWord.Application.Quit

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GaryCanuck]

Okay so I did those things (thanks for the tips) and it errors on this line:

Set Lrs = db.OpenRecordset("Discharge Schedule", dbOpenDynaset)

The error says it can't find "Discharge Schedule" However again this error only shows up on the second time the document runs. The first time it runs it successfully identifies that there are no records in the table.

Suggestions?

 

[GaryCanuck]

PHV,
I tried your suggestion and when I do that I get the error that "the object invoked has already disconnected from its client". This occurs on the first time the document is run, where it normally only errors the second time. Any other ideas?

 

[PHV]

So, simply get rid of this line.

 

[GaryCanuck]

If I take out that line the it will run the first time, and error on this line the second time:

objWord.Application.Run "ScratchMacro"

The application.Quit line is there for a reason though, without it an instance of word stays open in the background. I'm assuming this is from the instance of word opened when the mailmerge creates a new document. Objword variable is referring to the template which is indeed closed.

 

[GaryCanuck]

I ended up moving my recordsource chunk to the beginning of the code to separate it from the word references. This seemed to do the trick for now. Thanks kindly to the both of you for your replies.