Delete query object if it exists (1 Viewer)

Hi

I have some Mail Merge code which works fine, but when I go to run it again, it recognises the query object previously created & errors err no 3012 - object already exists.

I want to know how to have the error handler delete the query although sometimes the query would not have been created yet.

So first time I use it, the query isn't an object in the db & so no problem. The second time the query does exist & I get the error.

If I try to add code to delete the query before it's created, I naturally get an error because there's no such object.

Hope that explains it clearly, here's my code, Thanks

--------------------------------------------------------------------Option Explicit
Sub mail_merge(print_records_where As String)
'Declarations
Dim qdftemp As QueryDef
Dim QueryDef As Recordset
Dim mail_merge As QueryDef
Dim rst As Recordset
Dim db As Database
Dim wordapp As Word.Application
Dim cn As ADODB.Connection
Dim maindoc As Word.Document
Dim sdbpath As String

'Initialisations
Set cn = CurrentProject.Connection
Set db = CurrentDb()

If print_records_where = "" Then

print_records_where = " org_id = 0"

End If
'--------------------------------------------------------------------
'I need an error handler here BEFORE the code runs the below to create a new one
'------------------------------------------------------------------
'Query is created here
'-------------------------------------------------------------
'Create temporary QueryDef
Set qdftemp = db.CreateQueryDef("mail_merge", "SELECT tbl_contacts.contact_main_contact, tbl_contacts.contact_salutation, tbl_contacts.contact_first_name, tbl_contacts.contact_surname, tbl_contacts.contact_position, tbl_contacts.contact_email_address, tbl_organisation.org_organisation_name, tbl_organisation.org_board_member, tbl_organisation.org_postal_address, tbl_organisation.org_postal_suburb, tbl_organisation.org_postal_state, tbl_organisation.org_postal_postcode FROM tbl_organisation INNER JOIN tbl_contacts ON tbl_organisation.org_id = tbl_contacts.org_id WHERE " & print_records_where)

'Note print_records_where uses the results of a search function
'--------------------------------------------------------------------
Set rst = qdftemp.OpenRecordset

rst.CopyQueryDef

If rst.RecordCount = 0 Then

MsgBox "There are no Records for your criteria, Try again", vbOKCancel, "No Matches"
'GoTo NoRecords

End If

DoCmd.OpenQuery "mail_merge", acViewNormal, acPreview

If MsgBox("Send Letters to these Recipients?", vbYesNo, "Mail Merge?") = vbYes Then

Set wordapp = New Word.Application
Set maindoc = wordapp.Documents.Open("C:\Documents and Settings\letter2.docx")
wordapp.Visible = True
With maindoc.MailMerge
.MainDocumentType = wdFormLetters
sdbpath = "C:\Documents and Settings\Database.accdb"
.OpenDataSource Name:=sdbpath, SQLStatement:="SELECT * FROM [mail_merge]"
End With

With maindoc
.MailMerge.Destination = wdSendToNewDocument
.MailMerge.Execute
End With
wordapp.Activate
wordapp.Documents.Parent.Visible = True
wordapp.Application.WindowState = 1
wordapp.ActiveWindow.WindowState = 1

Else: GoTo NoRecords

End If

NoRecords:
DoCmd.Close acQuery, "mail_merge"
db.QueryDefs.Delete qdftemp.Name

End Sub
'----------------------------------------------------------------

Thanks

noboffinme,

Can you please put your code in code tags so it's more readable.

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

OK, is this what you mean?

--------------------------------------------------------------------

'Note print_records_where uses the results of a search function
'--------------------------------------------------------------------

Here's the answer if anyone needs it, Cheers

Add this function to a module

noboffinme said:

Here's the answer if anyone needs it, Cheers

Click to expand...

Instead of creating and deleting querydefs, qhat you need to do is create the mail_merge query as a blank query, i.e. it should just have SELECT;

Then anytime you want to use the query, simply change its SQL like this:

noboffinme said:

Thanks vbaInet for the tip about the

Code:Copy to clipboard

 option.
[[COLOR=Red]\[/COLOR]code]
 [/QUOTE]No problem but you've got the closing tag wrong. it's with a backslash, i.e. [COLOR=Blue]/[/COLOR] not a[COLOR=Red] \[/COLOR]

Click to expand...

Great, Thanks again