Help with Listing Records in a Single Field (1 Viewer)

[Eddie Mason]

Little more help:

I was given the following function which works perfectly: -

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
Rst.MoveNext
Wend
If FieldLine <> "" Then
FieldLine = Left(FieldLine, Len(FieldLine) - Len(Separator)) 'to remove last separator
End If
End Function

What I’m trying to do is use query as a filter to control the fieldline. When I manually enter the criteria to the query the function works perfectly, however when I use a form to create the queries criteria the function returns an error.

If I run the filter query whilst the form is open it produces the correct list of options so it must be seeing the form.

Can anyone help with this.

Kind regards

Eddie

 

[DavidAtWork]

Eddie, how are you calling this function and what exactly does it return

David

 

[Eddie Mason]

Hi Dave,

I'm calling it in a query that I want to use as an update query. The end use is that the field line will be included in a CSV export file.

Kind regards,

Eddie

 

[DavidAtWork]

sorry Eddie, what value does this query return for FieldLine

David

 

[Eddie Mason]

In the fieldLine it returns as a string the values from a single column in a table.

e.g. the records in the table are
Black
Blue
Green

The fieldline will return: 'black blue green'.

Kind regards,

Eddie

 

[DavidAtWork]

OK that's what I thought. So what criteria would you like to set and what do expect to see in terms of results

David

 

[Eddie Mason]

The criteria will be the ID of the record selected and I want returned the FieldLine for the related records.

Kind regards,

Eddie

 

[DavidAtWork]

Your function is just returning a long concatenated string of FNames regardless of any ID, maybe you should refine your SELECT statement in the function to include the ID criteria

David