Select an item from a multi column combo box programmatically

Question

Hi all,

 

This thing has buzzed me for more than two hours. I am of a VB6 background.

I try to select a value from a multi column combo box programmatically.

When you do that with VB6 you just set the listindex property to the desired

row (minus 1). The fact is that in access you can't do that because this

property is read only. I googled on that and I found that you can set the

combo box

to Me.Combo = Me.combo.column(1, DesiredIndex). This does the trick but

I believe it just sets the text property of the combo box and doesn't select

the row.

How can I select the entire row so that I can use the

Column(0,Combo.listindex) value?

I would highly appreciate some help on this...

 

Thanks in advance,

/\/ikos

 

 

Answer 1

Hi,

try this link http://msdn.microsoft.com/en-us/library/bb240110(office.12).aspx

Quote:

You can use the Selected property to select items in a combo box by using Visual Basic. For example, the following expression selects the fifth item in the list:

Visual Basic for Applications
Me!Combobox.Selected(4) = True

---

Andrey V Artemyev | Saint-Petersburg, Russia

Answer 2

Hi Andrey,

 

Thanks for the fast response. I tried your solution and the selected value is not displayed in the

combo box neither the AfterUpdate event of the combo box is triggered. Any other ideas?

I am completely helpless with this. I tried everything and still can't get it to work.

 

Thanks again,

/\/ikos

"Andrey Artemyev" wrote in message news:bea05f1f-c299-4d3c-b5ad-bf9a48749818...

Hi,

try this link http://msdn.microsoft.com/en-us/library/bb240110(office.12).aspx

Quote:

You can use the Selected property to select items in a combo box by using Visual Basic. For example, the following expression selects the fifth item in the list:

Visual Basic for Applications
Me!Combobox.Selected(4) = True

---

Andrey V Artemyev | Saint-Petersburg, Russia

Answer 3

Nikolaos,

I've found the solution in the built-in help.

To set the ListIndex property value, you can use the following:

Forms(formname).Controls(controlname).SetFocus
Forms(formname).Controls(controlname).ListIndex = index

And have just tried this code:

Private Sub Command17_Click()
 Me.Combo15.SetFocus
 Me.Combo15.ListIndex = Me.Text0.Value
End Sub

It works well and changes my combo value.

---

Andrey V Artemyev | Saint-Petersburg, Russia

Answer 4

"Nikolaos H" wrote in message

news:[e-mail]...

> Hi all,

>

> This thing has buzzed me for more than two hours. I am of a VB6

> background.

> I try to select a value from a multi column combo box programmatically.

> When you do that with VB6 you just set the listindex property to the

> desired

> row (minus 1). The fact is that in access you can't do that because this

> property is read only. I googled on that and I found that you can set the

> combo box

> to Me.Combo = Me.combo.column(1, DesiredIndex). This does the trick but

> I believe it just sets the text property of the combo box and doesn't

> select

> the row.

> How can I select the entire row so that I can use the

> Column(0,Combo.listindex) value?

> I would highly appreciate some help on this...

>

> Thanks in advance,

> /\/ikos

 

The help file is misleading.  If the combo box has the focus, you can set

its .ListIndex property.  However, the simplest way -- which doesn't require

it to have the focus -- is to set its .Value property.  However, you need to

set it to the value of the combo's bound column for that row.  For that, the

.ItemData property is very handy.  For example, to select the first item in

the combo box, you could write:

    With Me.cboMyCombo

       .Value = .ItemData(0)

   End With

 

The number of columns in the combo box is irrelevant to all this.

 

Note that setting the value of the combo box programmatically doesn't fire

its BeforeUpdate or AfterUpdate events.  If you need to, you can call them

directly.  For example:

    With Me.cboMyCombo

       .Value = .ItemData(0)

   End With

   Call cboMyCombo_AfterUpdate

 

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 5

Thanks a lot Dirk. I tried this as Andrey also said and I made one step forward and one step back.

I overcame the obstacle of improper use of listindex but I get the error -

 

Run time error '2115' : The macro or function set to the BeforeUpdate or ValidationRule Property

for this field is preventing (name of DB) from saving data in this field.

 

The fact is that there is not a macro or function for the BeforeUpdate event and definitely there is

not a validation rule in the combo box. I googled a lot on this message but almost everywhere in the

on-line forums the users have a BeforeUpdate event which I don't

 

Thanks a lot Andrey and Dirk.

 

P.S. If you think you know the answer of what's causing this error don't feel embarrassed to share

it with me...  Cheers!! 

Answer 6

Thanks a lot Dirk. I tried this as Andrey also said and I made one step forward and one step back.

I overcame the obstacle of improper use of listindex but I get the error -

 

Run time error '2115' : The macro or function set to the BeforeUpdate or ValidationRule Property

for this field is preventing (name of DB) from saving data in this field.

 

The fact is that there is not a macro or function for the BeforeUpdate event and definitely there is

not a validation rule in the combo box. I googled a lot on this message but almost everywhere in the

on-line forums the users have a BeforeUpdate event which I don't

 

Thanks a lot Andrey and Dirk.

 

P.S. If you think you know the answer of what's causing this error don't feel embarrassed to share

it with me...  Cheers!! 

I've tried a couple of times to reply, but got hung up.  So here goes again ...

What event are you using to run your code?  What does the code look like?  Assuming the combo box is bound, is there a validation rule on the field to which it is bound?  In the context of this thread, I'd assume that the value you're assigning to the combo is in the combo's list, but have you checked to make sure?

Oh, and is this a multiselect combo box?  That would make a difference.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 7

There is a table with the users of the application with the fields : Name (Text), Password (Text) , Job (Long Number)

There are two combo boxes - cmbUser which I populate with the Name field (which is the username) and FirstName

and LastName (three columns) and combo47 which is a value list with the RowSource "9;Administrator;1;JobA;2;JobB"

and the bound column is 1.

 

When I select a value from cmbUser this event is triggered:

 

--------------------------------------------------------------------------------

Private Sub cmbUser_AfterUpdate()
Dim sCode As String
Dim rsSel As Recordset

 

sCode = Me!cmbUser
Set rsSel = CurrentDb.OpenRecordset("SELECT * FROM tblUsers where Name = '" & sCode & "'")

 

Select Case rsSel("Job")
    Case 9
        Me.Combo47.SetFocus
        Me.Combo47.ListIndex = 0
    Case 1
        Me.Combo47.SetFocus
        Me.Combo47.ListIndex = 1
    Case 2
        Me.Combo47.SetFocus
        Me.Combo47.ListIndex = 2
    Case Null
        Me.Combo47.SetFocus
        Me.Combo47.ListIndex = -1
End Select

 

End Sub
--------------------------------------------------------------------------------

 

If rsSel("Job") is not null I get the "Run time error '2115' ". Any Ideas?

It is not a multi select combobox.

 

Cheers

/\/

 

"Dirk Goldgar" wrote in message news:fcfd0455-6ce6-4fa4-a291-4c99dcd72170...

I've tried a couple of times to reply, but got hung up.  So here goes again ...

What event are you using to run your code?  What does the code look like?  Assuming the combo box is bound, is there a validation rule on the field to which it is bound?  In the context of this thread, I'd assume that the value you're assigning to the combo is in the combo's list, but have you checked to make sure?

Oh, and is this a multiselect combo box?  That would make a difference.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 8

There is a table with the users of the application with the fields : Name (Text), Password (Text) , Job (Long Number)

There are two combo boxes - cmbUser which I populate with the Name field (which is the username) and FirstName

and LastName (three columns) and combo47 which is a value list with the RowSource "9;Administrator;1;JobA;2;JobB"

and the bound column is 1.

 

When I select a value from cmbUser this event is triggered:

 

--------------------------------------------------------------------------------

Private Sub cmbUser_AfterUpdate()
Dim sCode As String
Dim rsSel As Recordset

 

sCode = Me!cmbUser
Set rsSel = CurrentDb.OpenRecordset("SELECT * FROM tblUsers where Name = '" & sCode & "'")

 

Select Case rsSel("Job")
    Case 9
        Me.Combo47.SetFocus
        Me.Combo47.ListIndex = 0
    Case 1
        Me.Combo47.SetFocus
        Me.Combo47.ListIndex = 1
    Case 2
        Me.Combo47.SetFocus
        Me.Combo47.ListIndex = 2
    Case Null
        Me.Combo47.SetFocus
        Me.Combo47.ListIndex = -1
End Select

 

End Sub
--------------------------------------------------------------------------------

 

If rsSel("Job") is not null I get the "Run time error '2115' ". Any Ideas?

It is not a multi select combobox.

 

Combo47 has a Column Count of 2, and Bound Column is 1?

Is this a bound form, or unbound?  If so, is cmbUser a bound control, and if so, what is its controlsource?  Is Combo47 a bound control, and if so, what is its recordsource?

The logic seems unnecessarily complicated, since you could (for these values at least), just write:

    Me.Combo47 = rsSel("Job")

and be done with it.

Note: "Case Null" will never be satisfied, because Null is not equal to anything.  You probably should just use Case Else for the possibility that rsSel("Job") is Null.

Another point, which isn't relevant to the issue, is that it would be better to explicitly close rsSel before exiting the procedure.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 9

Combo47 is a 2 columns combo box and the bound column is number 1.

It is a value list with the values:

 

9-Administrator

1-JobA

2-JobB

 

When I look at it in design view I see the word "Unbound" written in it so I suppose it is unbound.

 

 

 

As long as it has to do with the form the form's "record source" property is nothing so I suppose it

is not bound and its "recordset type" property is dynaset.

 

 

 

When I look at cmbUser in design view I see the word "Unbound" written in it so I suppose it is unbound.

Its "control source" property is nothing and its "row source" property is:

 

"SELECT tblUsers.name, tblUsers.Firstname, tblUsers.LastName, tblUsers.Job FROM tblUsers;"

 

"row source type" = Table/Query , "Bound Column" = 1

"Dirk Goldgar" wrote in message news:9951fb73-98e8-495c-9821-e3b69b17a149...

 

Combo47 has a Column Count of 2, and Bound Column is 1?

Is this a bound form, or unbound?  If so, is cmbUser a bound control, and if so, what is its controlsource?  Is Combo47 a bound control, and if so, what is its recordsource?

The logic seems unnecessarily complicated, since you could (for these values at least), just write:

    Me.Combo47 = rsSel("Job")

and be done with it.

Note: "Case Null" will never be satisfied, because Null is not equal to anything.  You probably should just use Case Else for the possibility that rsSel("Job") is Null.

Another point, which isn't relevant to the issue, is that it would be better to explicitly close rsSel before exiting the procedure.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 10

"Nikolaos H" wrote in message

news:[e-mail]...

> Combo47 is a 2 columns combo box and the bound column is number 1.

> It is a value list with the values:

>

> 9-Administrator

> 1-JobA

> 2-JobB

>

> When I look at it in design view I see the word "Unbound" written in it so

> I suppose it is unbound.

>

> As long as it has to do with the form the form's "record source" property

> is nothing so I suppose it

> is not bound and its "recordset type" property is dynaset.

>

> When I look at cmbUser in design view I see the word "Unbound" written in

> it so I suppose it is unbound.

> Its "control source" property is nothing and its "row source" property is:

>

> "SELECT tblUsers.name, tblUsers.Firstname, tblUsers.LastName, tblUsers.Job

> FROM tblUsers;"

>

> "row source type" = Table/Query , "Bound Column" = 1

 

Hmm, then I don't see exactly what's causing your problem.  But are you

still manipulating the combo box by setting its ListIndex property?  That

can be quirky.  Change your code to replace all of this:

 

'------ code to be replaced ------

   Select Case rsSel("Job")

       Case 9

           Me.Combo47.SetFocus

           Me.Combo47.ListIndex = 0

       Case 1

           Me.Combo47.SetFocus

           Me.Combo47.ListIndex = 1

       Case 2

           Me.Combo47.SetFocus

           Me.Combo47.ListIndex = 2

       Case Null

           Me.Combo47.SetFocus

           Me.Combo47.ListIndex = -1

   End Select

'------ end of code to be replaced ------

 

... with this:

 

'------ replacement code ------

   Me.Combo47 = rsSel("Job")

'------ end of replacement code ------

 

Please post back the result.

 

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 11

Dirk,

 

thanks a lot my friend. Your advice worked like a charm...

 

Cheers,

/\/

"Dirk Goldgar" wrote in message news:120dad85-cce9-4da3-9fa6-0d37cffa4919...

"Nikolaos H" wrote in message

 

... with this:

 

'------ replacement code ------

   Me.Combo47 = rsSel("Job")

'------ end of replacement code ------

 

Please post back the result.

 

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 12

I struggled with this for hours.  And the .ListIndex didn't work with or without .SetFocus.  I don't think it's even usable as a method.  

But my problem was the values would change depending on a value selected in another box, so I couldn't use value or default value.  

The only thing that worked was using:

With cmbHeight

.SetFocus

.Value = .ItemData(0)

End With

Thanks Dirk.

All this time for something that should be as simple as [combobox].SetIndex = 0

Great job of over complicating things Microsoft, not to mention.

Answer 13

I struggled with this for hours.  And the .ListIndex didn't work with or without .SetFocus.  I don't think it's even usable as a method.  

But my problem was the values would change depending on a value selected in another box, so I couldn't use value or default value.  

The only thing that worked was using:

With cmbHeight

.SetFocus

.Value = .ItemData(0)

End With

Thanks Dirk.

All this time for something that should be as simple as [combobox].SetIndex = 0

Great job of over complicating things Microsoft, not to mention.

Not to mention what?

You don't need the .SetFocus there, since all you are manipulating is the combo box's .Value property, and that is available regardless of whether the combo box has the focus.  Try this:

With cmbHeight
    .Value = .ItemData(0)
End With

If you really want a one-line solution, then:

    cmbHeight.Value = cmbHeight.ItemData(0)

But that forces VBA to evaluate the combo box reference twice, so is less efficient.

Either way, seems pretty simple to me.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html