Populate field from unbound listbox with VB code 2

[kramerica]

(Access 97) How do you use VB to select data in a listbox, on a form and send it to a field?

For example: unbound listbox on form is called grocerylst.

I want to be able to click on a button and have all of the dataitems in the unbound listbox added to a field on the form separated by commas. Is this possible?

 

[LonnieJohnson]

Your request was interesting. I put together the following routine. I worked just fine form me. Replace the approptiate field names and run it. Hope this helps.

Private Sub cmdButton_Click()
Dim intCnt As Integer
Dim strText As String

For intCnt = 0 To (MyListBox.ListCount - 1)

If intCnt <> (MyListBox.ListCount - 1) Then
strText = strText & MyListBox.ItemData(intCnt) & &quot;, &quot;

Else
strText = strText & MyListBox.ItemData(intCnt)
End If

Next

MyTextBox = strText

End Sub

This will iterate thru the list box's items and concantenate each one to a string variable with a comma. Once it gets to the last one, it concantenates the value without a comma at the end.

B-) ljprodev@yahoo.com
ProDev
MS Access Applications

 

[Bammer]

Thanks Lonnie. Me like.

 

[JoeMiller]

You can use this code to go through the list box and get your items, and place them in MyResultTextBox separated by commas:

[tt]
Dim ItemSelected
Dim Result As String

For Each ItemSelected In MyListBoxName.ItemsSelected
Result = Result & MyListBoxName.ItemData(ItemSelected) & &quot;,&quot;
Next ItemSelected

MyResultTextBox = Left(Result, Len(Result) - 2)
[/tt]

HTH
Joe Miller
joe.miller@flotech.net

 

[JoeMiller]

Change the -2 to -1 in my example, sorry bout that! Joe Miller
joe.miller@flotech.net

 

[slaman]

Joe Miller,

Interesting approach. I'm trying something similar and maybe you can help provide a slight modification of what you previously posted.

I have an ID input. From a different table, I want to display a field from a record that matches that ID. It is unbounded.

For example, lets say I have a table called: &quot;Stuff&quot; This table has several fields, one being autonumbered, one called &quot;Referring ID&quot;, one called &quot;Description&quot;.

Now an ID is selected from the Main Table (this is referential with &quot;Referring ID&quot; on the secondary table). How do I go through each record in the Secondary Table that has this &quot;Referring ID&quot;?

[Field on Main Form] = DLookup(&quot;[Description]&quot;, &quot;Secondary Table&quot;, &quot;[Referring ID] = Forms![Main Table]![ID]&quot

The above code would be if there was a one-to-one relationship. How would I link them all up using commas?

 

[JoeMiller]

DLookup won't work in a routine like this because it will always return the first match. When you have more than one ReferringID that will match (as in your example) you need another criteria to determine if the ID being returned is the same as the last ID returned. Dlookup is not a very efficient function either and if the recordset were large this would be a very slow process. I'm gonna have to think about this one, because it's an interesting problem.

Joe Miller
joe.miller@flotech.net

 

[slaman]

Thanks... It's just that I need to generate a &quot;summary&quot; sheet of ALL the information, but I need to make it fit on one page. Since it is unbounded for certain fields (like that in a secondary table) I can't think of an efficient way to display it besides putting them side by side using VBA coding. I look forward to hearing your ideas.

 

[JoeMiller]

Slaman:

I found a way to do what you're looking for, it's not mine but it works splendidly!

http://www.mvps.org/access/modules/mdl0008.htm

Joe Miller
joe.miller@flotech.net

 

[JerryDennison]

You realize, of course, that storing multiple values in a field separated by commas violates the First Normal Form? &quot;...all attributes must be atomic...&quot;