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?1 033 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.