insert all column(x) values in a single textbox

[riccifs]

Hi to everyone,
does anyone to know how can I insert in an unbounded texbox, separated
from a ";" ALL the values coming from the column(x) of a combobox?
On my form I have a combobox with its row source to a multicolumn
query and it's bounded with an ID field.
What I have to be able to do is that:
to insert for each row, with same ID, of the combo the value with
respect to its (x) column.

Hi hope to have been clearly...
Many thanks, Stefano.

 

[Morris]

Hi to everyone,
does anyone to know how can I insert in an unbounded texbox, separated
from a ";" ALL the values coming from the column(x) of a combobox?
On my form I have a combobox with its row source to a multicolumn
query and it's bounded with an ID field.
What I have to be able to do is that:
to insert for each row, with same ID, of the combo the value with
respect to its (x) column.

Hi hope to have been clearly...
Many thanks, Stefano.

Let's clarify this a little bit.
Your combo box holds X lines of data, each line has Y columns.

After one of the lines has been selected you want those Y columns to
be copied to a textbox, where each value is separated with semicolon,
right?

How about this:

Private Sub Command4_Click()
Dim rstData As ADODB.Recordset
Dim strBuffer As String
Dim i As Integer
Dim varField As Variant
Dim strSQL As String


strSQL = "SELECT Countries.CountryName, Companies.CompanyName,
Companies.AddressLine1, Companies.AddressLine2,
Companies.AddressLine3, Companies.ID "
strSQL = strSQL & "FROM Countries INNER JOIN Companies ON Countries.ID
= Companies.CountryID "
strSQL = strSQL & "WHERE (Companies.ID = " & Me.Combo0 & ")"

Set rstData = New ADODB.Recordset
rstData.Open strSQL, CurrentProject.Connection, adOpenStatic

If Not rstData.BOF Then
rstData.MoveFirst
If Not rstData.RecordCount = 1 Then
MsgBox "More than one record returned, aborting"
Exit Sub
Else
For i = 0 To rstData.Fields.Count - 1
If i <> 0 Then
strBuffer = strBuffer & ";" & Nz(rstData.Fields(i),
"NULL")
Else
strBuffer = Nz(rstData.Fields(i), "NULL")
End If
Next i
End If
Else
MsgBox "No data returned by query, aborting"
Exit Sub
End If

Me.Text2 = strBuffer

End Sub

 

[riccifs]

Let's clarify this a little bit.
Your combo box holds X lines of data, each line has Y columns.

After one of the lines has been selected you want those Y columns to
be copied to a textbox, where each value is separated with semicolon,
right?

How about this:

Private Sub Command4_Click()
Dim rstData As ADODB.Recordset
Dim strBuffer As String
Dim i As Integer
Dim varField As Variant
Dim strSQL As String

strSQL = "SELECT Countries.CountryName, Companies.CompanyName,
Companies.AddressLine1, Companies.AddressLine2,
Companies.AddressLine3, Companies.ID "
strSQL = strSQL & "FROM Countries INNER JOIN Companies ON Countries.ID
= Companies.CountryID "
strSQL = strSQL & "WHERE (Companies.ID = " & Me.Combo0 & ")"

Set rstData = New ADODB.Recordset
rstData.Open strSQL, CurrentProject.Connection, adOpenStatic

If Not rstData.BOF Then
rstData.MoveFirst
If Not rstData.RecordCount = 1 Then
MsgBox "More than one record returned, aborting"
Exit Sub
Else
For i = 0 To rstData.Fields.Count - 1
If i <> 0 Then
strBuffer = strBuffer & ";" & Nz(rstData.Fields(i),
"NULL")
Else
strBuffer = Nz(rstData.Fields(i), "NULL")
End If
Next i
End If
Else
MsgBox "No data returned by query, aborting"
Exit Sub
End If

Me.Text2 = strBuffer

End Sub

Hi Morris,
thanks for answered to me, I think you understood well my question.
I tried your code but it's giving to me an error saying that it's
missing an operator in the string SQL.
What I'm doing wrong?

Hear it is my strSQL:

strSQL = "SELECT tblCave.Cod_cava, tblCave.Nome_cava,
tblComuni.COMUNE, tblCatasto.Nbr_particella"
strSQL = strSQL & "FROM (tblComuni INNER JOIN tblCave ON
tblComuni.IDComune = tblCave.Cod_comune) LEFT JOIN tblCatasto ON
tblCave.Cod_cava = tblCatasto.Cod_cava"
strSQL = strSQL & "GROUP BY tblCave.Cod_cava, tblCave.Nome_cava,
tblComuni.COMUNE, tblCatasto.Nbr_particella"
strSQL = strSQL & "WHERE (tblCave.Cod_cava = " & Me.Cod_cava & ")"


Many thanks for your help in any case.
Bye, Stefano.