Joining records

[Eddie Mason]

I have a table with a field ‘Options’ that contains several records. What I want to do is list the content of ‘Options’ into a single field separated by space.

Eg: Options
Small
Medium
Large

To appear in a field as: Small Medium Large

Options
Small
Medium
Large
Massive

To appear in a field as: Small Medium Large Massive

Is there any way that I can do this?

Hope someone can help.

Kind regards

Eddie

 

[CJ_London]

Hi,

try this function which will return the contents of any field from any table using any separator.

In your case

FormField=FieldLine("Options","tableName"," ")

replacing the bits in red with your actual names.

When to call it is up to you - probably in the Form_Current event

Public Function FieldLine (FName as string, TName as string, Separator as string) as string
Dim Mydb as CurrentDB
Dim Rst as Recordset

Set Mydb=CurrentDB
Set Res=Mydb.OpenRecordset("SELECT " & FName & " FROM " & TName & "WHERE FName is Not Null")
FieldLine=""
While not rst.eof
FieldLine=FieldLine & Rst.fields(0)& Separator
Wend
if FieldLine<>"" Then FieldLine=left(FieldLine,Len(FieldLine)-Len(Separator)) 'to remove last separator
End Function

I would be inclinded to put this function in a module so that it can be called from anywhere

 

[Eddie Mason]

Hi CJ

Your advice has indeed been very useful and I thank you very much not only for giving it but also your prompt reply.

kind regards,

Eddie

P.S. where is the thanks button

 

[pr2-eugin]

Its here..

 

[Eddie Mason]

Hi CJ,

I have copied and pasted your function which I named as basListRecords but when I call it, it just returns an error. I have changed the:
Set Res=Mydb.OpenRecordset("SELECT " & FName & " FROM " & TName & "WHERE FName is Not Null")
To
Set Rst=Mydb.OpenRecordset("SELECT " & FName & " FROM " & TName & "WHERE FName is Not Null")
Is there anything else that I should do?

Kind regards,

Eddie

 

[CJ_London]

Ah Sorry, missed a space - should be space before the WHERE

("SELECT " & FName & " FROM " & TName & " WHERE FName is Not Null")