Using MS Access/Cont: Function Error

Advertisement


Question
Scott:
Here is a direct copy/paste of what is in my module:

Public Function fConcatFlds()
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Function fConcatFld(stTable As String, _
         stForFld As String, _
         stFldToConcat As String, _
         stForFldType As String, _
         vForFldVal As Variant) _
         As String
'Returns mutiple field values for each unique value
'of another field in a single table
'in a semi-colon separated format.
'
'Usage Examples:
'   ?fConcatFld(("Customers","ContactTitle","CustomerID", _
'          "string","Owner")
'Where  Customers     = The parent Table
'       ContactTitle  = The field whose values to use for lookups
'       CustomerID    = Field name to concatenate
'       string        = DataType of ContactTitle field
'       Owner         = Value on which to return concatenated CustomerID
'
Dim lodb As Database, lors As Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Const cQ = """"

   On Error GoTo Err_fConcatFld
   
   lovConcat = Null
   Set lodb = CurrentDb
   
   loSQL = "SELECT [" & stFldToConcat & "] FROM ["
   loSQL = loSQL & stTable & "] WHERE "
   
   Select Case stForFldType
       Case "String":
         loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQ
       Case "Long", "Integer", "Double":    'AutoNumber is Type Long
         loSQL = loSQL & "[" & stForFld & "] = " & vForFldVal
       Case Else
         GoTo Err_fConcatFld
   End Select
   
   Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)
   
   'Are we sure that duplicates exist in stFldToConcat
   With lors
       If .RecordCount <> 0 Then
         'start concatenating records
         Do While Not .EOF
         lovConcat = lovConcat & lors(stFldToConcat) & "; "
         .MoveNext
         Loop
       Else
         GoTo Exit_fConcatFld
       End If
   End With
       
   'That's it... you should have a concatenated string now
   'Just Trim the trailing ;
   fConcatFld = Left(lovConcat, Len(lovConcat) - 2)


Exit_fConcatFld:
   Set lors = Nothing: Set lodb = Nothing
   Exit Function

Err_fConcatFld:
   MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
   Resume Exit_fConcatFld
End Function

Answer
OK, I think I see the problem Delete everything above

Function fConcatFld(stTable As String, _

And change that to Public Function. That should do it.

Then use fConcatFld(....) in your query.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.