restrict concatenated result

[BradCollins]

With many thanks to the other members here I have found the code (below) that will allow me to take a particular field in my table and concatenate the results into one single line.
My problem now is how can I change this code so that it will only show the records for one user.

My table structure is :
tblAllowedCompanies
fldCompID
fldCompName

tblUsers
fldUserName
fldSecID

Each user may be allocated one or more companies, at the moment the code below returns

1,2,3,4,5,6 - all company numbers listed

I would like to see 1,2,3,6 - only those company numbers allocated to the chosen user.

Private Function UpdateList()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strResult As String

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblAllowedCompanies", dbOpenDynaset)
    
    rs.MoveFirst
    
    Do
        strResult = strResult & rs("fldCompID") & ", "
        rs.MoveNext
    Loop Until rs.EOF
    
    UpdateList = Mid$(strResult, 1, Len(strResult) - 2)
    
    rs.Close
    db.Close
    
End Function

Thansk again

 

[PHV]

Use a query instead of tblAllowedCompanies

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BradCollins]

Of course, now that makes sense.

however now that I have changed the line of code to read :

Set rs = db.OpenRecordset("qryAllowedCompanies", dbOpenDynaset)

I get an #Error in the results field, should this entire line read differently now

Thanks again for your expert advice.

 

[BradCollins]

Nevermind, I have worked it out.

For anyone interested the resulting code is :

    Dim qd As DAO.QueryDef
    Dim db As DAO.Database
    Dim prm As DAO.Parameter
    Dim rs As DAO.Recordset
    Dim strResult As String

    Set db = CurrentDb
    Set qd = db.QueryDefs("qryAllowedCompanies")
    For Each prm In qd.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    Set rs = qd.OpenRecordset()
    
    rs.MoveFirst
    
    Do
        strResult = strResult & rs("fldCompID") & ", "
        rs.MoveNext
    Loop Until rs.EOF
    
    UpdateList = Mid$(strResult, 1, Len(strResult) - 2)
    
    rs.Close
    db.Close