Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • 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-ManCOOCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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-ManCOOCommented:
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-ManCOOCommented:
put your cursor in the function and press F8....does that work?

Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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