Concatenate Many Side of 1:Many Relationship

[Steve]

Greetings:

I would like to concatenate the "many" side of a 1:Many relationship for
display in a listbox as follows:

Basket 1: apples;oranges;bannanas
Basket 2: staplers; paperclips; stickynotes

The Field Structure of the tables are as follows:
tblBasket:
idsBasketID - Autonumber; key
chrBasketName

tblItems:
idsItemID - Autonumber; key
intBasketID - Integer; Foreign Key
chrItemName

Where tblBasket and tblItems are linked in a 1:many relationship. The
contents of the tables are shown below.

tblBasket
idsBasketID chrBasketName
1 Basket 1
2 Basket 2

tblItems
idsItemID intBasketID chrItemName
1 1 apples
2 1 oranges
3 1 bananas
4 2 staplers
5 2 paperclips
6 2 stickynotes

 

[Steve Sanford]

Steve,

In a standard module, paste in the following function:

'---------------
Public Function Concat(id As Long) As String
Dim d As DAO.Database
Dim r As DAO.Recordset
Dim sSQL As String
Dim tmpStr As String

tmpStr = ""

Set d = CurrentDb

sSQL = "SELECT tblItems.chrItemName"
sSQL = sSQL & " FROM tblItems"
sSQL = sSQL & " WHERE tblItems.intBasketID = " & id
sSQL = sSQL & " ORDER BY tblItems.chrItemName;"

'open recordset
Set r = d.OpenRecordset(sSQL)

' check for records
If Not r.BOF And Not r.EOF Then
r.MoveFirst
'loop thru recordset
Do While Not r.EOF
tmpStr = tmpStr & r.Fields(0) & "; "
r.MoveNext
Loop
tmpStr = Left$(tmpStr, Len(tmpStr) - 2)
End If

'return value
Concat = tmpStr

'clean up
r.Close
Set r = Nothing
Set d = Nothing
End Function
'---------------

In the listbox, set

ROW SOURCE = SELECT tblBasket.idsBasketID, [chrBasketName] & ": " &
Concat([idsBasketID]) AS chrItem FROM tblBasket ORDER BY [chrBasketName] & ":
" & Concat([idsBasketID]);

BOUND FIELD = 1
COLUMN COUNT = 2
COLUMN WIDTHS = 0



Save, then open the form


HTH

 

[Steve Sanford]

Oops, it should have been:

ROW SOURCE = SELECT tblBasket.idsBasketID, [chrBasketName] & ": " &
Concat([idsBasketID]) AS chrItems FROM tblBasket ORDER BY
tblBasket.chrBasketName;



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve,

In a standard module, paste in the following function:

'---------------
Public Function Concat(id As Long) As String
Dim d As DAO.Database
Dim r As DAO.Recordset
Dim sSQL As String
Dim tmpStr As String

tmpStr = ""

Set d = CurrentDb

sSQL = "SELECT tblItems.chrItemName"
sSQL = sSQL & " FROM tblItems"
sSQL = sSQL & " WHERE tblItems.intBasketID = " & id
sSQL = sSQL & " ORDER BY tblItems.chrItemName;"

'open recordset
Set r = d.OpenRecordset(sSQL)

' check for records
If Not r.BOF And Not r.EOF Then
r.MoveFirst
'loop thru recordset
Do While Not r.EOF
tmpStr = tmpStr & r.Fields(0) & "; "
r.MoveNext
Loop
tmpStr = Left$(tmpStr, Len(tmpStr) - 2)
End If

'return value
Concat = tmpStr

'clean up
r.Close
Set r = Nothing
Set d = Nothing
End Function
'---------------

In the listbox, set

ROW SOURCE = SELECT tblBasket.idsBasketID, [chrBasketName] & ": " &
Concat([idsBasketID]) AS chrItem FROM tblBasket ORDER BY [chrBasketName] & ":
" & Concat([idsBasketID]);

BOUND FIELD = 1
COLUMN COUNT = 2
COLUMN WIDTHS = 0



Save, then open the form


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Greetings:

I would like to concatenate the "many" side of a 1:Many relationship for
display in a listbox as follows:

Basket 1: apples;oranges;bannanas
Basket 2: staplers; paperclips; stickynotes

The Field Structure of the tables are as follows:
tblBasket:
idsBasketID - Autonumber; key
chrBasketName

tblItems:
idsItemID - Autonumber; key
intBasketID - Integer; Foreign Key
chrItemName

Where tblBasket and tblItems are linked in a 1:many relationship. The
contents of the tables are shown below.

tblBasket
idsBasketID chrBasketName
1 Basket 1
2 Basket 2

tblItems
idsItemID intBasketID chrItemName
1 1 apples
2 1 oranges
3 1 bananas
4 2 staplers
5 2 paperclips
6 2 stickynotes