Removals From A List Box

[rookery]

I have a ListBox whose RowSourceType is set to Field List and RowSource to a table. I've got it so that when a user clicks on a field in the listbox a text box is populated with the field name selected. There are 10 text boxes on my form.

This is all very well but I'd like the field name to disappear from the listbox, once it is selected, so as to prevent any duplication. Perhaps I could make a workaround comparing previous TextBox enteries but this is not ideal?

anyone?

 

[jebry]

Hi!

This may not be easier than just looking for duplicates, but here it goes!

Make the list box you have invisible and create another list box which is the one that will be visible, set the row source type of that list box to value list. In the Load event of the form you will use this code to build the value list:

Dim intRow As Integer

For intRow = 0 To Me!InvisibleBox.ListCount - 1
Me!VisibleBox.RowSource = Me!VisibleBox.RowSource & Me!InvisibleBox.Columns(0, intRow) & ";"
Next intRow
Me!VisibleBox.RowSource = Left(Me!VisibleBox.RowSource, Len(Me!VisibleBox.RowSource) - 1)

Then in the click event of the visible box (actually, I prefer to use the doubleclick) Use this code:

Dim intRow As Integer
Dim strField As String
Dim lngPosition As Long

intRow = Me!VisibleBox.ListIndex
strField = Me!VisibleBox.Columns(0, intRow)

If Nz(Len(Me!YourTextBox.Value), 0) <> 0 Then
Me!YourTextBox.Value = Me!YourTextBox.Value & &quot;;&quot; & strField
Else
Me!YourTextBox.Value = strField
End If

lngPosition = InStr(Me!VisibleBox.RowSource, strField)
If lngPosition = 1 Then
Me!VisibleBox.RowSource = Mid(Me!VisibleBox.RowSource, Len(strField) + 1)
Else
Me!VisibleBox.RowSource = Left(Me!VisibleBox.RowSource, lngPosition - 1) & Mid(Me!VisibleBox.RowSource, lngPosition + Len(strField) + 1)
End If

Me.Repaint

As I said, checking for duplicates might be easier.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[FancyPrairie]

I have found that when you need to add/remove items from a listbox, the ActiveX control &quot;Microsoft Forms 2.0 ListBox&quot; is the easiest way to do it. It has the properties and methods to add and remove items. The help file fm20.hlp will explain how to use it (should be on your harddrive c:\windows\system)

 

[rookery]

Thanks guys for your help. I'll let you know how I get on!!

 

[rookery]

Prairie I cant seem to find the ActiveX ListBox. I've searched the References with no success. I do have the Microsoft Froms 2.0 Object Library selected though. Does this control not ship as standard with 2000?

 

[FancyPrairie]

Gee, I don't know. I have the Developer's Edition, but I don't think you need it. But you won't find it in the References. Open a form and, from the menu bar, select INSERT|ACTIVEX CONTROL... You should see it in the list.

 

[rookery]

Prairie I found the ListBox but I was too far down the coding route to turn back and start from scratch.

Basically I used a Collection to solve the problem and made the ListBox.Rowsource equal to the contents of the Collection. It works brilliantly!

Thanks for your help both Prairie and Jeb.