Put datas of table into a string

[Johnny Wong]

I would like to create a funtion that it can copy a table's data into a array or string. Then, I can use the string to do other thing. Assume the table contains only one column of data.

Thanks for any suggestion.

 

[yhgtbfk]

I would like to create a funtion that it can copy a table's data into a array or string. Then, I can use the string to do other thing. Assume the table contains only one column of data.

Thanks for any suggestion.

Yes, but how would you like that string to be displayed? And what would you like to do with it?

I did heavily edit this from some code I had, so sorry if there are any mistakes

DivString = Null 'The string that holds the text
Do While 1 = 1
'If the DivString is not empty, we check for strings NOT in the divstring
If IsNull(DivString) = False Then
GetText = DLookup("text", "table", "text not in (" & DivString & ")"
'If the DivString is empty, we check for the first string
Else
GetText = DLookup("text", "table")
End If
'If we cant find any(more) we exit
If IsNull(GetText) = True Then
Exit Do
End If
'Add the text to the string
DivString = DivString & " " & GetText
Loop

 

[Johnny Wong]

yhgtbfk,
Thanks for your help!
I have created my code and share below.
But I want to improve it to make it more general to use.

Public Function TableToString(tblName As String, columnName As String, output As String)

I could not use rst![columnName] or rst!columnName if I pass columnName as a variable.

Is there any suggestion?

Thanks!

Johnny:>

########################################
Public Function TableToString(tblName As String, output As String)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim StrMessage As String
Dim tbl As TableDef
Dim RecordCount As Integer
Dim avaData As Variant
Dim Count As Integer

'initial
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(tblName)
RecordCount = rst.RecordCount
Count = 0

'copy all data from the single column table into string
If RecordCount > 0 Then
Do While Count < RecordCount
StrMessage = StrMessage & ", '" & rst!columname & "'"
rst.MoveNext
Count = Count + 1
Loop
'Cut the first "," character
output = Right(StrMessage, Len(StrMessage) - 1)
Else
'Return a message box if the table is empty
MsgBox "The table is empty"
End If

End Function
########################################

 

[Johnny Wong]

I just find the solution from the other thread.

It is simple.
Change "rst!columnname" to "rst(columnvariable)"

Thank you so much!

Johnny:>