Hello tjay,
First, welcome to this group as a first time poster. Here is a generic
example for the sample Northwind.mdb database. Try it out first in Northwind,
and then see if you can get it to work in your database.
Create a new module. Add Option Explicit as the second line of code to your
module, if it is not already there. Here is the reason why you want this, and
instructions on how to configure your copy of Access to always include this
in all new modules:
Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions
Click on Tools > References. You need to verify that you have a reference
set to the "Microsoft DAO 3.6 Object Library" (or version 3.51 if you are
using Access 97). If you do not see this reference selected, then scroll down
the list until you find it, and place a check to select it. Then click on OK
to back out of the references dialog.
Select the following code and copy it (Ctrl C). Paste it into your new
module, just below the line that reads Option Explicit:
Function ConcatenateRecords(lngCategoryID As Long) As String
On Error GoTo ProcError
' Input: Primary key of record in Categories table
'Returns: String containing all the related product names.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strOut As String
Dim lngLen As Long
Const conSEP = "; "
Set db = CurrentDb
strSQL = "SELECT ProductName " _
& "FROM Products " _
& "WHERE CategoryID =" & lngCategoryID
Set rs = db.OpenRecordset(strSQL)
With rs
Do While Not (.BOF Or .EOF) = True
strOut = strOut & ![ProductName] & conSEP
.MoveNext
Loop
End With
lngLen = Len(strOut) - Len(conSEP)
If lngLen > 0 Then
ConcatenateRecords = Left$(strOut, lngLen)
Else
ConcatenateRecords = ""
End If
ExitProc:
On Error Resume Next
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, _
"Error in ConcatenateRecords function..."
Resume ExitProc
End Function
Click on Debug > Compile ProjectName. Hopefully, your code will compile
without any errors. VBA code is compiled when an attempt to repeat this
operation reveals that the option is grayed out (ie. unavailable).
Save your new module as "basConcatentateRecords" (without the quotes).
Create a new query. Dismiss the Add Tables dialog without adding any tables.
In query design view, click on View > SQL View. You should see the word
SELECT highlighted. Copy the following SQL statement (Ctrl C) and paste it
into the SQL view (Ctrl V), replacing the SELECT keyword:
SELECT Categories.CategoryName,
ConcatenateRecords([CategoryID]) AS ProductName
FROM Categories
ORDER BY Categories.CategoryName;
You can then switch back to the more familiar design view, if you wish, by
clicking on View > Design View. Run the query.
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
