Function works fine in debug but not when placed in button

[nickperez22]

I copied a function that Changes a Query Definition from a book on Access.

The function "ChangeQueryDef" works fine from the Debug window however when I'm try to use the function in a button I get a compile/syntax error.

How is it possible to have code working fine in the debug window and yet not work in a button?

 

[DougP]

Not sure
Can you paste the code here

Also is the function Public or Private
Needs to be Public like so

Public Function myfunction
'
End Function DougP, MCP

 

[JPeters]

Also, is the module written with any options? For instance

Public Function WorkForMe(frm as form)

when you call it from the button it should read
WorkForMe Me

Thus setting the form as the current opened form.

If it works in module run mode, then it's probably just not setup as a public function. DougP knows what's up!

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[nickperez22]

Ok, I already developed a work around for my purposes, however I it would be interesting for me and hopefully to others if you could help me figure out what was going wrong in the code:

This is a neat function because it allows you to change a query in code, I used it to create a single report that is automatically modified as the query is modified. The query in turn is modified based on users selecting differnt buttons in an option group.

The function as written in the book is:
Function ChangeQueryDef(strQuery As String, strSQL As String) As Boolean
'
' Purpose: Demonstrates editing a querydef
' Arguments: strSQL The SQL string for the querydef
' Returns: True
' Author: Robert Smith
' Date: 8 June 1997

If strQuery = "" Or strSQL = "" Then Exit Function

Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs(strQuery)
qdf.SQL = strSQL
qdf.Close
RefreshDatabaseWindow

ChangeQueryDef = True

End Function


The example in the book was to run the following from the debug window:

?ChangeQueryDef ("MyQuery", "Select * from [order] where PaidDate = #1/1/97#&quot The example used a table called order but you can substitute any table in your DB)

In the debug window this returned a true, and when I went into the query it had changed, however when I put

ChangeQueryDef ("MyQuery", "Select * from [order] where PaidDate = #1/1/97#&quot in the button it stayed highlighted in red and gave me the syntax error. I used cut and paste several times to make sure it wasn't a typo, and I tried it using various tables. They all worked fine in debug mode but not in the button.

I tried declaring the function as Public but that did not work. I worked around it by naming the Query directly in the function and modifying the first line of code so that the only variable that the function was looking for was "strSQL".

Modified Function:
Public Function ChangeQueryDef(strSQL As String) As Boolean
'
' Purpose: Demonstrates editing a querydef
' Arguments: strSQL The SQL string for the querydef
' Returns: True
' Author: Robert Smith
' Date: 8 June 1997

If strSQL = "" Then Exit Function

Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("MyQuery&quot
qdf.SQL = strSQL
qdf.Close
RefreshDatabaseWindow

ChangeQueryDef = True

End Function

Although this works for me because I only had one query I wanted to update it would be useful to get this function working correctly so that it could be called upon for updating any query.