Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a problem with a MSForms combobox which I have created on a userform. The form contains details of an object, and the combobox is populated with a list from a database, and has 2 columns - the first contains the ID (this is the BoundColumn, and hidden when the user views it), the second the description, as follows :

    ID Description
    -- -------------
    6 Gas Asia
    3 Global Oil Americas
    2 Global Oil EH
    1 Global Oil Europe
    5 GTEL
    4 NAGP
    7 Not Known

    I create a new record, and save it with an ID of 1 (Global Oil Europe).
    The problem I have is that when I want to load the saved object record in the form, I cannot set the ComboBox to show the correct value. I cannot use the Value setting, because the ID column is hidden, and I cannot use ListIndex either as setting it to 1 gives me Global Oil Americas.

    There must be a way round this, but I cannot work out what it is.

    Any help gratefully received

    Bodders

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use code like this:

    Code:
      Dim intValue As Integer
      Dim i As Integer
      intValue = 1 ' the ID of the record to select
      For i = 0 To Me.ComboBox1.ListCount - 1
    	If Me.ComboBox1.List(i) = intValue Then
    	  Me.ComboBox1.ListIndex = i
    	  Exit For
    	End If
      Next i
    where ComboBox1 is the name of the combo box.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='781881' date='26-Jun-2009 13:12']You could use code like this:

    Code:
      Dim intValue As Integer
      Dim i As Integer
      intValue = 1 ' the ID of the record to select
      For i = 0 To Me.ComboBox1.ListCount - 1
    	If Me.ComboBox1.List(i) = intValue Then
    	  Me.ComboBox1.ListIndex = i
    	  Exit For
    	End If
      Next i
    where ComboBox1 is the name of the combo box.[/quote]

    Hans

    Thanks very much for that. I was hoping there was just a property like Itemdata in an Access form to set, but it looks like I will have to use this loop.

    Bodders

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You have to set the ListIndex to the appropriate value.
    You could declare a variable at the top of the userform module:

    Private arrIndex()

    Populate it in the UserForm_Initialize event, after populating the combo box:

    Code:
      Dim i As Integer
      Dim n As Integer
      n = Me.ComboBox1.ListCount
      ReDim arrIndex(1 To n)
      For i = 0 To n - 1
    	arrIndex(Me.ComboBox1.List(i)) = i
      Next i
    You can then use a single line to select the item with ID = 1:

    Me.ComboBox1.ListIndex = arrIndex(1)

    So you still need a loop, but you don't have to repeat it each time you want to set the value of the combo box.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •