text from table

Please provide an example; sample data would be better.

Are you talking about concatenating the values from multiple records in your [TextModelNumbers] column, into a single textbox on the form?

That is correct as in:

The values come from the query ModelsQuery

ModelNumbers
A1
B2
C3
D4

to go a field (TextModelNumbers) on the MainForm as
A1, B2, C3, D4

With a limit of 200 Characters

Is this the only field in the query?  What does the query look like?  Generally, the best way to do this is to use some form of concatenation function.  I use something that looks like:

Public Function fnConcat(FieldName as String, TableName as string, _
                                Optional Criteria as Variant = NULL, _
                                Optional Separator as string = ", ", _
                                Optional WrapWith as string = "") as string

    Dim strSQL as String
    Dim rs as DAO.Recordsource

    strSQL = "SELECT [" & FieldName & "] as ConcatField " _
                & "FROM [" & TableName & "] " _
                & ("WHERE " + Criteria)  'this second line will eval to NULL if Criteria is NULL
    
    'The next line will remove extra [ and ] characters
    strSQL = Replace(Replace, strSQL, "[[", "["), "]]", "]"))

    set rs = currentdb.openrecordset strSQL, , dbfailonerror
    While not rs.eof

        fnConcat = fnConcat & Separator & WrapWith & rs!ConCatField & WrapWith

        rs.MoveNext

    Wend

    'Strip the leading separator from the return value
    fnConcat = Mid(fnConcat, Len(Separator) + 1)

    rs.close
    set rs = nothing

Exit Function

Select allOpen in new window

You would call this something like:

= fnConcat("ModelNumbers", "ModelsQuery")

HTH, Dale

Hi Dale

I get error (expected end of statement) with:

    set rs = currentdb.openrecordset strSQL, , dbfailonerror

And presumably I need:

If Len(funconcat)>199 then
       msgbox "Some text"
end if

sorry, need to wrap that in ()

    set rs = currentdb.openrecordset(strSQL, , dbfailonerror)

Yes, I would change:

While not rs.eof

    fnConcat = fnConcat & Separator & WrapWith & rs!ConCatField & WrapWith

    rs.MoveNext

Wend

Select allOpen in new window

to:

Do while not rs.eof

        fnConcat = fnConcat & Separator & WrapWith & rs!ConCatField & WrapWith

        if Len(fnConcat) - Len(Separator) >200 then Exit Do

        rs.MoveNext

Loop

Select allOpen in new window

This could have the effect of returning > 200 characters if your ModelNumber field is long.  For example, if Len(fnConcat)-Len(Separator) = 199 then it will allow you to add another ModelNumber to the string and will then exit.  If you really want to keep it to under 200 characters, you would need to check the length after removing the leading separator character(s).  If it is still over 200, then you would need to search backwards for the last occurrence of the separator:

if Len(fnConcat) > 200 then
    fnConcat = Left(fnConcat, instrrev(fnConcat, separator) - 1)
endif

Wow!

Last record would be fine for about 200 characters.

Should Dim rs as DAO.Recordsource be DAO.Recordset as I get error

and I get augment ("Replace" highlighted on second placement) in statement not optional with strSQL = Replace(Replace, strSQL, "[[", "[", "]]", "]")

yes, recordset, not recordsource.

The problems of typing on an iPad.

strSQL = Replace(Replace(strSQL, "[[", "[", "]]", "]")

I am still getting augment not optional on

strSQL = Replace(Replace(strSQL, "[[", "[", "]]", "]"))

Have I put the last bracket in the right place and should this statement say what to replace these brackets with?

Or am I missing some library references? I have DAO 3.6 and Activex Data Objects 2.5

Public Function fnConcat(DoorNumber As String, Sets As String, _
                                Optional Criteria As Variant = Null, _
                                Optional Separator As String = ", ", _
                                Optional WrapWith As String = "") As String

    Dim strSQL As String
    Dim rs As DAO.Recordset

    strSQL = "SELECT [" & DoorNumber & "] as ConcatField " & "FROM [" & Sets & "] " & ("WHERE  (((Sets.ItemNumber)=[Forms]![ProjectForm]![Details].[Form]![ItemNumber]))" + Criteria)  'this second line will eval to NULL if Criteria is NULL
    
    'The next line will remove extra [ and ] characters
    'strSQL = Replace(Replace(strSQL, "[[", "[", "]]", "]"))

    Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
Do While Not rs.EOF

        fnConcat = fnConcat & Separator & WrapWith & rs!ConCatField & WrapWith

        'If Len(fnConcat) - Len(Separator) > 200 Then Exit Do

        rs.MoveNext

Loop

    'Strip the leading separator from the return value
    fnConcat = Mid(fnConcat, Len(Separator) + 1)

    rs.Close
    Set rs = Nothing

End Function

Select allOpen in new window

If I disable the two lines above it works but I get all values from table sets so it seems to be ignoring the where clause. Is the where clause wrong or am I calling the function in the wrong way with:
Note = fnConcat("DoorNumber", "Sets")

Derek

It can get confusing, and it looks like I dropped a right paren ).

The Replace syntax is:

Replace(ValueToSearch, Character(s) to replace, Replace with what), you need to add a ) between the "[" and the "]]"

strSQL = Replace(Replace(strSQL, "[[", "["), "]]", "]"))

You could also have done this as:

strSQL = Replace(strSQL, "[[", "[")
strSQL = Replace(strSQL, "]]", "]")

That's great.

I take it that you did not get the edited version of the question.
It works but I get all the values from the table instead of those with the where clause. Can you revisit the comment above when it has refreshed. It's just the records I am getting now.

Fabulous!

With thanks Derek

sorry it took so long to get that working.