William Hindman
wdhindman at bellsouth.net
Wed Dec 7 07:06:57 CST 2005
...just a couple of further notes ...AccessXP defaults to ADO, Dev's module
is written in DAO ...I try not to mix the environments simply because it
makes code maintenance a pita ...if I'm using Jet I use DAO because, ime,
its much faster and frankly, I know it a lot better ...and MS has essentialy
abandoned ADO so I'm not investing any more time than necessary in it ...but
if I'm using anything but Jet as my be, then ADO is probably the way to go
...the point being that while you can mix the environments, you then have to
set both references and remember which you've defaulted to (placed first in
the ref list) ...Stuart places the ADO reference above the DAO and I do just
the opposite ...it all depends on your local needs as to which works best or
you ...in your case I suspect that if you place the DAO 3.6 ref above the
ADO ref, Dev's code will play fine without any further changes ...but it
"might" screw up other code in your mdb ...and it might not if you've
learned to ignore most MS program samples and fanatically define your dbs as
ADO or DAO ...just my 2 cents, I'm sure others will demur :)
William
----- Original Message -----
From: "David & Joanne Gould" <dajomigo at tpg.com.au>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Wednesday, December 07, 2005 6:58 AM
Subject: Re: [AccessD] Concatenating data into lists
> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>