Hi,

I have an Access database and I want to populate a memo/text field
with a string of values retrieved from the database based upon a
particular value. So for example I want to populate the field with a
string of the names of other contacts at the same company as the
current contact separated by a comma.

I'm not sure how to go about this? Should I use Visual Basic or a
query or a combination of both.

Any suggestions or sample code would be very helpful.

Thanks


Emmett Power

"Emmett Power" <Emmett (AT) Silico-Research (DOT) com> wrote

You can use a simple bit of code to loop through records and build a string
which you then put into your memo field.

It might look something like:

If Not rsCompany.EOF then rsCompany.MoveFirst 'If records exist move
first
Do Until rsCompany.EOF
strCriteria = "[CompanyID]=" & rsCompany![CompanyID]
varContacts = Null 'Clear each time through
rsContacts.FindFirst strCriteria
Do Until rsContacts.NoMatch
if Len(Contacts) > 0 then Contacts = Contacts & ", " 'Only add
comma if text exists
varContacts = Contacts & rsContacts![ContactName]
rsContacts.FindNext strCriteria
Loop
With rsCompany
.Edit
![ContactList] = varContacts
.Update
.MoveNext
End With
Loop

--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/reponse

Thanks Bradley I'll give it a go.

Regards

Emmett

"Bradley" <bradley (AT) REMOVETHIScomcen (DOT) com.au> wrote

Hi,

This is a rather slow solution. Do you know how this can be done directly
with SQL? I almost thought i had the solution by using GROUP BY and
EXPRESSION where a VBA function is called and populates the strings through
a STATIC string variable and STATIC long recordid. But unfortunatly it
didn't work well.

Any help would be gratefull
Thanks!

"Bradley" <bradley (AT) REMOVETHIScomcen (DOT) com.au> schreef in bericht
news:Gsrcb.121872$bo1.75337 (AT) news-server (DOT) bigpond.net.au...

Hi,

There are different ways to do this. One solution is based on VBA code that
loops through records (This is a performance killer, because for every
record you will have to loop through another recordset). Look at
http://www.mvps.org/access/modules/mdl0004.htm

I still don't know if it can be done with pure SQL, but i got it working
without VBA code which loops through records. (Not to mention the time lost
for opening and closing the recordset.)

The trick is using two queries and a bit of VBA code. It works faster than
looping through records in VBA.

1. The base query GROUPS some Recordkey and GROUPS the Textfield you want to
populate. In a 3th column a calculated textfield as EXPRESSION calls a
public function fnPopulateTextfield which takes to params (Recordkey and
Textfield).

RecID
GROUP BY

Textfield
GROUP BY

PopulatedText: fnPopulateTextfield([RecID];[Textfield])
EXPRESSION

The second column is needed, because without it the query does not
recognizes that Textfield is part of a statistic function! The side effect
is that it will group on every Textfield. We dont want that shit! Dont worry
the final query will correct this!

2. The finishing query based on the above query GROUPS again on the
Recordkey and takes the LAST record of the calculated populated textfield.

RecID
GROUP BY

PopulatedText
LAST

3. The function fnPopulateTextfield uses two STATIC vars (long and string).
every time it sees a different recordkey it reinitializes the STATIC vars
(Long var gets new recordkey and the String var gets empty). Next it adds
the Textfield to the STATIC string var seperated by comma and then returns
the STATIC string var.

Lets say you have a table like the next one :

RecID SomeTextfieldYouWantToPopulate
1 Cat
1 Dog
2 Fish
3 Lion
3 Birth
3 Snake
4 Dog
4 Crock
4 Hyena
4 Fish

After the base query you will get:

RecID PopulatedTextfield
1 Cat
1 Cat, Dog
2 Fish
3 Lion
3 Lion, Birth
3 Lion, Birth, Snake
4 Dog
4 Dog, Crock
4 Dog, Crock, Hyena
4 Dog, Crock, Hyena, Fish

What the f*#%! Don't worry after the final query you will get:

1 Cat, Dog
2 Fish
3 Lion, Birth, Snake
4 Dog, Crock, Hyena, Fish

Voila!

My own code was too complex to show the basic principle, sorry!

Good luck and if you believe hard enough it will happen!

Sidney.

"Emmett Power" <Emmett (AT) Silico-Research (DOT) com> schreef in bericht
news:6e11de5c.0309230808.4819f4c2 (AT) posting (DOT) google.com...