Undefined function 'PrevRecVal' in expression.

[Wade]

My query contains a field called TotalIn, which returns number data from an underlying table. I have modified a function, PrevRecVal, which returns the datum form the previous record in the TotalIn field and places it into a field called PrevTotalIn. To accomplish this I inserted, into the query, a new field containing the expression referencing the function. I ran the query and it returned the desired result. However, I then saved the module and the mdb file tried to run the query again and got the "Undefined function 'PrevRecVal' in expression." What is happening in the save operation to give this error? Thanks.

[ADezii]

My query contains a field called TotalIn, which returns number data from an underlying table. I have modified a function, PrevRecVal, which returns the datum form the previous record in the TotalIn field and places it into a field called PrevTotalIn. To accomplish this I inserted, into the query, a new field containing the expression referencing the function. I ran the query and it returned the desired result. However, I then saved the module and the mdb file tried to run the query again and got the "Undefined function 'PrevRecVal' in expression." What is happening in the save operation to give this error? Thanks.

Is the Function declared Publically as opposed to Privately?

[Wade]

My function statement reads: Function PrevRecVal (Keyname As String, Keyvalue, FieldNameToGet As String).

I am confused by the sentence in "Microsoft Access 2003 Inside Out": "Use the Public keyword to make this function available to all other procedures in all modules". I want the function to be available to my expression within my query! Translate, please. I have a lot to learn, yet, in MSAccess. Thanks.

[ADezii]

My function statement reads: Function PrevRecVal (Keyname As String, Keyvalue, FieldNameToGet As String).

I am confused by the sentence in "Microsoft Access 2003 Inside Out": "Use the Public keyword to make this function available to all other procedures in all modules". I want the function to be available to my expression within my query! Translate, please. I have a lot to learn, yet, in MSAccess. Thanks.

In order for a Function to be available within an Expression in an Access Query it 'must' be declared Public in a Standard, (not Form), Code Module. e.g.

Public Function PrevRecVal (Keyname As String, Keyvalue, FieldNameToGet As String)

It must also be passed the correct number and Data Types of Arguments as defined in its Declaration. e.g.
PrevRecVal must receive 2 Strings

[NeoPa]

My function statement reads: Function PrevRecVal (Keyname As String, Keyvalue, FieldNameToGet As String).

I am confused by the sentence in "Microsoft Access 2003 Inside Out": "Use the Public keyword to make this function available to all other procedures in all modules". I want the function to be available to my expression within my query! Translate, please. I have a lot to learn, yet, in MSAccess. Thanks.

When a query is run, it is processed by the SQL engine or interpreter. As such, it is not part of any form or report or any database object. It is therefore not local to any modules and is only able to reference globally available (As ADezii has explained) and built-in procedures (functions and subroutines).

Even if you execute a SQL command from within a form's module, that SQL executes in the SQL engine and cannot access anything locally in the form. Does this help to understand why this is as involved as it is?

[Wade]

Yes, that is the just the type of explanation I need. So, when the term "form" is used, does that specifically mean the MS Access Form object or are Tables, Queries, Forms and Reports all considered, generically, forms. Also, just what is a module? What must a module consist of and what all can be included in a module? Thank all of you for your responses. I'm sure all this will become more clear as I study MS Access further. Jeez, I hope these aren't too basic questions for this forum.

[NeoPa]

1. The term 'form' would refer to an MS Access form.
2. Tables; Queries (QueryDefs); Reports; Forms are all objects in an Access database.
3. A module is a container for program code. There are three types
   1. General purpose modules
   2. Object modules (for code in Forms or Reports)
   3. Class Modules - more complicated - support defining classes.

HTH.

[Redrock6]

Is the problem simply that this was a function in Access 97 but does not seem to work in latter versions. I have copied this from an old access version and it still works fine using the earlier version of access.

Previous Odometer: PrevRecVal("ID",[ID],"Odometer") this simply returns the Odometer value from the previous row.

This was actually taken from a query from the free access download Qrysmp97.mdb from Microsoft.

Hope this helps.

[NeoPa]

No. I'm afraid not.
If you read through the thread you will see the reason explained quite fully and clearly :)
Let me know if you have any problem understanding the explanations.