Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

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.
0
maianoel
Asked:
maianoel
  • 3
  • 3
  • 2
  • +2
1 Solution
 
stevbeCommented:
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
0
 
pique_techCommented:
What is your code for ExportResults?  Without that, I'm not sure we can help.
0
 
Data-ManCommented:
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
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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

Hope this helps
0
 
maianoelAuthor Commented:
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

0
 
pique_techCommented:
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?
0
 
Data-ManCommented:
does the compile work?  can you set a breakpoint in the event procedure and then F8 into the code?

Mike
0
 
maianoelAuthor Commented:
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.
0
 
pique_techCommented:
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?
0
 
Data-ManCommented:
put your cursor in the function and press F8....does that work?

Mike
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now