David & Joanne Gould
dajomigo at tpg.com.au
Wed Dec 7 05:58:48 CST 2005
Thank you both for your suggestions. I will try them properly tomorrow
after I have had som sleep.
David
At 10:17 PM 7/12/2005, Gustav Brock wrote:
>Hi David
>
>Or adjust this variation with a saved query which I posted recently:
>
>Here's a variation where you save the SQL to look up the Qualifications in
>a separate query assuming that your primary key, VolNum, is a number:
>
> PARAMETERS lngKey Long;
> SELECT
> QualificationCode
> FROM
> tblQualifications
> WHERE
> VolNum = lngKey
> ORDER BY
> QualificationCode;
>
>Save this as, say, qdyQualifications.
>
>Now, adjust you main query to include the function below to look something
>like this:
>
> SELECT
> *,
> ConcatenateRecords('qdyQualifications',[VolNum],'QualificationCode',', ')
> FROM
> tblVolunteers;
>
>/gustav
>
><code>
>
>Public Function ConcatenateRecords( _
> ByVal strSource As String, _
> ByVal lngKey As Long, _
> ByVal strField As String, _
> Optional ByVal strSeparator As String = ";") _
> As String
>
>' Concatenates values from one field (strField) from all
>' records in query strSource using parameter value lngKey.
>' Values are separated by strSeparator.
>' Default output like:
>' 34;56;34;67;234
>'
>' 1999-10-12. Cactus Data ApS, CPH
>
> Dim dbs As DAO.Database
> Dim qdf As DAO.QueryDef
> Dim rst As DAO.Recordset
> Dim fld As DAO.Field
>
> Dim booPluralis As Boolean
> Dim strFields As String
>
> On Error GoTo Err_ConcatenateRecords
>
> Set dbs = CurrentDb()
>
> If Len(strSource) > 0 And Len(strField) > 0 Then
> Set qdf = dbs.QueryDefs(strSource)
> qdf.Parameters(0) = lngKey
> Set rst = qdf.OpenRecordset()
> Set fld = rst.Fields(strField)
>
> With rst
> While Not .EOF
> If booPluralis = True Then
> ' There is more than one record.
> ' Add separator.
> strFields = strFields & strSeparator
> End If
> strFields = strFields & Trim(fld.value)
> booPluralis = True
> .MoveNext
> Wend
> .Close
> End With
>
> Set fld = Nothing
> Set rst = Nothing
> Set qdf = Nothing
> End If
>
> Set dbs = Nothing
>
> ConcatenateRecords = strFields
>
>Exit_ConcatenateRecords:
> Exit Function
>
>Err_ConcatenateRecords:
> MsgBox "Error " & Err.Number & ". " & Err.Description
> Resume Exit_ConcatenateRecords
>
>End Function
>
></code>
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
At 10:27 PM 7/12/2005, Stuart Sanders wrote:
>The code was likely written using Miscrosoft DAO rather than ADO which is
>set by default in Access 2003.
>
>If you feel like the exercise you can probably convert the code to ADO,
>but since I haven't looked at it I don't know how much other ado specific
>code it contains.
>
>For a simple solution, the easiest thing would be to add a reference to
>DAO and change the declares for database and recordset to DAO.Database and
>DAO.recordset.
>
>To change the reference, from a code window, click on the tools menu and
>then references...
>
>The one you are looking for is called the Microsoft DAO x.x Object
>Library. In my case its 3.6, but I'm not using access 2003.
>
>Make sure the reference stays below the Microsoft ActiveX Data Objects
>library so that your existing code doesn't need to change.
>
>Stuart
>
>
>-----Original Message-----
>From: David & Joanne Gould <dajomigo at tpg.com.au>
>To: Access Developers discussion and problem solving
><accessd at databaseadvisors.com>
>Date: Wed, 07 Dec 2005 22:05:08 +1100
>Subject: [AccessD] Concatenating data into lists
>
> > I have been trying to learn how to use Dev Ashish's wonderful module to
> > turn data from a child table into a comma delimitered list. It works
> > perfectly every time I use Northwind to test it and even works if I
> > import
> > tables and queries into Northwind from another database. But I can't
> > get it
> > to work in any other database. One line in the code has me thinking
> > (Set db
> > as Database - I am using Access XP and it doesn't recognise this code.
> > But
> > I just comment it out and Northwind is happy.
> >
> > I don't get any error messages, it just doesn't put anything into the
> > query
> > field. Anyone who wants to see an example of what I have been working
> > on, I
> > can send a copy of the test database (only about 20kb zipped)
> >
> > TIA
> >
> > David Gould
> > DG Solutions
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com