none
List box with FieldNames of a table RRS feed

  • Question

  • Thanks for taking the time to read my question

    I have 2 list boxes. List box 1 should contain a listing of field names of a selected table, the second should be the fields that the user has selected. When they select a field it appears in list box 2 and disappears from list box 1. If I set my listbox rows ource to a table and the row source type to FieldList I don't see how this is possible.

    Is there another way to do this besides running VBA to populate a table with the current list of field names? Is it not possible to query some of the system tables in Access or something?

    Thanks,
    Brad

    Thursday, October 29, 2015 8:30 PM

Answers

  • Sorry, this code works...

    Private Sub cmdAddTolstSelected_Click()
    Dim PickValList As String
    
    PickValList = Me.lstPick.RowSource
    
        If Me.lstSelected.RowSource = "" Then
            Me.lstSelected.RowSource = """" & Me.lstPick.Value & """"
        Else
            Me.lstSelected.RowSource = Me.lstSelected.RowSource & "," & """" & Me.lstPick.Value & """"
        End If
        PickValList = Replace(PickValList, """" & Me.lstPick.Value & """" & ",", "", 1, 1, vbTextCompare)
        Me.lstPick.RowSource = PickValList
        Me.lstPick.Requery
        Me.lstPick = Me.lstPick.ItemData(0)
        Me.lstSelected.Selected(Me.lstSelected.ListCount - 1) = True
    End Sub

    Brad

    Thursday, October 29, 2015 9:16 PM

All replies

  • Hi Brad. Like you said, the only way I know how is to "manually" do it yourself using code rather than using the built-in "Field List" option. Just my 2 cents...
    Thursday, October 29, 2015 8:53 PM
  • Since you want field names to disappear from the first list box when they are added to the second one, the row source of both cannot be a query on a system table or something similar.

    One option is to create a table tblFieldNames with two fields: FieldName (Text) and Selected (Yes/No). Populate the FieldName list with the list of field names of a table.

    The RowSource of the first list box would be

    SELECT FieldName FROM tblFieldNames WHERE Selected = False ORDER BY FieldName

    and that for the second list box

    SELECT FieldName FROM tblFieldNames WHERE Selected = True ORDER BY FieldName

    Requery the list boxes using VBA when the selection has changed.

    Another option would be to manage the content of the list boxes using the AddItem and RemoveItem methods in VBA.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 29, 2015 8:53 PM
  • Thanks both for your replies. I appreciate it.

    I decided to edit the RowSource of each ListBox and set the RowSourceType to ValueList

    I have an 'Add' button between the two to move the selected value.. here is my code:

    Private Sub cmdAddTolstSelected_Click()
    Dim PickValList As String
    
    PickValList = Me.lstPick.RowSource
    
        If Me.lstSelected.RowSource = "" Then
            Me.lstSelected.RowSource = """" & Me.lstPick.Value & """"
        Else
            Me.lstSelected.RowSource = Me.lstSelected.RowSource & "," & """" & Me.lstPick.Value & """"
        End If
        PickValList = Replace(PickValList, """" & Me.lstPick.Value & """" & ",", "", 1, 1, vbTextCompare)
        Me.lstPick.RowSource = PickValList
        Me.lstPick.Selected(0) = True
        Me.lstSelected.Selected(Me.lstSelected.ListCount - 1) = True
    End Sub

    Works well.

    It also selected the new first row on the pick side and the newly added value on the selected side

    Would you change anything?

    Brad

    Thursday, October 29, 2015 9:11 PM
  • Sorry, this code works...

    Private Sub cmdAddTolstSelected_Click()
    Dim PickValList As String
    
    PickValList = Me.lstPick.RowSource
    
        If Me.lstSelected.RowSource = "" Then
            Me.lstSelected.RowSource = """" & Me.lstPick.Value & """"
        Else
            Me.lstSelected.RowSource = Me.lstSelected.RowSource & "," & """" & Me.lstPick.Value & """"
        End If
        PickValList = Replace(PickValList, """" & Me.lstPick.Value & """" & ",", "", 1, 1, vbTextCompare)
        Me.lstPick.RowSource = PickValList
        Me.lstPick.Requery
        Me.lstPick = Me.lstPick.ItemData(0)
        Me.lstSelected.Selected(Me.lstSelected.ListCount - 1) = True
    End Sub

    Brad

    Thursday, October 29, 2015 9:16 PM