making a combo box display a value from its list via code?

[ZaphodBBB]

Hi

I have a form frmAdd_Equipmen t. It has a number of combo boxes on it, cboModel, cboSupplier, cboManufacturer , cboEquipment_Ty pe etc.

Bcause therer is no existing data to go off I put an ADD NEW cmdButton next to each of the ComboBoxes so if the item does not exist then the user can add a new Model, Supplier etc.

The various ComboBoxes all load from Queries when the Form Loads.

What I'd like to do, is to make it so that should the user click on cboModel, code behind the After Update event would somehow shuffle the data around in the other comboboxes..lik e Supplier, Manufacturer etc to make those ComboBoxes show the relevant data to the Model they originally selected with the cboModel. Then all the user would have to do is select the Department enter in the new Serial Number and Equipment number and click Save instead of having to trawl through and update each and every combo.

I wrote an strSQL string that creates a recordset based upon the Model selected and that works fine, but I cannot get the Columns of the combo boxes to change or update with values from the Recordset. (I guess because they have already been preloaded from the the initial queries during the Form Load event.

I guess I could place a text box next to each combobox, that would load if the user selected an already existing Model, but it would make an already busy form look really cluttered.

Thanks for any assistance
Mike

[nico5038]

What you are looking for is called "cascading combo boxes" and we have an article about that at http://bytes.com/topic/access/insigh...mbo-list-boxes

Basically when having manufacturers related to models, the relation should also be in your database and adding a model should also include adding a (link to) the manufacturer.

Nic;o)

[ZaphodBBB]

Thanks for the article Nico I'll have careful read of it. I have used before where you have one combo define the values that will appear in another, however in this case the database started with empty tables so to begin with everything needed to be entered manually. However there is a little data there now so it would be nice not to have to keep entering all and every item.

Thanks Mike

[nico5038]

Hi Mike,

The "alternativ e" I often use for "plain" reference fields is to define the combo box with a SELECT DISTINCT <field> from tblX.
Thus e.g. a city can be selected easily when entered once.
This will however to be limited to "plain" fields, as it's not useful when you also need e.g. an address and/or phone number from a supplier.

Personally I use different forms for manipulation of tables (read "objects").
So in your case I would have a separate form for departments, supplier, etc.
On the frmAdd_Equipmen t I would only re-use the other tables and give an option to "jump" to the frmAddDepartmen t, etc. Using the NotInList property of the combo box.

Nic;o)

[ZaphodBBB]

Thanks Nico

I had never heard of the Not In List property so thats something I have learned today.

I do have seperate forms for the other Fields such as AddManufacturer and Add Supplier etc but I wanted to make it quick and easy to add new equipment from one form , so I put all the comboboxes on the form with Add New cmdButtons next to each combo box that will take you to the relevant form should you need them. But it's beginning to get a little messy so I may end up doing as you suggest.

Cheers
Mike

[ZaphodBBB]

Hi Nico

I just had a quick read of the Cascading Comboboxes article. Do you know if you can load more than one column via code into the second comboBox?

i.e. Say Combobox 1 was the CompanyID and Company Name
could you code the after update event of Combobox 1 so that it updates Combobox 2 with say EmployeeID and Employee Name?

Thanks Mike

[nico5038]

Glad to see you're interested in learning something new :-)

There's no limit to the number of columns. Just make sure that the correct WHERE is implemented.

A complete sample how to use the NotInList to add a new record is available here:
Use NotInList Event to Add a Record to Combo Box

Nic;o)

[ZaphodBBB]

Thanks Nico

Just read the article and it appears it could be very useful. Funny how you sometimes dont notice things, as I'd never even noticed that in the events list for Combos.

Thanks
Mike

[nico5038]

Took me also several months to detect this mechanism and that's the beauty of sites like this, it can save you a lot of time :-)

Success with your application !

Nic;o)