Bogus error message “the expression you entered has a function name that Microsoft Access can't find”
Posted on 2006-07-12
I manage a basic customer service and order entry database at a local business. I have a very bizarre problem. I have researched and tried all that I could, for weeks, to no avail. To adequately describe the problem requires a fairly lengthy explanation.
The Access database uses linked tables from SQL Server 2000 (the main tables into which data is entered either by users or programmatically) and from another Access database (a local installation of static lookup tables). The problem started not long after (but not right after) converting a Access database in 2000 format to 2002 format. Converting it back to 2000 format does not fix the problem
There is a Customer form in which is recorded typical customer info (name, address, etc.). Embedded in it is an Order Subform, which records typical order header information (shipping info, payment info, etc.). Embedded in the Order Subform is a Line Item Subform, but that is not relevant here. In the Customer form header there is an “Orders” button that the user can click to shift the view to the Order Subform (page 2 of the Customer form), and a “Customer” button that can be clicked to return to the Customer portion of the form (page 1 of the Customer form) from the Order Subform. Using these buttons, the user can toggle back and forth between viewing customer information and order information. The macro steps that power these buttons are as follows:
Set Value CustButton.Visible No
SetValue OrdButton.Visible Yes
SetValue CustButton.Visible Yes
SetValue OrdButton.Visible No
On the Order Subform, there is a “Calc” button, which is clicked after all order information has been entered to run a macro that performs a variety of calculations and sets values of various fields on the Order Subform (discount, sales tax, shipping charges) AND on the Customer form (the emphasis will be explained shortly).
So typically the user enters or edits customer information (or not, if it is a returning customer), then clicks the “Orders” button to shift to Order Subform, where order information is entered. When that is done, he / she clicks the “Calc” button to complete the order and then the “Customer” button to return to the customer portion of the form.
The problem is that now after clicking the Calc button and then clicking the “Customer” button to return to the customer portion of the form, the following message pops up – “the expression you entered has a function name that Microsoft Access can't find”. Once the OK button is clicked to clear the message, the view moves to customer portion of the form normally
If the user toggles back and forth between the Customer portion and the Order Subform (pages 1 and 2 of the Customer form), there is no problem. And when the Calc button is clicked after taking an order all calculations are done and values set properly. In other words, everything works the way it should, but still there is the error message.
I believe I have discovered where the problem is triggered, but I haven’t a clue as to what the problem is or how to fix it. I tested the Calc macro by eliminating all the lines and then adding them back one at a time until I got the error message. The first line that produced the message was
If [OrderDate]>=Date() SetValue [Forms]![Customer General]![CLDate] [OrderDate]
This line deals with a field on the customer portion of form that records the last date a customer has contacted us, setting that date to the date of the order, provided that the order date is not in the past. However, when I remove this line and add the next line, it too produces the error. That line is
No Condition SetValue [Forms]![Customer General]![InfoOnly] 0
This line deals with a Boolean field on the customer form that is set to True automatically upon entering a new customer to indicate that the customer has received our information but not yet placed an order. This line sets that field to False when the first order is placed.
So either of those two lines can cause the bogus message. Remember that all the calculations and values set by the macro do complete properly, and the message is not generated until the Customer button is clicked to return to the customer portion of the form, yet the message is not produced if the user just toggles between the pages and without running the Calc macro.
Now for the interesting part. The Calc macro has 28 lines. The first 26 run without incident. They reference, evaluate, and set values of fields in the Order Subform, which is where the Calc button is located. The last two lines, those detailed above that seem to trigger the problem, reference and set values of fields on the customer portion of the form. Furthermore, when I move those lines out of the Calc macro and put them in the macro that runs when the “Customer” button is clicked, the values are set properly and the view returns to the customer portion of the form without incident, but when I click on the “Orders” button to toggle back to the Order Subform, the error message pops up.
So there is apparently some relationship between those particular actions of the macro and / or the fields they reference and / or the change of focus from the Customer form to the Order Subform. But what is it, and what could it have to do with a missing function?
There are no missing references noted in the Visual Basic editor. The only function that would seem to be involved is Date(), and certainly that reference is intact, because that function is called in other places in this database without any trouble.
Note that neither the user procedure nor any of the underlying macro steps are new or have been modified for years. As I said, the problem came up shortly after converting the file format from 2000 to 2002, but not right after. It did run fine for a brief period after the conversion.
I’m totally baffled. Please help.
Deleted / Refund
ee ai construct, community support moderator