By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,039 Members | 1,831 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,039 IT Pros & Developers. It's quick & easy.

runtime -expression can't find function

P: 23
When opening an mde users get a message (like) a date (Format(Now(),"yyyy") expression has a function that Microsoft Access cannot find. I checked all the references and re-registered them. Same message. Any help is greatly appreciated. Alan
Oct 13 '07 #1
Share this Question
Share on Google+
25 Replies


nico5038
Expert 2.5K+
P: 3,072
Check the references on the "error machines".

Open some VBA code (e.g. by pressing [CTRL+G]) and select Tools/References.
Each libraryname starting with "MISSING" must be unchecked and (e.g. microsoft DAO version x.xx) replaced when necessary.

As access doesn't cleanup added libraries, often unnecessary refenreces are present.
Just delete as many as possible without getting an error executing "Debug/compile all modules"

Nic;o)
Oct 13 '07 #2

NeoPa
Expert Mod 15k+
P: 31,494
When opening an mde users get a message (like) a date (Format(Now(),"yyyy") expression has a function that Microsoft Access cannot find. I checked all the references and re-registered them. Same message. Any help is greatly appreciated. Alan
Check the code you're using and, just for testing purposes, determine which function is causing the error (by removing all other function calls). I'm afraid I'm not very familiar with MDEs but I'm sure once you've isolated the problem we can find some way of resolving it for you.
@Nico - I hadn't realised that Ctrl-G takes you straight to the Immediate Pane even directly from the main Access window :)
Oct 14 '07 #3

P: 23
Hi
Thanks for responding. The form is called birthday and has a control based on date(). The form also has an animation that bounces a ball on the person's birthday. This is based on the following expression in oncurrent event:
Expand|Select|Wrap|Line Numbers
  1. If Format(DATE, "mm/dd") = Me!Birthdate Then
  2.         Me.TimerInterval = 1000
  3.          Me.AnimateOn = Not Me.AnimateOn
  4.     Else
  5.         Me.TimerInterval = 0
  6.         N.ForeColor = vbGreen
  7.     End If
This works on my second computer but not on the end users. I checked the references on their computer and regsvr them. Maybe they weren't pointing to them???
Oct 14 '07 #4

nico5038
Expert 2.5K+
P: 3,072
Hmm, an mde is a bit complexer as an .mdb.
Try http://www.mvps.org/access/modules/mdl0022.htm to check your references to be OK.

Nic;o)
Oct 14 '07 #5

NeoPa
Expert Mod 15k+
P: 31,494
Alan,

You don't seem to have isolated the call which is causing your problem. This should still be your first step I think.
Oct 14 '07 #6

P: 23
The error message was "the expression (Format(DATE, "mm/dd"...) has a function that MS Access can't find." This is the if statement. Remember this worked on my second computer and I registered all the references on the user's computer. I did get the wzref file and am putting it in the app(doesn't work as an addin so imported). I can delete references with the wiz but can't add any. When I try I get a message "can't add a non-vba reference to the reflibpaths registry key" but I can see what references are there an if any are broken. There is a good article at http://www.trigeminal.com/usenet/usenet026.asp?1033 regarding disambiguation and expressions. Alan
Oct 15 '07 #7

NeoPa
Expert Mod 15k+
P: 31,494
Alan,

When I say you haven't isolated the call I'm referring to the actual function call and not the line of code. Please revisit post #3 for my original instructions and we can hopefully proceed. When we know the actual function we can check out the available information for that function and see where that gets us.
Oct 15 '07 #8

P: 15
Why don't you try like this

Date.Now.Year();
Oct 15 '07 #9

P: 23
NeoPa: Thanks for your reply
The call is within the current module. There is an animated eye that bounces on the person's birthday:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. ' flag to identify whether animation is on
  4. ' or off, used in the form custom property procedures.
  5. Private g_AnimateState As Boolean
  6. ' Form property used to set the timerInterval property.
  7. Public TimerVal As Integer
  8. ' Image control object.
  9. Dim img As Image
  10. ' Animation properties used to move the image control.
  11. Public h_max As Integer
  12. Public v_max  As Integer
  13. Public H_Move As Integer
  14. Public V_Move As Integer
  15.  
  16.  
  17. Property Get AnimateOn() As Boolean
  18.     ' Read the custom property AnimateOn.
  19.     AnimateOn = g_AnimateState
  20. End Property
  21. Property Let AnimateOn(bolState As Boolean)
  22.     ' SEt the custom property AnimateOn.
  23.     If bolState = True Then
  24.         ' Turn animation on.
  25.         Me.TimerInterval = Me.TimerVal
  26.     Else
  27.         ' Turn animation off.
  28.         Me!image1.PictureData = Me!ImageEarth.PictureData
  29.         Me.TimerInterval = 0
  30.     End If
  31.     g_AnimateState = bolState
  32. End Property
  33.  
  34. Private Sub Form_Close()
  35. DoCmd.OpenForm "reminders"
  36. Dim F As Form
  37. Set F = Forms!reminders
  38. Dim ctl As TextBox
  39. Set ctl = F!text
  40. If IsNull(ctl) Then
  41. DoCmd.Close acForm, "reminders"
  42. 'DoCmd.OpenForm "switchboard"
  43. Else
  44. F.SetFocus
  45. End If
  46.  
  47. End Sub
  48.  
  49. Private Sub Form_Resize()
  50.     ' Set the movement boundaries based on the current window size.
  51.     On Error Resume Next
  52.     h_max = Me.WindowWidth - Me!image1.Width
  53.     If h_max < 0 Then h_max = 0
  54.     v_max = Me.WindowHeight - (Me!image1.Height + 400)
  55.     If v_max < 0 Then v_max = 0
  56.     DoCmd.Restore
  57.     ' Locate message in middle of display area.
  58.     'Me!lblMessage.Left = (Me.WindowWidth / 2) - (Me!lblMessage.Width / 2)
  59.     'Me!lblMessage.Top = (Me.WindowHeight / 2) - (Me!lblMessage.Height / 2)
  60.  
  61. End Sub
  62.  
  63. Sub MoveImage(ctlImage As Image)
  64.     ' This procedure moves the image control horizontally and
  65.     ' vertically depending on the specified settings.
  66.  
  67.     Dim V_Pos As Integer, H_Pos As Integer
  68.  
  69.     On Error Resume Next
  70.  
  71.     H_Pos = ctlImage.Left + H_Move
  72.     ' Determine whether to move left to right, or to use the (* -1)
  73.     ' feature to change movement to right to left.
  74.     If H_Pos < 0 Then
  75.         H_Pos = 0
  76.         H_Move = H_Move * -1
  77.     ElseIf H_Pos > h_max Then
  78.         H_Pos = h_max
  79.         H_Move = H_Move * -1
  80.     End If
  81.  
  82.     V_Pos = ctlImage.Top + V_Move
  83.     ' Determine whether to move top to bottom, or to use the (* -1)
  84.     ' feature to change movement to bottom to top.
  85.     If V_Pos < 0 Then
  86.         V_Pos = 0
  87.         V_Move = V_Move * -1
  88.     ElseIf V_Pos > v_max Then
  89.         V_Pos = v_max
  90.         V_Move = V_Move * -1
  91.     End If
  92.     ' Move the image control.
  93.     ctlImage.Left = H_Pos
  94.     ctlImage.Top = V_Pos
  95. End Sub
  96.  
  97.  
  98. Private Sub image1_Click()
  99.     ' Toggle image animation.
  100.     Me.AnimateOn = Not Me.AnimateOn
  101. End Sub
  102.  
  103.  
  104. Private Sub Form_Current()
  105. Dim D As TextBox
  106. Dim birth As TextBox
  107. Dim N As TextBox
  108. Dim I As Integer
  109. Set N = Me![name]
  110. Set D = Me![DATE]
  111.  
  112.  
  113. If Format(DATE, "mm/dd") = Me!Birthdate Then
  114.         Me.TimerInterval = 1000
  115.          Me.AnimateOn = Not Me.AnimateOn
  116.     Else
  117.         Me.TimerInterval = 0
  118.         N.ForeColor = vbGreen
  119.     End If
  120. End Sub
  121.  
  122. Private Sub Form_Open(Cancel As Integer)
  123. ' Initialize the custom property AnimateOn
  124.  Me.AnimateOn = False
  125.  ' Initialize the object variable representing the image control.
  126.  Set img = Me!image1
  127.  ' Initialize the form property used to set the timerInterval property.
  128.  Me.TimerVal = 100
  129.  ' Initialize the variables used to move the image control during animation.
  130.  H_Move = 150
  131.  V_Move = 150
  132.  'HideSwitchboard
  133.  
  134. Dim ctl As TextBox
  135. Set ctl = Me!name
  136. If IsNull(ctl) Then
  137. DoCmd.Close
  138. End If
  139. 'DoCmd.OpenForm "switchboard"
  140. End Sub
  141.  
  142. Private Sub Form_Timer()
  143.  ' Resets TimerInterval if the value has been changed by a new
  144.     ' setting on the frmAnimationPopup form.
  145.     Me.TimerInterval = Me.TimerVal
  146.     ' Save the value of each image used when the Timer event fires.
  147.     Static intCntr As Integer
  148.     If intCntr = 0 Then intCntr = 2
  149.  
  150.     ' Rotate the images within the image control.
  151.     Select Case intCntr
  152.         Case 2
  153.             img.PictureData = Me!image2.PictureData
  154.         Case 3
  155.             img.PictureData = Me!image3.PictureData
  156.         Case 4
  157.             img.PictureData = Me!image4.PictureData
  158.         Case 5
  159.             img.PictureData = Me!Image5.PictureData
  160.         Case 6
  161.             img.PictureData = Me!Image6.PictureData
  162.         Case 7
  163.             img.PictureData = Me!Image7.PictureData
  164.         Case 8
  165.             img.PictureData = Me!Image8.PictureData
  166.         Case 9
  167.             img.PictureData = Me!Image9.PictureData
  168.     End Select
  169.     intCntr = intCntr + 1
  170.     If intCntr = 10 Then intCntr = 2
  171.  
  172.     ' Move the image control within the window display area.
  173.     MoveImage img
  174.  
  175. Dim ctl As Control
  176. Dim N As TextBox
  177. Dim D As TextBox
  178. Set D = Me![DATE]
  179. Set N = Me![name]
  180. Set ctl = Me![Birthdate]
  181. N.SetFocus
  182.  
  183. With N
  184. .ForeColor = (IIf(.ForeColor = vbRed, vbBlue, vbRed))
  185. End With
  186. With D
  187. .ForeColor = (IIf(.ForeColor = vbGreen, vbRed, vbGreen))
  188. End With
  189.  
  190. End Sub
Also there is a sql that shows the dob 10 days before the birthday:
Expand|Select|Wrap|Line Numbers
  1. WHERE (((Format([DOB],"mm/dd")) Between Format(Now(),"mm/dd") And Format(Now()+10,"mm/dd"))
That's it. Thanks for your help-Alan
Oct 15 '07 #10

NeoPa
Expert Mod 15k+
P: 31,494
Alan,

I can see that we're having difficulties communicating. That's fine. Let me try again.
What I would like you to do, to provide some helpful information, is to create a test version of your database which has these function calls on separate lines. That way, we can identify which particular function call is causing your issue. I suspect that somewhere along the line one of the (probably standard) functions which is fully available to an MDB file is not available to an MDE file. When we know which it is we can do some searching around (I certainly don't already know what is causing your problem) to see if we can find some documentation somewhere that tells us that certain functions are not supported in an MDE.
It may be possible, at this stage, to arrange to add the necessary links explicitly. It may be necessary to provide the same functionality separately, in one of your own modules, in order to get past this.
The first step of course is for you to identify which function it is that is being complained about.
Does this make more sense?
Oct 15 '07 #11

P: 23
I understand but with my limited experience am unsure of how to find the function call that is causing the problem. Can you please be specific about what I need to do to provide this. I looked online to figure this out so I would save you the trouble of explaining but didn't find anything. Excuse my limited experience. What should I do to provide this info? Alan
Oct 15 '07 #12

nico5038
Expert 2.5K+
P: 3,072
Hmm, an mde is a bit complexer as an .mdb.
Try http://www.mvps.org/access/modules/mdl0022.htm to check your references to be OK.

Nic;o)
Did you try this link above ?
In general a missing standard function is caused by a MISSING reference...

Nic;o)
Oct 15 '07 #13

P: 23
Yes I downloaded this wiz. It was able to identify references within an MDE, to delete them but not add. I don't believe the referencing is the problem. NeoPa How can I find the offending expression in the mde? Alan
Oct 15 '07 #14

NeoPa
Expert Mod 15k+
P: 31,494
Alan,

That's fine. I appreciate you made the attempt ;)
Try rewriting the Form_Current() procedure (only in the test version of your database - not the live) as :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim D As TextBox
  3. Dim birth As TextBox
  4. Dim N As TextBox
  5. Dim I As Integer
  6. Dim datThis As DateTime
  7. Dim strDate As String
  8. Set N = Me![name]
  9. Set D = Me![DATE]
  10.  
  11. datThis = Date()
  12. strDate = Format(datThis, "mm/dd")
  13. If strDate = Me!Birthdate Then
  14.     Me.TimerInterval = 1000
  15.     Me.AnimateOn = Not Me.AnimateOn
  16. Else
  17.     Me.TimerInterval = 0
  18.     N.ForeColor = vbGreen
  19. End If
  20. End Sub
When the error message comes up it will now be clear from the line number reported what it is complaining about.
As I've now gone through this particular piece of code in context, it appears that the problem may well be the fact that you have a control called DATE (which is a reserved word so must always be referenced explicitly. That means it is not good to say just DATE as you have). If you want the Date() function then that's fine but referencing the control should either be [DATE] or Me.DATE or such like. Try the date thing first - if that fixes it then forget the rest. If not proceed as earlier instructed.
@Nico from the first post I think the OP has already checked the library references.
Oct 15 '07 #15

P: 23
NeoPa Thanks for the advice I will try this and let you know if the user has any problems. Thanks again Alan
Oct 16 '07 #16

NeoPa
Expert Mod 15k+
P: 31,494
No problems Alan. Good Luck.
Oct 16 '07 #17

P: 23
Thanks all. The answer to the riddle is none of the above. You have to put the openform in the autoexec macro and then all is well. If you have a form with an expression and put it in the startup it doesn't work. Does this make sense. Thanks microsoft.
Oct 19 '07 #18

nico5038
Expert 2.5K+
P: 3,072
Hmm, not sure that a different way of opening a form can have this effect.
It's the use of the timer that's more suspicious for me. Have seen too many unpredictable results because of that.
Did you test the startupform option without the timer?

Nic;o)
Oct 19 '07 #19

NeoPa
Expert Mod 15k+
P: 31,494
Alan,

I'm pleased you got your problem solved, although a little confused by the answer if truth be told, but the posts above were not suggested solutions. They were attempts to help you debug the problem to find the correct starting point, from where we could look at finding what was the underlying cause of the problem.
It's good that you found something that works for you anyway :)
Oct 19 '07 #20

P: 23
Help!!
Honest it loaded fine. At least it did the first and only time. I get the message: function not available in expression 'Format([DOB],"mm/dd")'. Any help figuring this out is appreciated. Alan
Oct 20 '07 #21

P: 23
OOPS! My original solution worked. The if not file file script I wrote for the bat file that puts files on the user's computer was messed up. thanks again for everyone's help. Alan
Oct 20 '07 #22

P: 23
I found out the whole problem was that the msword.olb file didn't copy to the office11\directory. Another wild goose chase!! Thanks for coming along. Alan
Oct 20 '07 #23

NeoPa
Expert Mod 15k+
P: 31,494
I guess that means you're all sorted now Alan?
Oct 20 '07 #24

nico5038
Expert 2.5K+
P: 3,072
Hmm, then I guess a reference was missing after all....

Nic;o)
Oct 20 '07 #25

NeoPa
Expert Mod 15k+
P: 31,494
; )
Oct 21 '07 #26

Post your reply

Sign in to post your reply or Sign up for a free account.