compile error sub or function not defined

[tranchemontaigne]

I'm confused (again)

Here is my code...Every time I try to call this functin from the immediate window run it I receive the "Compile error: Sub or Function not defined"

I have added lines of code, compiled the database outside of break mode, saved the module, deleted the lines of code I just added, compiled the database outside of break mode, and retried calling the code with no change in results

I get the same error regardless of whethe rI place this function in a form module or a code module.

Option Compare Database
Option Explicit

Private Function fnTEST()

MsgBox "done"

End Function

Any thoughts would be appreciated.
________
GM 54-DEGREE V6 ENGINE HISTORY

 

[boblarson]

How about using

Public Function fnTEST()

or just

Function fnTEST()

instead of PRIVATE

 

[tranchemontaigne]

This works to fix the problem, but makes no sense to me

I typically try to limit the scope of functions to a specific module whenever the function will not be called elsewhere. This addresses accidental function overloading when I'm copying/pasting code from another project into a current project. It also allows more of an object based coding model to be implemented.

What am I missing?

Am I coding in bad form?
________
Health Store

 

[boblarson]

This works to fix the problem, but makes no sense to me

I typically try to limit the scope of functions to a specific module whenever the function will not be called elsewhere.

Yes, that is good. I would expect that you should be able to call it from the same module when it is private and if that was what you were doing then I'm not sure why you were getting the error, unless you were trying to access it from ANYWHERE outside of the module it was in.

Am I coding in bad form?

No, I don't think so.

 

[tranchemontaigne]

Thanks Bob,

I appreciate the response. In answer to your questions I tried running the function by placing the cursor inside the function code block before calling it from the immediate window. I also tried clicking on the CONTINUE button in the VBA IDE's Standard toolbar. Both efforts invoked (and still invoke) the sub or function not defined error if this functino is declared as private.....

I guess we are both stumped by this behavior....
________
MARIJUANA

 

[tranchemontaigne]

ARGHHHHH

The problem just came back when when adding a new function.

This time declaring the function as public does not solve the problem.....

Function fnClearEvents()
Dim strsql As String

strsql = "DELETE * FROM t08_Events;"

docmd.RunSQL(strsql)

strsql = "UPDATE t04_Agreement_Master SET t04_Agreement_Master.t04_Process_Flow_ID = Null;"

docmd.RunSQL(strsql)

MsgBox "done"

End Function

Any thoughts would be appreciated.
________
Roor bongs

 

[georgedwilkinson]

Try this:

Public Sub fnClearEvents()
Dim strsql As String

strsql = "DELETE * FROM t08_Events;"

docmd.RunSQL(strsql)

strsql = "UPDATE t04_Agreement_Master SET t04_Agreement_Master.t04_Process_Flow_ID = Null;"

docmd.RunSQL(strsql)

MsgBox "done"

End Sub

 

[tranchemontaigne]

Thanks - but changing the declaration from a function to a sub does not remove the error.

<WISH>
I wish MS Access was a little more intuitive - this kind of error drives me nuts...
</WISH>
________
Edsel corsair

 

[georgedwilkinson]

I can't imagine why this happens in a code module. I just tried your simple code in several different types of modules and got exactly the result you would expect: it doesn't work in form/report modules but it does work in a regular module.

 

[tranchemontaigne]

You are a genius! But why on earth would a form module be different from a code module?

I always thought that a module was a module was a module....
________
Glass Pipe Pictures

 

[Rabbie]

You are a genius! But why on earth would a form module be different from a code module?

I always thought that a module was a module was a module....

Functions in Code modules are generally accessible from everywhere in your DB. Form modules are restricted to within the form.

 

[georgedwilkinson]

You are a genius!

Dang, I must be good, then.

Glad you got it figured out.

 

[tranchemontaigne]

Rabbie,

I understand how the scope of a code module is different from the scope of a form or report module.

I jut find it hard to comprenend why a function within a form module cannot be executed as a private function from a function call from the same form module.

<RANT>
I'm guessing that some MS designer must have been up all night figuring out how to make the compiler "smart" enough to choke when certain DDL and DML language is executed. I'm guessing that this might have been justified by making it harder for a malicious form or report to alter database tables...but we are talking about MS Access now and any MS Access database that is open for form edits is also open for direct table edits.
</RANT>

Since my rant is based upon a guess, I still find it perplexing that some functions need to be written in code modules and are prohibited from form modules.

I think we can all agree that calling a function from another module necessitates the function becoming public.



georgedwilkinson and boblarson
Thanks greatly
________
Lamborghini lm002

 

[georgedwilkinson]

You should be able to create code in your form's module. You just can't invoke it from the Immediate window (which is outside of the form).

 

[tranchemontaigne]

Now that makes sense! Thanks georgedwilkinson
________
Babi mac

 

[boblarson]

You should be able to create code in your form's module. You just can't invoke it from the Immediate window (which is outside of the form).

Yeah, I forgot about that.