Need to Create an Array

[CathieB]

I have an access 2002 database where I have two tables, a Fund table which lists funds and a Partner Fund table which associates many partners to many funds. I need to create an array that lists the partners once in one field and have another field that holds all the funds that they are in. And if a new fund is created, that fund is added to the one field. Anyone have any ideas?

Thank you

Cathie

 

[Norris68]

I'm not sure why you specifically need an array. When using a database system you would normally use recordsets.

Anyway, to create a string listing all the funds a partner is a member of you would need to use a public function. e.g.

Public Function ListPartnerFunds(PartnerID As Long) As String
Dim S As String
Dim R As ADODB.Recordset
Set R = New ADODB.Recordset
R.Open "SELECT FundID FROM tblPartnerFund WHERE PartnerID=" & PartnerID, CodeProject.Connection, adOpenStatic, adLockReadOnly
While Not R.EOF
    If S <> &quot;&quot; Then S = S & &quot;, &quot; 'Separate funds with commas
    S = S & R(&quot;Fund&quot;).Value
    R.MoveNext
Wend
R.Close
Set R = Nothing
If S <> &quot;&quot; Then S = &quot;(&quot; & S & &quot;)&quot; 'Enclose result in brackets to look like an array ;)
ListPartnerFunds = S
End Function

I assume you have a table for partners, one for funds and another to link the partners to the funds (in my example called 'tblPartnerFund').

You can then use the public function within a query, or from code.

Query example: SELECT PartnerID, ListPartnerFunds([PartnerID]) FROM tblPartner
Code example: S = ListPartnerFunds(1)

Because of the way we formatted the output of the function, you could also it as part of an IN clause e.g. "SELECT ... WHERE FundID IN " & ListPartnerFunds(PartnerID)

Change the SQL statement within the function to select the fund name with an INNER JOIN to tblFund if you want to return the names instead of the IDs.
e.g. R.Open "SELECT FundName FROM tblPartnerFund INNER JOIN tblFund ON tblPartnerFund.FundID = tblFund.FundID WHERE PartnerID=" & PartnerID, CodeProject.Connection, adOpenStatic,

 

[CathieB]

I guess what I really need is a recordset. I'm kind of new to VB, but have been working with Access for a very long time. When I asked another Programmer (4d) for help, I was told what I needed was an array. I am using this for a show all form, where the Partners Name is in one field, and all the funds they are involved in are in another field. I'm not quite sure how to make what you gave me work in this instance. I created the Public Function, but now don't know how to use it. Could you maybe help me a little more....?