Listing data from linked table with commas

[SSBSystemsClerk]

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?

 

[John Vinson]

I just want to list it in a text box,
separated by commas.

The reason your approach is slow and visually noisy is that you're
getting the data from a Form. Well, the data isn't stored in a Form -
it's stored in a Table!

Instead, use VBA code to go directly to the Table for the information.
There's sample code to do so at

http://www.mvps.org/access/modules/mdl0004.htm


John W. Vinson[MVP]

 

[SSBSystemsClerk]

Thank you for your help. Yes, I figured getting the data through a form was a
major problem; I just couldn't seem to get it to work any other way.

The sample code you directed me to seems like it ought to work, but I am
having trouble implementing it successfully. For one thing, the database
doesn't seem to like the "Dim db As Database" line in the code. I've
bypassed that so I don't get stalled with an error message (I deactivated
that line and the line that sets the db value to CurrentDb, and then replaced
db later on with CurrentDb), but of course now I wonder if that's one reason
it won't work for me. Or perhaps I'm doing the wrong thing in SQL? This is
how I have my query set up: SELECT PCs.PCasset, PCs.Compname,
fConcatChild("License","pcAsset","swID","String",[pcAsset]) AS SubFormValues
FROM PCs;

Can you please tell me what I'm doing wrong and how I can fix it?

 

[John Vinson]

Thank you for your help. Yes, I figured getting the data through a form was a
major problem; I just couldn't seem to get it to work any other way.

The sample code you directed me to seems like it ought to work, but I am
having trouble implementing it successfully. For one thing, the database
doesn't seem to like the "Dim db As Database" line in the code.

The code assumes you have the DAO object library active; AccessXP
defaulted to the ADOX library instead.

Open the VBA editor, select Tools... References, and scroll down to
the line with "Microsoft DAO x.xx Object Library", largest version
number, and check it. You may want to also uncheck the default ActiveX
Data Objects reference - both ADO and DAO have "Recordset" objects but
they are DIFFERENT recordset objects. If you leave both checked,
change the code to Dim all Recordset objects as DAO.Recordset (and
Querydefs as DAO.Querydef, probably others).

I've
bypassed that so I don't get stalled with an error message (I deactivated
that line and the line that sets the db value to CurrentDb, and then replaced
db later on with CurrentDb), but of course now I wonder if that's one reason
it won't work for me. Or perhaps I'm doing the wrong thing in SQL? This is
how I have my query set up: SELECT PCs.PCasset, PCs.Compname,
fConcatChild("License","pcAsset","swID","String",[pcAsset]) AS SubFormValues
FROM PCs;

Can you please tell me what I'm doing wrong and how I can fix it?

Almost certainly just the missing reference.

John W. Vinson[MVP]

 

[SSBSystemsClerk]

That did it! Thank you again for your help! Now I can get this project
finished!

Thank you for your help. Yes, I figured getting the data through a form was a
major problem; I just couldn't seem to get it to work any other way.

The sample code you directed me to seems like it ought to work, but I am
having trouble implementing it successfully. For one thing, the database
doesn't seem to like the "Dim db As Database" line in the code.

The code assumes you have the DAO object library active; AccessXP
defaulted to the ADOX library instead.

Open the VBA editor, select Tools... References, and scroll down to
the line with "Microsoft DAO x.xx Object Library", largest version
number, and check it. You may want to also uncheck the default ActiveX
Data Objects reference - both ADO and DAO have "Recordset" objects but
they are DIFFERENT recordset objects. If you leave both checked,
change the code to Dim all Recordset objects as DAO.Recordset (and
Querydefs as DAO.Querydef, probably others).

I've
bypassed that so I don't get stalled with an error message (I deactivated
that line and the line that sets the db value to CurrentDb, and then replaced
db later on with CurrentDb), but of course now I wonder if that's one reason
it won't work for me. Or perhaps I'm doing the wrong thing in SQL? This is
how I have my query set up: SELECT PCs.PCasset, PCs.Compname,
fConcatChild("License","pcAsset","swID","String",[pcAsset]) AS SubFormValues
FROM PCs;

Can you please tell me what I'm doing wrong and how I can fix it?

Almost certainly just the missing reference.

John W. Vinson[MVP]