Remove querydef

[Ruegen]

I'm using code below to make whatever the record source is an excel spreadsheet - two functions are to work together and make it easier for me to do export to.

I can't find the fault in the error. I have this picking out multiple subforms and I get errors or the wrong recordsource (if it doesn't delete the original object querydef it uses the same one over for some reason)

help :O

'save as dialog box
Public Function SaveAsLocation(Optional filename As String) As String
Dim strButtonCaption As String
Dim strDialogTitle As String, strfilename As String
 
'Define your own Captions if necessary
strButtonCaption = "Select a Folder"
strDialogTitle = "Folder Selection Dialog"
If Not IsMissing(filename) Then
strfilename = filename
Else
strfilename = ""
End If
 
With Application.FileDialog(msoFileDialogSaveAs)
  .ButtonName = strButtonCaption
  .InitialView = msoFileDialogViewDetails     'Detailed View
  .title = strDialogTitle
  .InitialFileName = strfilename
     If .SHOW Then
      SaveAsLocation = .SelectedItems(1)         'Write Folder Path to Field
     End If
End With
End Function

'gives you excel of records
Public Sub ExportToExcel(RecordSource As String, filename As String)
If FindQuery("QueryExportToExcel") Then
CurrentDb.QueryDefs.Delete "QueryExportToExcel"
End If


Dim qryname As String
Dim qdfNew As QueryDef
On Error GoTo ErrorHandler
Dim strSave As String


strSave = SaveAsLocation(filename)
qryname = "QueryExportToExcel"
Set qdfNew = CurrentDb.CreateQueryDef(qryname, RecordSource)


Debug.Print strSave
DoCmd.TransferSpreadsheet acExport, 8, qryname, strSave & ".xls", True
Debug.Print RecordSource
CurrentDb.QueryDefs.Delete qdfNew.Name 'cleanup
ErrorHandler:
   Resume Next
End Sub

'find query
Function FindQuery(strQueryName) As Boolean
Dim dbs As dao.Database
Dim found As Boolean
Set dbs = CurrentDb


On Error GoTo fail
'next line succeeeds without error if qry exists
found = Len(dbs.QueryDefs(strQueryName).Name) > 0


exithere:
FindQuery = found




fail:
'no query of that name
found = False
Resume exithere
End Function

[John_G]

Hi -

You have an error (two errors, actually) in the FindQuery function - it is returning False every time it is called. Right now if the Found test succeeds, the code sets FindQuery = Found. That really should be FindQuery = True, but it still works because any non-zero boolean value is True.

But that isn't the main problem - after setting Findquery = True, the code continues, sets Found = False, then sets
FindQuery = Found.

So FindQuery is always false.

Put Exit Function after the FindQuery = Found (Findquery = True).

John

[Ruegen]

Hi -

You have an error (two errors, actually) in the FindQuery function - it is returning False every time it is called. Right now if the Found test succeeds, the code sets FindQuery = Found. That really should be FindQuery = True, but it still works because any non-zero boolean value is True.

But that isn't the main problem - after setting Findquery = True, the code continues, sets Found = False, then sets
FindQuery = Found.

So FindQuery is always false.

Put Exit Function after the FindQuery = Found (Findquery = True).

John

Thanks - the problem I get in that at the exporttoexcel function

If FindQuery("QueryExportToExcel") Then
CurrentDb.QueryDefs.Delete "QueryExportToExcel"
End If

I get an error at
CurrentDb.QueryDefs.Delete "QueryExportToExcel"

[Ruegen]

I changed
If FindQuery("QueryExportToExcel") = false Then

and it skipped the error... not sure as to why

[John_G]

Hi -

If FindQuery("QueryExportToExcel") Then ...
and
If FindQuery("QueryExportToExcel") = false Then...

are the opposite of each other.

If FindQuery("QueryExportToExcel") is the same as If FindQuery("QueryExportToExcel") = True

[Ruegen]

Hi -

If FindQuery("QueryExportToExcel") Then ...
and
If FindQuery("QueryExportToExcel") = false Then...

are the opposite of each other.

If FindQuery("QueryExportToExcel") is the same as If FindQuery("QueryExportToExcel") = True

Yes but I thought the way that I made it that i it finds a query def of name then = true

however it is of false

I guess I could swap the function bool around in the function...

[John_G]

Hi -

Did you fix the FindQuery function with the addition of the Exit Function line?

It should be OK otherwise.

What is the error that CurrentDb.QueryDefs.Delete "QueryExportToExcel" gives you?

[Ruegen]

Hi -

Did you fix the FindQuery function with the addition of the Exit Function line?

It should be OK otherwise.

What is the error that CurrentDb.QueryDefs.Delete "QueryExportToExcel" gives you?

Can't quite remember but I think it was missing object error - yeah updated the code as specified in your post. Thanks