Combine multiple records to one text string 1

[robmkimmons]

I have Table [tblColors] which has a listing of colors available to items. Some items have more than one color to them. I want to be able to combine more than one color to a particular item in the [tblProducts] table into ONE TEXT STRING on a bound form.

tblProducts
Item# 453


tblColors
Item# Color
453 Red
453 Blue
453 Slvr

So on my form it would look something like:

Item#453
These together in one text box >>>{Red, Blue, Slvr}

MUCH THANKS!


~Rob

If we expect the unexpected, does that make the unexpected... well, expected?

 

[Blorf]

Hi, I have an option that works using VBA.

If you make an unbound text box on your form, and put =Combine_Colors([Item]) in it, and use put the following into a module, it seems to work.

Function Combine_Colors(Itm As String)
XrX = "Select Item, Color from Colors where Colors.item = '" & Itm & "';"
Set sele1 = CurrentDb.OpenRecordset(XrX)
sele1.MoveFirst

Rtn = ""
X = 0
While Not sele1.EOF
If X = 0 Then
Rtn = "{" + Trim(sele1.Color)
Else
Rtn = Rtn + ", " & Trim(sele1.Color)
End If
X = 1
sele1.MoveNext
Wend
Rtn = Rtn + "}"
Combine_Colors = Rtn
End Function

In my example, the table with colors is just called colors, and I use Item instead of PartId, but you get the idea.

Hope this works for you.

ChaZ

Ascii dumb question, get a dumb Ansi