format() function gives #name on report

Posted on 2014-07-16
Last Modified: 2014-07-28
I have 20 pc's using my Access 2005 application. Just one of these pc's has a problem that when the user previews a report which uses the format() function, she gets #Name instead of the correct value.

I seem to remember seeing this years ago and it turned out to be a .dll with the wrong version, but I can't remember anything more. Can someone point me in the right direction please?
Question by:TownTalk
    LVL 21

    Expert Comment

    by:Randy Poole
    Have you attempted to set the default printer to something else and still see if it happens?

    Author Comment

    Thanks for the reply. I tried a different default printer and got the same result. Last time this happened I had to paste in a .dll from another pc. I can't remember which .dll though.
    LVL 48

    Expert Comment

    by:Gustav Brock
    You most likely have a missing reference issue on that machine.
    Go to (in code) menu Tools, References and check out or correct any reference labelled MISSING.


    Author Comment

    Thanks for the reply Gustav. My users are using .ACCDE files in runtime Access. They cannot get into that screen.
    LVL 48

    Accepted Solution

    Then create a small test app with the same references and run that as an accdb on the machine in question.


    Author Comment

    Hmmm..... i just copied my master .accdb file onto that pc and it wouldn't open at all. It faulted in my opening procedure, but because it's only runtime access it wouldn't show me the module window.

    It's the end of the day here, and i'm not in the office tomorrow. So i'm going to pick this up on Friday. Thanks for your help so far.


    Author Closing Comment

    Because the machine is question is running Access Runtime, there is no possibility to bring up a code window or to look for missing references. So for a quiet life, I wrote myself a function which mimics what I need the format() function to do. So now my problem is fixed. I accepted your answer in order to close down this thread. Thanks for your input.

    LVL 48

    Expert Comment

    by:Gustav Brock
    You could try running this code:
    Public Function VerifyReferences(ByVal booErrorDisplay As Boolean) As Boolean
    ' Verify Access' external references and re-establish these if possible.
    ' Uses function IsBroken97().
    ' 2001-07-29. Cactus Data ApS, CPH.
    ' 2003-07-23.
    '   Changes:
    '   Added call to DoCmd.Echo True to allow for display of MsgBox.
    '   Removed call to SysCmd() compilation which did not work.
      Dim refA                    As Access.Reference
      Dim refX                    As Access.Reference
      Dim strRefFullPath          As String
      Dim booNotBuiltInRefExists  As Boolean
      Dim booIsBroken             As Boolean
      Dim booRefIsMissing         As Boolean
      Dim strMsgTitle             As String
      Dim strMsgPrompt            As String
      Dim strMsgHeader            As String
      Dim strMsgFooter            As String
      Dim lngMsgStyle             As Long
      Dim strCrLf                 As String
      ' No special error handling.
      On Error Resume Next
      ' User oriented error message.
      strMsgTitle = "Missing support file"
      strMsgHeader = "One or more supporting files are missing:" & vbCrLf
      strMsgFooter = vbCrLf & vbCrLf & "Report this to IT support." & vbCrLf
      strMsgFooter = strMsgFooter & "Program execution cannot continue."
      lngMsgStyle = vbCritical + vbOKOnly
      ' Look for the first reference in the database other than
      ' the built in "Access" and "Visual Basic for Applications".
      For Each refA In Access.Application.References
        If refA.BuiltIn = False Then
          ' At least one not built in reference is in use.
          booNotBuiltInRefExists = True
          ' Check if the reference is not broken.
          If IsBroken97(refA) = False Then
            ' The first not missing not built in reference is found.
            Set refX = refA
            Exit For
          End If
        End If
      If booNotBuiltInRefExists = False Then
        ' Only built in references are in use.
        ' Nothing more to do.
        If refX Is Nothing Then
          ' All not built in references are missing.
          ' Don't remove missing references as there is no way to
          ' re-establish a reference if its identity is lost.
          ' Remove this not built in reference and add it back to
          ' force Access to revalidate all references.
          ' This may or may not rebuild links to missing references.
          With Access.Application.References
            strRefFullPath = refX.FullPath
            .Remove refX
            .AddFromFile strRefFullPath
          End With
          Set refX = Nothing
        End If
        ' Check references if any should be missing.
        ' If so, no attempt to read a reference is done as it most likely
        ' either is not installed or has been moved to an unknown directory.
        For Each refA In Access.Application.References
          booIsBroken = IsBroken97(refA)
          If booIsBroken = True Then
            ' Build list of missing files.
            strMsgPrompt = strMsgPrompt & vbCrLf & refA.FullPath
          End If
          booRefIsMissing = booRefIsMissing Or booIsBroken
        ' If any reference is broken, display error message if requested.
        If booRefIsMissing = True And booErrorDisplay = True Then
          strMsgPrompt = strMsgHeader & strMsgPrompt & strMsgFooter
          Access.DoCmd.Echo True
          VBA.MsgBox strMsgPrompt, lngMsgStyle, strMsgTitle
        End If
      End If
      Set refA = Nothing
      ' If References have been updated, the application is left decompiled.
      ' Run command in AutoExec macro to compile and save all modules.
      VerifyReferences = Not booRefIsMissing
    End Function
    Public Function IsBroken97(ByVal ref As Access.Reference) As Boolean
    ' Alternative method to check if a reference is broken
    ' as the IsBroken property cannot be used in Access97.
    ' 2000-03-19. Gustav Brock. Cactus Data ApS.
    ' Refer to this article at Microsoft Technet:
    ' Article ID: Q186720
    ' The information in this article applies to:
    ' Microsoft Access 97
    ' In Microsoft Access, IsBroken is a property of the References collection.
    ' The Microsoft Access Help topic on the Isbroken property states the following:
    ' The IsBroken property returns a Boolean value indicating whether a
    ' Reference object points to a valid reference in the Windows Registry.
    ' Although this statement is correct, to receive this Boolean value
    ' you must trap for errors that are generated by the broken reference.
    ' Also, the IsBroken property becomes True only when the file being referenced
    ' is deleted and the Microsoft Windows Recycle Bin is emptied.
    ' This article details the steps necessary to receive the Boolean value.
      Dim booRefOK As Boolean
      On Error GoTo Err_IsBroken97
      If Len(Dir(ref.FullPath, vbNormal)) > 0 Then
        booRefOK = Not ref.IsBroken
      End If
      IsBroken97 = Not booRefOK
      Exit Function
      ' Ignore non existing servers, drives, and paths.
      Resume Exit_IsBroken97
    End Function

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    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.

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    758 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

    11 Experts available now in Live!

    Get 1:1 Help Now