List Records in a Single Field

[Eddie Mason]

I have a table with a field ‘Options’ that contains several records. What I want to do is to 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

I have been supplied with the following function, which I have tried but cannot get to work:

Public Function FieldLine(FName As String, TName As String, Separator As String) As String
Dim Mydb As Database
Dim Rst As Recordset
Set Mydb = CurrentDb
Set Rst = 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 If
End Function

Can anyone see why this is not working?

Kind regards,

Eddie

 

[pbaldy]

It would be helpful to know what "not working" means exactly, but offhand you don't have an

rst.MoveNext

 

[pr2-eugin]

Hello Eddie, so is the UNBOUND text box, has its control source as,

= FieldLine("Options", "[COLOR=Blue]yourTableName[/COLOR]", " ")

Where yourTableName being the name of the table you are trying to use? and the following?

Public Function FieldLine(FName As String, TName As String, Separator As String) As String
    Dim Mydb As Database
    Dim Rst As Recordset
    Set Mydb = CurrentDb
    Set Rst = Mydb.OpenRecordset("SELECT " & FName & " FROM " & TName & " WHERE FName is Not Null")
    FieldLine = ""
    While Not Rst.EOF
        FieldLine = FieldLine & Rst.Fields(0) & Separator
        [B]Rst.MoveNext[/B]
    Wend
    If FieldLine <> "" Then
        FieldLine = Left(FieldLine, Len(FieldLine) - Len(Separator)) 'to remove last separator
    End If
End Function

 

[Eddie Mason]

I've cut and pasted the module. Created a form with an unbound test with:
= FieldLine("Options", "tblDBTest", " ") as its control source but when I try to open the form it gives me the following message:

Run-time error '3061'
Too few parameters. Expected 1.

Does this make any sense.

Kind regards

Eddie

 

[pr2-eugin]

Oops, Sorry, the code should have been..

Public Function FieldLine(FName As String, TName As String, Separator As String) As String
    Dim Mydb As Database
    Dim Rst As Recordset
    Set Mydb = CurrentDb
    [COLOR=Red]Set Rst = Mydb.OpenRecordset("SELECT " & FName & " FROM " & TName & " WHERE " & FName & " Is Not Null")[/COLOR]
    FieldLine = ""
    While Not Rst.EOF
        FieldLine = FieldLine & Rst.Fields(0) & Separator
        Rst.MoveNext
    Wend
    If FieldLine <> "" Then
        FieldLine = Left(FieldLine, Len(FieldLine) - Len(Separator)) 'to remove last separator
    End If
End Function

 

[pbaldy]

You have to concatenate the FName variable in the WHERE clause.

 

[Eddie Mason]

Hi Paul, you’re a gem, I have been at this all day and you’ve got it to work for me. Many Many thanks

Eddie

Before :banghead: after: