Solved

Bogus error message “the expression you entered has a function name that Microsoft Access can't find”

Posted on 2006-07-12
23
3,499 Views
Last Modified: 2007-11-27
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:

CustPage
      GoToPage      1
      Set Value      CustButton.Visible      No
      SetValue      OrdButton.Visible      Yes
OrdPage
      GoToPage      2
      SetValue      CustButton.Visible      Yes
      SetValue      OrdButton.Visible      No
      RunCommand RecordsGoToLast
      
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
============================================================
0
Comment
Question by:synergycompany
  • 9
  • 4
  • 4
  • +1
23 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17093433
You could have a function call spelt incorrectly

Have u tried doing a debug/compile?

Go to Tools/Options in vba, then select Require Variable Declarations

then do a debug/compile

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17093442
What is the code behind the "Customer" button

0
 

Author Comment

by:synergycompany
ID: 17093925
Thanks so much for taking a stab at this.

Did the Require Variable Declarations thing; no difference.

The "Customer" button click event runs a macro.  Macro actions are detailed in the original question; look for CustPage

Thanks again.  I have to leave for a while, but will check back later for any other ideas that people may have.  BTW, I rated the points on difficulty, because I am experienced with Access but totallly stumped by this one.  The issue is not urgent.  As I said in the question description, everything actually runs as it should.  The bogus message is an irritant to the user, but not limiting work or damaging data.  If others have urgent problems, they should get experts' attention first.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17093944
The common link perhaps is

 [Forms]![Customer General]![

Is this form open when u run the macro?



U can prove Date() is working fine by doing this

public sub testdate
    msgbox Date()
end sub

0
 

Author Comment

by:synergycompany
ID: 17096195
I tested Date() previously in the Immediate Window ( by entering "? Date()") and it worked.

Yes, the form is open when the macro is run.  It is the Customer General form into which is embedded the Orderr Subform.  What is puzzling is that the Calc macro runs properly and produce accurate results, and the page buttons work properly unless the Calc macro is run first, but there is no real connection between the two that I can see, and neither macro produces the error message when run by itself.  Furthermore, the error message doesn't happen if the last two lines (that reference fields on the Customer General form) are left out of the Calc macro, yet there is nothing in those lines that calls a function that would be missing.  As I said, I'm baffled.  I certainly do appreciate your ideas.
0
 

Author Comment

by:synergycompany
ID: 17399929
Thank you for the notice.  I really would like to find a  solution for this, but obviously it has not been attracting much interest.  I have been reluctant to raise the point level because, as I mentioned at the outset, it is not critical in the sense of causing data damage or productivitiy loss.  It is certainly a nuisuance and has defied everyone's best efforts to solve it, which I guess does elevate its stature.  So I would like to give it a 500 point value and see what happens.  If there are no solutions forthcoming and / or no further attempts to solve it after a while, then I guess we can all throw our hands in the air and go on to something else.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17400276
I'll see if I can get some otters involved.
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
ID: 17400457
Hmm, nasty problem and personally I would start with translating the macro's into VBA code to allow better tracebility of the problem e.g. by adding debug.print statements.
Also the goto's and the use of pages is something I don't use. I use a tabbed control when a page is too small to hold all data.

There can be many reasons for Access to start behaving "strange". One is a (slight) corruption that can be tried to solve with these steps:
1) Create a backup of the corrupt database. (Just in case of)

2) Create a new database and use File/Get external data/Import to get all objects of the damaged database.

3) Try these Microsoft solutions:
Repair A97/A2000:
http://support.microsoft.com/support/kb/articles/Q109/9/53.asp
Jetcomp:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q273956
and/or read the article:
ACC2000: How to Troubleshoot Corruption in a Microsoft Access Database
http://support.microsoft.com/default.aspx?scid=kb;en-us;306204

4) Bit "heavier":
Access decompile:
http://www.granite.ab.ca/access/decompile.htm

5) Try a recovery tool / Table rescue
Table datarecovery:
www.mvps.org/access/tables/tbl0018.htm
Access recovery:
http://www.officerecovery.com/access/index.htm

6) Ask a company (will cost $$'s ! )
http://www.pksolutions.com/services.htm

check also: http://www.granite.ab.ca/access/corruptmdbs.htm

Another problem might be triggered by a date field like [Forms]![Customer General]![CLDate] being empty, something that can be tried to prevent with a test.
Fieldnames with an embedded special character like "-", "/", etc. can cause strange effects in A2003.
Finally I've experienced that referencing a field on a form without it being part of the form's recordset can even give a dump :-(

Nic;o)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17400943
<Not for points since I've already posted as a cleanup volunteer>

A couple of far-reaching possibilities:

I've seen this error in applications I've worked on where a function has the same name as a module (doesn't sound like the likely culprit in this case)

Participants in other online discussions claim they have resolved this specific error through software upgrades:
>>I fixed the problem!!!! I updated the Microsoft Jet datablase engine service pack, and the problem
>>was resolved. http://support.microsoft.com/kb/239114

>>I had this problem a few months ago with an Access 2k database. In that case I tried
>>upgrading to the latest Jet SP with no success. It was ultimately resolved by installing the latest VB6 >>runtime.
0
 

Author Comment

by:synergycompany
ID: 17401570
Thank you all.  Tomorrow I will try the various suggestions offered above and report back.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:synergycompany
ID: 17407856
Patience, ee, patience.  Tomorrow has indeed become today, but there's still plenty of it left and I've been busy working with all the above generously offered suggestions in addition to doing a normal day's work.  Anyway, here are the results of my efforts:

I imported all the objects into a new database (which I had done before I initially posted, but it was worth a try again) - no change in the aberrant behavior.

I ran JetComp - no change (not surprising, since this does not seem related to data or data integrity; everything does run and produce accurate results, but then pops up this rogue error message)

I did the Access decompile - no change, but it did run without incident and without the need to then import objects into a new database as the online instructions suggested that it might with Access 2002.  My version is SP3, which apparently does help.

My msjet40.dll version is 4.0.8618.0.  Should be OK, huh?  I should mention that this problem occurs on all the computers at my business running this application, all of which are Windows XP Pro.  I will try to find a Windows 2000 machine to test, just to see if that makes a difference.

I did convert the macro to VBA, groomed it a bit (which often seems necessary with such conversions) and pasted it into an event procedure for the command button formerly used to run the macro.  It delivered the same error message, in the same place, at these lines:

If .ODATE >= Date Then
     Forms![Customer General]![CLDate] = .ODATE
End If
Forms![Customer General]![InfoOnly] = 0

I say "lines" because it will happen when it hits the first line, but if I comment out the first three lines (the "if" block), then it will happen on the fourth line.  If all these lines are removed, everything is fine.  And remember, everything acutally runs properly and all data is updated correctly.  And the bogus error message does not appear until I click the button to return to the top of the page (which means leaving the Order Subform and moving the focus to the body of the Customer General form).  Also worth pointing out again is that these lines are the only ones that refer to controls that outside the current form (i.e. CLDate and InfoOnly are in the Customer General form rather than in the Order Subform).  But again, all this ran fine for years, until a short time ago and no change was made that I am aware of.

I haven't tried using tabs yet, but I will.  Meanwhile any further ideas for solutions or tests are always appreciated.

0
 
LVL 54

Expert Comment

by:nico5038
ID: 17408093
Try:

If .ODATE >= Date Then
     Forms("Customer General")![CLDate] = .ODATE
End If
Forms("Customer General")![InfoOnly] = 0

or better use no spaces in formnames and prefix them like:
If .ODATE >= Date Then
     Forms!frmCustomerGeneral![CLDate] = .ODATE
End If
Forms!frmCustomerGeneral![InfoOnly] = 0

Additionally when the code is behind the "Customer General" form it's best to use:
If .ODATE >= Date Then
    Me![CLDate] = .ODATE
End If
Me![InfoOnly] = 0

Nic;o)
0
 

Author Comment

by:synergycompany
ID: 17408617
I tried all those and unfortunately no change.  Very strange.  To what function could the message be referring?  Just for an extra test, I changed it to this:

If .ODATE >= Forms("Customer General")![CLDate] Then
     Forms("Customer General")![CLDate] = .ODATE
End If
Forms("Customer General")![InfoOnly] = 0

In other words, I took the Date() function out of it and compared the order date (ODATE) to the last customer date (CLDate) instead.  Now there is no function at all referenced in that expression, but the message still pops up, but again, only after all the data is correctly updated and the button is clicked that runs the page switching macro (see my original post).

I'm going to try the tabs idea.  I have created a tabs control before, but it is worth a try.

Thank you again for the atttention you have given this.  I have learned some things just in trying out these ideas.  Any other ideas would be appreciated
0
 

Author Comment

by:synergycompany
ID: 17408629
Woops!  I should have said I have never created a tabs control before.  I can create the control easily enough, but it is not readily apparent how one links the tabs to some action, like changing pages or views.  Macros or code that run on the "click" event?  
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17409148
A tabcontrol is only a "trick" to place fields above one another.
Just place as many tabs as needed (right-click the control and select "Add page") and make sure you select (press) the tab first before placing a field. (The background will be dark when placing a control on the tab).
When the tab isn't selected the field will stay visible when selecting another tab.

Clear ?

Nic;o)
0
 

Author Comment

by:synergycompany
ID: 17415992
Nic,

Yeah, I figured it out once I really started looking at it.  In effect, the tab control really is the form.  I copied all the controls from page 1 of my original form and pasted them on one tab, then I copied the Order Subform control from page 2 and pasted it on another tab, named the tabs, sized the control and form so everything looked good and ran it.  The good (great) news is that when I click the Calc button in the Order Subform (to run all the routines for calculating and updating data) and then tab back to the Customer area of the form, I no longer get the bogus error message.  YES!!!  I have tried it many times, using both the original macro and the converted VBA code and it works fine either way.  We may never know exactly what the cause of the problem was (given the seemingly unrelated events), what little bits or bytes in some far corner of Access was having the tantrum, but it they are now either appeased or bypassed.

One more fairly insignificant question:  Is there any way to make each tab page have a different background color?  I couldn't see any way to do this.

Thank you so much for your help.  I'm sure I would have not figured this out on my own.  I think I will convert all my applications that have forms with embedded subforms to tab controls.  

So collect the points, you earned them, and we'll consider this issue resolved.

John

0
 
LVL 61

Expert Comment

by:mbizup
ID: 17416168
synergycompany,

I just noticed this is your first question here.

>So collect the points, you earned them
You can/should  accept Nico's answer yourself...   Details here:
http://www.experts-exchange.com/help.jsp#hs5
0
 

Author Comment

by:synergycompany
ID: 17416357
mbizup,

Got it. Thanks
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17416367
You're welcome.  I'm a sucker for happy endings :-)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17417312
Glad its solved, for the final question:
> Is there any way to make each tab page have a different background color?
The answer is yes, each tab control can have a colored rectangle as background :-).

You can also add images to the tabs (e.g. before the text) to emphasize the function.

Success !

Nic;o)
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now