asked on

Problem calling a function

OK, I am having a problem calling a Function in one of my subroutines:

Private Sub ExportResults_Click()
Dim qdQD As QueryDef

DoCmd.DeleteObject acQuery, "qryExport_results"

Set qdQD = CurrentDb.CreateQueryDef("qryExport_results", Sql)

ExportResults CAN'T CALL THIS

End Sub

When I attempt to export the document, I receive an error indicating "Invalid use of property." I am not sure what the problem is. It worked fine in Access 2000, but we just got Access 2003 and it no longer works.

stevbe

Why not just chnage the SQL property instead of deleting and re-creating?

Private Sub ExportResults_Click()
CurrentDb.QueryDefs("qryExport_results").SQL = Sql
End Sub

Steve

pique_tech

What is your code for ExportResults? Without that, I'm not sure we can help.

Data-Man

can you debug | Compile?

what about using the Call keyword

Call ExportResults

I try and prefix all my functions with fn and subs with sb to avoid any keyword conflicts. Also is ExportResults a function or a sub?

Mike

pdebaets

The subroutine might have a problem because the variable "sql" is undefined or empty.

Hope this helps

maianoel

ASKER

Sorry. The code for ExportResults is below. Also, I tried Call ExportResults and I still got the same error.

Function ExportResults()
Dim strFilter As String
Dim lngFlags As Long
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If strSaveFileName = "" Then Exit Function
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExport_results", strSaveFileName
MsgBox "Export of File complete."

End Function

pique_tech

Let me ask a clarifying question:
Your code snippet appears to be from a button's On Click event. It appears that you delete and recreate a query then you call a sub, ExportResults. Is it that call to ExportResults that is failing, or is it the button ExportResults_Click event that's failing?

ASKER CERTIFIED SOLUTION

Data-Man

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

maianoel

ASKER

It appears it just the function call that's failing. Clicking the button appears to create the query correctly, it just runs into some problem when calling the ExportResults function.

pique_tech

If you debug/single-step through the code, is the failure point within this sub or in the button event's attempt to call it?

Data-Man

put your cursor in the function and press F8....does that work?

Mike