Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Undefined Function, Name Conflicts, Just a Nightmare

    I have a function that I grabbed online that should serve my purposes. After putting the function in a new module, and attempting to call it, I get the "Undefined Function" error. If I create another simple function in the same module for testing purposes, it works fine.

    This leads me to believe there is something in the function that Access doesn't like. I notice that the function contains DAO. So after googling I see suggestions that I need to enable the Microsoft DAO library from the References area. But when i do that, i get another error: "Name conflicts with existing module, project, or object...".

    I've googled this and there are no "Missing" references, I've tried compacting the DB, uncompiling, compiling, etc. Nothing seems to fix this. A while back, I did upgrade from 2007 to 2010. Short of blowing away office and re-installing, which is not really feasible, does anyone else have ideas about this?

    Here is the function in question:
    Code:
    Public Function ConcatRelated(strField As String, _
        strTable As String, _
        Optional strWhere As String, _
        Optional strOrderBy As String, _
        Optional strSeparator = ", ") As Variant
    On Error GoTo Err_Handler
        'Purpose:   Generate a concatenated string of related records.
        'Return:    String variant, or Null if no matches.
        'Arguments: strField = name of field to get results from and concatenate.
        '           strTable = name of a table or query.
        '           strWhere = WHERE clause to choose the right values.
        '           strOrderBy = ORDER BY clause, for sorting the values.
        '           strSeparator = characters to use between the concatenated values.
        'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
        '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
        '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
        '           4. Returning more than 255 characters to a recordset triggers this Access bug:
        '               http://allenbrowne.com/bug-16.html
        Dim rs As DAO.Recordset         'Related records
        Dim rsMV As DAO.Recordset       'Multi-valued field recordset
        Dim strSql As String            'SQL statement
        Dim strOut As String            'Output string to concatenate to.
        Dim lngLen As Long              'Length of string.
        Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
        
        'Initialize to Null
        ConcatRelated = Null
        
        'Build SQL string, and get the records.
        strSql = "SELECT " & strField & " FROM " & strTable
        If strWhere <> vbNullString Then
            strSql = strSql & " WHERE " & strWhere
        End If
        If strOrderBy <> vbNullString Then
            strSql = strSql & " ORDER BY " & strOrderBy
        End If
        Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
        'Determine if the requested field is multi-valued (Type is above 100.)
        bIsMultiValue = (rs(0).Type > 100)
        
        'Loop through the matching records
        Do While Not rs.EOF
            If bIsMultiValue Then
                'For multi-valued field, loop through the values
                Set rsMV = rs(0).Value
                Do While Not rsMV.EOF
                    If Not IsNull(rsMV(0)) Then
                        strOut = strOut & rsMV(0) & strSeparator
                    End If
                    rsMV.MoveNext
                Loop
                Set rsMV = Nothing
            ElseIf Not IsNull(rs(0)) Then
                strOut = strOut & rs(0) & strSeparator
            End If
            rs.MoveNext
        Loop
        rs.Close
        
        'Return the string without the trailing separator.
        lngLen = Len(strOut) - Len(strSeparator)
        If lngLen > 0 Then
            ConcatRelated = Left(strOut, lngLen)
        End If
    
    Exit_Handler:
        'Clean up
        Set rsMV = Nothing
        Set rs = Nothing
        Exit Function
    
    Err_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
        Resume Exit_Handler
    End Function
    from here: Microsoft Access tips: Concatenate values from related records

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This line seem to be dubious:
    Code:
        Dim rsMV As DAO.Recordset       'Multi-valued field recordset
    As far as I know, you need to use a DAO.Recordset2 when working with multi-valued field. see: Recordset2 Object (DAO)
    Have a nice day!

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Well here is another interesting development. If I copy this DB to a coworkers computer, it does not work, same error as me. But if he creates the module/function on his machine it works, and if i copy back to my machine, it works. So i'm thinking it has to be something messed up with my machine.

  4. #4
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    Clawlan,

    You can not name a module with the same name as the function.

    Was that your problem?

    Wayne

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by WayCal View Post

    ...You can not name a module with the same name as the function...
    I've never seen this mistake generating an "Undefined Function" error message, but it's quite possible that this is the problem! The Access Gnomes really don't like it when you give a Standard Module and a Function the same name, but apparently there is no error message specifically assigned to this error, so they use whatever error message is at hand, none of which is appropriate!

    One way to avoid this is to always use the mod prefix for Module names, i.e. modModuleName.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •