Data in List box to text 3

terre · Oct 21, 2005

Can anyone help.....?

On a form, I have a list box, BestWith

It has a list of foods compatible with wines (mmmmm!!)

The RowSource for the text box is

SELECT [tblFood].[WineID], [tblFood].[FoodID], [tblFood].[Food] FROM tblFood WHERE ((([tblFood].[WineID])=[Forms]![frmWines]![WineID]));

[tblFood].[Food] is the visible field

I would like the values (regardless of how many) to be recorded in a text box, txtFoods on the same form

ie ListBox

Steak
Cheese
Lamb

txtbox

Steak, Cheese, Lamb

Any suggestions greatfully accepted

HarleyQuinn · Oct 21, 2005

How about something like:

Code:

Private Sub cmdPopTxt_Click()
Dim i As Integer
Dim strFoods As String
For i = 0 To BestWith.ListCount - 1
strFoods = strFoods & BestWith.ItemData(i) & ","
Next i
txtbox = Left(strFoods,Len(strFoods)-1)
End Sub

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

BoxHead · Oct 21, 2005

I may be wrong, but I think you'll have to use the Selected and Column properties to grab the third column of the SQL on only selected records in the listbox.

Code:

Private Sub BestWith_Click()
Dim intI As Integer, mySlx As String
mySlx = ""
For intI = 0 To BestWith.ListCount
mySlx = mySlx & IIf(BestWith.Selected(intI), ", " & BestWith.Column(2, intI), "")
Next intI
If Len(mySlx) >= 2 Then mySlx = Mid(mySlx, 2)
Text56 = mySlx
End Sub

As you Select and Deselect items in your list box, the string in Text56 (replace with your textbox name) should update accordingly.

HTH

John

Use what you have,
Learn what you can,
Create what you need.

HarleyQuinn · Oct 21, 2005

If the bound column is the visible one ([tblFood].[Food]) then my code gets you all entries in the listbox comma delimited. If it's not the bound column then you can use the .Column property (as shown in BoxHead's code) to display the value you want.
If you only want selected rows then BoxHead's code works fine as long as your ListBox allows multiple selection, else it will not display anything in the textbox.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

terre · Oct 21, 2005

Thaks guys....great solutions both.

Your suggestion works fine for me HarleyQuinn

Your help has been much appreciated.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Data in List box to text 3

terre

Technical User

HarleyQuinn

Programmer

BoxHead

Technical User

HarleyQuinn

Programmer

terre

Technical User

Similar threads

Part and Inventory Search

Sponsor