I have a fairly small computer inventory database.  It was originally created
in Access XP, but I am currently using Access 2003. It includes the tables
PC, License, and Software.  PC and Software have a many-to-many relationship,
joined by the table License.  In a report, I want to be able to list all the
software for each PC, but I don't want to have to list it in columns because
that takes up too much space.  I just want to list it in a text box,
separated by commas.  I was hoping this would be as easy as using one of the
existing functions.  For instance, I have no trouble creating a field in a
query that counts the total number of different software each computer has,
or that gives the first software name on the list.  But I want one that will
say, for example, "Symantec Antivirus, Microsoft Office 2003, Internet
Explorer" etc., all in one field.  I need this list to appear in a report,
but I would like it to appear in the query on which the report is based.

Now I have found a way to make this work directly in the report, but it is
obviously very inefficient.  A screen pops up showing a form, and you see
Access scrolling through the records at lightning speed, but it still takes
well over a minute to populate the textbox in the report.  Then every time I
go to another record, it has to look up the data for that textbox all over
again.  If I try to go directly to the last record, it looks them all up
again.  I haven't even tried to print the report yet.

This is the code I am currently using:

Public Function soft(pcID As String)
On Error GoTo Err_sfunc

   Dim stDocName As String
   Dim stLinkCriteria As String
   
   stDocName = "GetSoftware"
   stLinkCriteria = "[PC]=" & pcID
   
'The form GetSoftware has the data I need from the License table.  I have
' not succeeded in finding a way to retrieve the data directly from the
table or
' a query    
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria
   
   Dim sw As String
   Dim swAll As String
   Dim counter As Integer
     
   counter = 0
   sw = "x"

Do Until sw = ""
   Forms!GetSoftware!swID.SetFocus
   sw = Forms!GetSoftware!swID.Text
       If counter = 0 Then
           swAll = sw
       ElseIf sw <> "" Then
           swAll = swAll & ", " & sw
       Else
           GoTo Copydata
       End If
   counter = counter + 1

If sw <> "" Then DoCmd.GoToRecord
 
Loop

Copydata:
DoCmd.Close acForm, "GetSoftware", acSaveNo

soft = swAll

Exit_sfunc:
   Exit Function

Err_sfunc:
   MsgBox Err.Description
   Resume Exit_sfunc
End Function

In the Report, I call the function using this code:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
softwaretxt = soft("'" & [pcAsset] & "'")

There's more complicated details of how I have it set up and running, but
the point is, I'm sure there must be a better way.  My experience with VBA
and SQL is pretty limited.  Can anybody help me?