MultiSelect or Single Select List Box

[LadyDi]

I have inherited a database that is used to track equipment that comes in for repair. One of the fields in this database is a memo field that lists the parts that are replaced on a piece of equipment. It has been requested that I leave this field as it is. However, I would like to regulate the data that is entered into to some extent. I need to ensure that there is a comma between every part number listed (there is another group that wants data from this database and they want to be able to separate this field out into individual fields). Is there a way to have Access look at the data in a memo field, find all the periods or ampersands, and replace them with commas?

The other thought I had was to provide the person filling in the database with a multi-select list box that lists all the available part numbers for a given piece of equipment. That person could select as many as she wants and when she was done, the database would take those values, put a comma between each one, and paste them in the memo field. Is that possible? What I have tried isn't working. I tried to get it to loop through the selected items and put them in the memo field, but I just end up with the last value selected instead of all of the items selected.

I don't know what to try next, I feel like it is impossible to make everyone happy. Either the person entering the data is going to be upset because she has to type every part number in a different field, or the people receiving the data are going to be upset because there isn't a comma between each number. Any suggestions you have would be greatly appreciated.

 

[RuralGuy]

Everything you want to accomplish can be done. Have you looked at the Replace() function yet?

 

[missinglinq]

Allan's suggestion should take care of parsing out the existing Records, separating each part with a comma; it will take a separate run, using Replace(), for each possible delimiter that has been used in the past.

As to preventing the problem, in moving forward

...Either the person entering the data is going to be upset because she has to type every part number in a different field...

In point of fact, the correct way of doing this would actually require there to be a separate 'parts installed' Table, with the data entry person entering a New Record for each part that is installed! But compromises have to be made, sometimes, especially when serving multiple masters, and depending on the number of parts we're talking about, here, this may be a necessary workaround. At any rate, here's an example of looping thru a Multi-select Listbox and assigning the selections, separated by a comma, to a Textbox.

Private Sub MyListBox_AfterUpdate()

Dim strNames As String

Dim varItem As Variant

If Nz(Me.MyMemoField, "") <> "" Then
  Me.MyMemoField = Me.MyMemoField & ", "
  strNames = Me.MyMemoField
End If 

For Each varItem In Me.MyListBox.ItemsSelected
  strNames = strNames & Me.MyListBox.ItemData(varItem) & ", "
Next varItem

Me.MyMemoField = Left(strNames, Len(strNames) - 1)

End Sub

Linq ;0)>

 

[LadyDi]

That worked great. Thank you so much.

 

[RuralGuy]

Thanks for jumping in Linq. I had to go to a meeting and could not get back.

 

[missinglinq]

Glad Allan and I could help!

Linq ;0)>