-1

I have a MS Access 2010 form with a "Company" combobox and an "address" multivalue combobox.

When a company is selected the AfterUpdate event requeries the address combobox so that only addresses relevant to the company are listed. This works perfectly; however the majority of companies only have one address value, so I want to automatically select the address if there is only one.

I have spent a lot of time trying variuos methods such as:

AddressCombo.Value = AddressCombo.ItemData(0)

which brings up "Runtime error 3032" and the following doesn't appear to do anything.

AddressCombo.Selected(0) = True

I know using a listbox is better but I want to know if this can be done with a multivalue combobox.

For those unaware of multivalue comboboxes (MSA 2007+), here is a video of them in action:

http://www.youtube.com/watch?v=queKMe9MiSs

Symanb
  • 17
  • 1
  • 6
  • What code are you using to fill the Address combo? Note, MS Access does not support multivalue combo boxes. – ron tornambe Jul 14 '14 at 15:55
  • http://office.microsoft.com/en-us/access-help/use-a-list-that-stores-multiple-values-HA010031117.aspx ... NOTE The new lists take their data from another database component called a multivalued lookup field. You must have a multivalued lookup field in one of your database tables before you can create multivalued lists. – Mark C. Jul 14 '14 at 16:00
  • 2
    Please do not use multivalued ields unless you are using Sharepoint. I wonder do you mean listboxes? – Fionnuala Jul 14 '14 at 16:32
  • You can start here http://access.mvps.org/access/lookupfields.htm – Fionnuala Jul 15 '14 at 01:12
  • While I freely admit to being a complete novice this article says that lookup tables can be useful if understood and not misused. http://improvingsoftware.com/2009/10/02/blog-response-lookup-fields-in-access-are-evil/ – Symanb Jul 15 '14 at 08:42

1 Answers1

1

I called a friend who is much smarter than me. He said that in his experience of VB sometimes you cannot access a control's properties until the control has been activated.

He suggested using .Dropdown to "activate" the combobox before attempting to set the options.

Me!AddressCombo.SetFocus
Me!AddressCombo.Dropdown
Me!AddressCombo.Selected(0) = True
Me!AddressCombo.Selected(1) = False
Me!AddressCombo.Selected(2) = True

He was absolutely right and the code enabled me to select the required options.

Symanb
  • 17
  • 1
  • 6