Multiple Values in textbox

[frksdf]

Hi All;
I am very happy to see a quality forum like this. Sorry for my bad English.

I have an issue about the data source of a textbox on a form. My access version is MS ACCESS 2007.

I have a sql statement that results a datasheet with one coloumn and more than one row. I want to view these results in a textbox with (,) or something like that between records.

For example,
sql statement

 
SELECT cars.Car_names FROM cars WHERE cars.age = "21"

This sql query results 10 records. And I want to list 10 car names in a textbox. ( I cannot use them in a listbox because of the structure of form)



Textbox should be like this : Renault, Mercedes, Audi, Porche

It doesn't matter what is seperating sign. (,) (-) or blank may be.

Is it possible a solution on access?
Thank you very much for your kind help and interest.

[Robeen]

If you're willing to get into some code - you could try using the code in this function [don't use the first & last lines - 'Function . . .' & 'End Function'] in the Form Load Event of your Form to get all your values into one string variable and then put the variable into the TextBox.

 
Function Get_DB_Values()
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strField1 As String
Dim i as Integer
On Error GoTo Error_Handle
 
Set db = CurrentDb
 
strSQL = "SELECT cars.Car_names FROM cars WHERE cars.age = "'" & 21 & "'"
 
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
With rs
 
Set i = 1
 
'This Do While loop goes through all the records in strSQL.
Do While Not rs.EOF
 
If i = 1 Then
    strField1 = rs![Car_names] & ","
Else
    strField1 = strField1 & " " & rs![Field1] & ","
End If
 
.MoveNext          'Move to next record in recordset.
i = i + 1
 
Loop               'Back to 'Do While' to check if we are at the end of the file.
 
'You can use this MsgBox to display the contents of strField1 - at the end of the loop.
'Just un-comment the line below.
'MsgBox strField1
 
Exit_Get_DB_Values:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
 
Error_Handle:
    Resume Exit_Get_DB_Values
End With
 
End Function

I haven't tested this - but you can play with it and see if it will work for you.

[frksdf]

Thanks for your help Robeen; but unfortunately I am not very experienced about VBA codes.
Could you please tell me the line that states the Textbox name? I will write my original textbox name.

Is it strfield1 ?

[orange]

What is the purpose of the textbox with 10 values?
Perhaps there are other options to accomplish what you want.

[Robeen]

I didn't put the Text Box name into the code.

What you would do is put the code in where I have that MsgBox statement.
The code would look something like this:
Me.YourTextBoxName.SetFocus
Me.YourTextBoxName.Text = strField1

[frksdf]

What is the purpose of the textbox with 10 values?
Perhaps there are other options to accomplish what you want.

As I mention above, I have a query , results with 1 coloumn and more than one row. My main purpose is listing values in the same line. I cannot use a listbox beacuse of structre of the form. It is a formal form and I cannot amend or change it.

If you have an other idea please make informed.

Summary: I have a query. I need to write the results in the same line. For example, renault, mercedes, bmw, volvo OR 1,2,3,4,5,6,7.

By the way, thank you very much.

[Robeen]

Hi frksdf!

Was the code I gave you of any help to you?
Let us know if we can help more.

I saw an example somewhere on the internet a couple of years ago where someone said it was possible to do what you are wanting in a query.
He used a keyword called 'Concat' [?I think?].
I tried using the example but could never get it to work. As I was on a time-bound project - I wrote code to achieve this.
In my case I was creating a list of customers and their balances for various categories. The final output had to end up in a spreadsheet - and multiple Names/Balances for each category had to be in one cell . . .

I just saw this from Allen Browne. It is conceptually the same as what I suggested - but is probably laid out a lot better. See if you can use it.
http://allenbrowne.com/func-concat.html

My first thought when I read your post was the same one as what Orange said.
It seems almost the opposite of how a database is supposed to be used.
But . . . I just assumed you know what you need - and took it from there!

[Robeen]

P.S. Try using this in Google:
ms access concatenate values from multiple rows
You might find something that you can use.

All the best!!

[frksdf]

Hi Robeen;
I am very happy to see a helpful person. Thanks a lot.

I am exactly mining the Google for one week. Find lots of example codes for this situation. By the way, as you said, "It seems almost the opposite of how a database is supposed to be used." you are right. But it is very hard to convince a damn manager!!!

So, Your code that gave me, I tried but I got some errors. Then, I asked a guy in Turkish forums for a code. He applied this code to my sample database called CARS. ( It is attached ). It is working peoperly. The SQL statement is embeded in the textbox with this:

 
=Concatenate("Select KOD FROM Arabalar Where Yas = 8 ")

But if you change the table name, It doesn't work.

And VBA code this:

 
Option Compare Database
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'
'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)
 
'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

By the way, in the attached file, the table name is "arabalar" which means in Turkish "cars". And the field "yas" means age. ( a quick turkish lesson if you wish )


thanks, Robeen, additional to this I am looking to Allen Browne's page that you advised me. Thanks a lot.

Why this code stops working if we change the name of table ? Do you have an idea ??

[frksdf]

This problem solved.
thank for your all help and interest.
You can use this VBA based function.

 
Function Concatenate(pstrSQL As String, Optional pstrDelim As String = ", ") As String
    Dim rs As New ADODB.Recordset
    rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
        Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
End Function

[sureej19]

Thanks this helped me