They broke Access on me!

[Sue Reitz]

I have an Access database with a VB module that calculates some reserves for me. It ran just fine at the end of the 3rd quarter, but now it doesn't. I get an error message that says "Compile Error: Sub or Function not defined."

The help file that went with the error listed a bunch of possible causes. There are only two that could possibly apply (only because I don't understand enough to eliminate them!) These 2 possibilities are:

1. You declared a Windows dynamic-link library (DLL) routine or Macintosh code-resource routine, but the routine isn't in the specified library or code resource.

2. Check the ordinal (if you used one) or the name of the routine. Make sure your version of the DLL or Macintosh code-resource is the correct one. The routine may only exist in later versions of the DLL or Macintosh code-resource. If the directory containing the wrong version precedes the directory containing the correct one in your path, the wrong DLL or Macintosh code-resource is accessed. You gave the right DLL name or Macintosh code-resource, but it isn't the version that contains the specified function.

I asked our IT people if they had made any changes to Access that might have caused this error. The silence has been deafening.

Does anyone have any ideas about how I can fix this or what I can do check on those 2 possible causes for my error? Although I wrote the function myself, I did it by going to the help files and trying a whole bunch of stuff until it did what I wanted it to do. So, since I really don't know very much about VB for Access, any replies should be typed VEEERRRY slowly and clearly.

Thanks!

[Dr T Non-Fan]

Have you replaced your PC since last quarter? Sometimes bits and pieces don't get replicated. I don't know how to fix that.

[Sue Reitz]

Yeah, I've had that happen to me in the past too! But no, this PC is a little over a year old now.

I know our IT people come into my office and do stuff to my PC on nights, weekends, lunch breaks,etc. I know this because they turn off my NumLock and turn on my Scroll Lock. I certainly don't know because they tell me that they're going to be doing something, much less WHAT they're going to do.

[NoName]

VB should point out for you the line that causes the error. If you can tell which sub or function VB is trying to call, find out where it is defined. Can you see the definition of the function in your code, or is there a declaration with a "Lib" clause meaning that it is in a DLL, such as

Declare Sub MessageBeep Lib "User32" (ByVal N As Long)

or is it not defined or declared anywhere? Someone might be able to help you with some more information.

[Sue Reitz]

It's a function that I call from a Macro.

i.e. I click on the Macro Button titled "Calculate PER Reserves", the macro does a couple of things and then has the line "RunCode" with an extra line showing that the code is the function called "CalcPERReserves()"

The first line in that function, "Function CalcPERReserves()," is the one that generates the error. Should I add the coding you told me about? I've never defined a function in the way that you described. I've just used them once I've created them. Where would I do this definition? With the stuff at the top of the module? (There are 2 lines there, the first says "Option Compare Database", the second says "Option Explicit". Since I don't know what they do, I just keep them.)

[NoName]

The text there was an example of a declaration, not something for you to type.

VB is trying to call a function that it cannot find.

First, you should see the name of the function - not the function that is trying to do the calling, but the function (or sub) that it is trying to call. Let's say this is a Sub called xyz.

Search for "Sub xyz" (or Function xyz, if it is a function) and see if xyz is defined or declared anywhere.

If it is, either it is "defined" - that is, you have the code in the module and VB should be able to find it - or it is "declared" with a line similar to the one in my earlier post (starting with "Declare Sub xyz").

If it is neither defined or declared, the mystery is why you found it last time - perhaps a module was somehow removed.

If it is defined, VB should be able to find it and if it not I don't know what to tell you.

If it is declared and VB is not finding it, most likely a certain DLL (the file specified in the "LIB" clause of the Declare statement) is missing or has been changed.

[Sue Reitz]

The line:

Function CalcPERReserves()

is the very first line of my code in the module called "Calculate CARVM Reserves for PER". I'm looking at it right now. In fact, I've been looking at it since yesterday afternoon. It's the line the debugger sends me to when I click on the macro button. It's also the line the debugger sends me to when I try to run the function directly.

Do I have to define the function somewhere else? If so, where and how?

[Sue Reitz]

BTW, thank you for your patience with me!

[Dorothy]

I don't know much about Access either, but I'll take a stab.

My guess is that the function may be trying to use an argument that doesn't exist or is missing or misspelled.

I ususally get this type of error when I have declared a variable but subsequently misspelled its name later in the macro code.

Another guess would be that the function is defined, but not available to that module, but since you haven't changed anything, I don't think that is it.

One way to find the error would be to step through the macro in the code window, looking for something that should be assigned a value but retains the 'Empty' or 'Nothing' value.

[TwistedMentat]

Sue,

Why do you have to use a RunCode command? If the function is properly defined, you should just say Function(parameters) and it should run.

RunCode also may not be supported in later versions(perhaps the one you are using), as it is actually a leftover macro command that MS is trying to move away from.

I think??

[Sue Reitz]

My guess is that the function may be trying to use an argument that doesn't exist or is missing or misspelled.

If, by this, you mean parameters that go in a function i.e. function_name(parameter1, parameter2), this function has no parameters. It's not returning a value, it's reading a file line by line, calculating reserves for each line, and outputting the reserves to another file. I don't think the arguments defined within the function are a problem either since the function doesn't even start running.

I ususally get this type of error when I have declared a variable but subsequently misspelled its name later in the macro code.

I thought of this too and reselected the function name in my macro. No typing, just pick the function from a list. Besides, the function doesn't run when I'm looking at the VB code and click on the run button. So the problem isn't in the macro, it's in the VB code. I'm going to check the VB code for errors in variable names. But, darn it, I don't think I touched this code since I last ran it. I did add another function in another module in the database, but this function was not touched!

One way to find the error would be to step through the macro in the code window, looking for something that should be assigned a value but retains the 'Empty' or 'Nothing' value.

The function won't even start running.

Another guess would be that the function is defined, but not available to that module, but since you haven't changed anything, I don't think that is it.

The function is there. Any time I click on the little wand thingie (the "Build Expression" icon) I can see it.


Why do you have to use a RunCode command? If the function is properly defined, you should just say Function(parameters) and it should run.

I'm sure I don't have to use "RunCode", but that was the only way I found to call a function from a macro. I just now looked for something that said "Function" in the list of macro commands and couldn't find it so I guess I'll stick with "RunCode" for now. Besides, I use "RunCode" at an earlier stage in the macro and THAT function ran just fine. It's the function I'm calling that's broken, not the macro.

RunCode also may not be supported in later versions(perhaps the one you are using), as it is actually a leftover macro command that MS is trying to move away from.

I think??

I'm using Access 2000, the same version I used in October. That's not to say that IT hasn't upgraded it in the last couple of months without telling me. But I don't think Microsoft takes away commands in upgrades, don't they save annoying stuff like that for new versions? Besides, as I said, I KNOW "RunCode" still works.

Thanks again for all the suggestions!

[Sue Reitz]

Ok. This one really makes me look like a moron.

Every quarter, the queries I ran against my reserve file bombed because I always forgot to change the data type of my plan code to numeric from string. It doesn't take long to make the change in data type, but I would spend 30 minutes investigating the problem and then say, "Oh Yeah! I forgot!"

So last quarter, after my 30 minute investigation, I solved the problem once and for all by changing the code from: OutputRec![Plan]= InputRec![Plan] to: OutputRec![Plan]= Value(InputRec![Plan])

It was a brilliant idea, but Access has a Val() function, not a Value() function. It only took me 12 hours to notice that "Value" was highlighted in blue and to remember my brilliant idea from last October.

This means that I now have to run this program 24 quarters before I realize a time savings from my brilliant idea. ARRGH!!!

[TwistedMentat]

Ok. This one really makes me look like a moron.

It was a brilliant idea, but Access has a Val() function, not a Value() function. It only took me 12 hours to notice that "Value" was highlighted in blue and to remember my brilliant idea from last October.

This means that I now have to run this program 24 quarters before I realize a time savings from my brilliant idea. ARRGH!!!
Okay, but when you show the database to others, don't mention how long it took to debug this problem. Just let them consider you a genius for designing it in the first place. :)