runtime -expression can't find function

[allj]

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

[nico5038]

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)

[NeoPa]

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 :)

[allj]

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:

If Format(DATE, "mm/dd") = Me!Birthdate Then
        Me.TimerInterval = 1000
         Me.AnimateOn = Not Me.AnimateOn
    Else
        Me.TimerInterval = 0
        N.ForeColor = vbGreen
    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???

[nico5038]

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)

[NeoPa]

Alan,

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

[allj]

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

[NeoPa]

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.

[KnightHack]

Why don't you try like this

Date.Now.Year();

[allj]

NeoPa: Thanks for your reply
The call is within the current module. There is an animated eye that bounces on the person's birthday:

Option Compare Database
Option Explicit
' flag to identify whether animation is on
' or off, used in the form custom property procedures.
Private g_AnimateState As Boolean
' Form property used to set the timerInterval property.
Public TimerVal As Integer
' Image control object.
Dim img As Image
' Animation properties used to move the image control.
Public h_max As Integer
Public v_max  As Integer
Public H_Move As Integer
Public V_Move As Integer
 
 
Property Get AnimateOn() As Boolean
    ' Read the custom property AnimateOn.
    AnimateOn = g_AnimateState
End Property
Property Let AnimateOn(bolState As Boolean)
    ' SEt the custom property AnimateOn.
    If bolState = True Then
        ' Turn animation on.
        Me.TimerInterval = Me.TimerVal
    Else
        ' Turn animation off.
        Me!image1.PictureData = Me!ImageEarth.PictureData
        Me.TimerInterval = 0
    End If
    g_AnimateState = bolState
End Property
 
Private Sub Form_Close()
DoCmd.OpenForm "reminders"
Dim F As Form
Set F = Forms!reminders
Dim ctl As TextBox
Set ctl = F!text
If IsNull(ctl) Then
DoCmd.Close acForm, "reminders"
'DoCmd.OpenForm "switchboard"
Else
F.SetFocus
End If
 
End Sub
 
Private Sub Form_Resize()
    ' Set the movement boundaries based on the current window size.
    On Error Resume Next
    h_max = Me.WindowWidth - Me!image1.Width
    If h_max < 0 Then h_max = 0
    v_max = Me.WindowHeight - (Me!image1.Height + 400)
    If v_max < 0 Then v_max = 0
    DoCmd.Restore
    ' Locate message in middle of display area.
    'Me!lblMessage.Left = (Me.WindowWidth / 2) - (Me!lblMessage.Width / 2)
    'Me!lblMessage.Top = (Me.WindowHeight / 2) - (Me!lblMessage.Height / 2)
 
End Sub
 
Sub MoveImage(ctlImage As Image)
    ' This procedure moves the image control horizontally and
    ' vertically depending on the specified settings.
 
    Dim V_Pos As Integer, H_Pos As Integer
 
    On Error Resume Next
 
    H_Pos = ctlImage.Left + H_Move
    ' Determine whether to move left to right, or to use the (* -1)
    ' feature to change movement to right to left.
    If H_Pos < 0 Then
        H_Pos = 0
        H_Move = H_Move * -1
    ElseIf H_Pos > h_max Then
        H_Pos = h_max
        H_Move = H_Move * -1
    End If
 
    V_Pos = ctlImage.Top + V_Move
    ' Determine whether to move top to bottom, or to use the (* -1)
    ' feature to change movement to bottom to top.
    If V_Pos < 0 Then
        V_Pos = 0
        V_Move = V_Move * -1
    ElseIf V_Pos > v_max Then
        V_Pos = v_max
        V_Move = V_Move * -1
    End If
    ' Move the image control.
    ctlImage.Left = H_Pos
    ctlImage.Top = V_Pos
End Sub
 
 
Private Sub image1_Click()
    ' Toggle image animation.
    Me.AnimateOn = Not Me.AnimateOn
End Sub
 
 
Private Sub Form_Current()
Dim D As TextBox
Dim birth As TextBox
Dim N As TextBox
Dim I As Integer
Set N = Me![name]
Set D = Me![DATE]
 
 
If Format(DATE, "mm/dd") = Me!Birthdate Then
        Me.TimerInterval = 1000
         Me.AnimateOn = Not Me.AnimateOn
    Else
        Me.TimerInterval = 0
        N.ForeColor = vbGreen
    End If
End Sub
 
Private Sub Form_Open(Cancel As Integer)
' Initialize the custom property AnimateOn
 Me.AnimateOn = False
 ' Initialize the object variable representing the image control.
 Set img = Me!image1
 ' Initialize the form property used to set the timerInterval property.
 Me.TimerVal = 100
 ' Initialize the variables used to move the image control during animation.
 H_Move = 150
 V_Move = 150
 'HideSwitchboard
 
Dim ctl As TextBox
Set ctl = Me!name
If IsNull(ctl) Then
DoCmd.Close
End If
'DoCmd.OpenForm "switchboard"
End Sub
 
Private Sub Form_Timer()
 ' Resets TimerInterval if the value has been changed by a new
    ' setting on the frmAnimationPopup form.
    Me.TimerInterval = Me.TimerVal
    ' Save the value of each image used when the Timer event fires.
    Static intCntr As Integer
    If intCntr = 0 Then intCntr = 2
 
    ' Rotate the images within the image control.
    Select Case intCntr
        Case 2
            img.PictureData = Me!image2.PictureData
        Case 3
            img.PictureData = Me!image3.PictureData
        Case 4
            img.PictureData = Me!image4.PictureData
        Case 5
            img.PictureData = Me!Image5.PictureData
        Case 6
            img.PictureData = Me!Image6.PictureData
        Case 7
            img.PictureData = Me!Image7.PictureData
        Case 8
            img.PictureData = Me!Image8.PictureData
        Case 9
            img.PictureData = Me!Image9.PictureData
    End Select
    intCntr = intCntr + 1
    If intCntr = 10 Then intCntr = 2
 
    ' Move the image control within the window display area.
    MoveImage img
 
Dim ctl As Control
Dim N As TextBox
Dim D As TextBox
Set D = Me![DATE]
Set N = Me![name]
Set ctl = Me![Birthdate]
N.SetFocus
 
With N
.ForeColor = (IIf(.ForeColor = vbRed, vbBlue, vbRed))
End With
With D
.ForeColor = (IIf(.ForeColor = vbGreen, vbRed, vbGreen))
End With
 
End Sub

Also there is a sql that shows the dob 10 days before the birthday:

WHERE (((Format([DOB],"mm/dd")) Between Format(Now(),"mm/dd") And Format(Now()+10,"mm/dd"))

That's it. Thanks for your help-Alan

[NeoPa]

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?

[allj]

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

[nico5038]

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)

[allj]

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

[NeoPa]

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 :

Private Sub Form_Current()
Dim D As TextBox
Dim birth As TextBox
Dim N As TextBox
Dim I As Integer
Dim datThis As DateTime
Dim strDate As String
Set N = Me![name]
Set D = Me![DATE]
 
datThis = Date()
strDate = Format(datThis, "mm/dd")
If strDate = Me!Birthdate Then
    Me.TimerInterval = 1000
    Me.AnimateOn = Not Me.AnimateOn
Else
    Me.TimerInterval = 0
    N.ForeColor = vbGreen
End If
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.

[allj]

NeoPa Thanks for the advice I will try this and let you know if the user has any problems. Thanks again Alan

[NeoPa]

No problems Alan. Good Luck.

[allj]

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.

[nico5038]

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)

[NeoPa]

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 :)

[allj]

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

[allj]

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

[allj]

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

[NeoPa]

I guess that means you're all sorted now Alan?

[nico5038]

Hmm, then I guess a reference was missing after all....

Nic;o)

[NeoPa]

; )