ComboBox info linked to other comboBox 2

[SidLancing]

I have a form with a combobox that displays info from a query. It's a list of Companies from the Company table and and their various locations from the Locations table. I have the row source as CompanyLocations which is a query.
When a user makes a selection(i.e. Company and location) I'd like to capture that data and use it in another combobox in the same form; this next combobox is a contact list. I would like it to be the list of contact for the Company and location previously selected not a list of all compamies and their locations.
Can anyone help?...

P.S. How do I save the selected value of the combobox in a field of the form where it's located.

 

[PHV]

Are you sure the BoundColumn of ShipingLocationID returns LocationID (and not LocationName as I guess) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SidLancing]

ShipingLocationId does return the name of a location but the field name is ShipingLocationID... I inherited this DB...I know that the field names tend to be confusing...

It's a text field just like ReceivingCompanyID.

P.S...I was trying to figure this out while waiting and found that sometimes when I make a selection for shiping location a "Enter parameter" box comes up and if I enter a locationID (i.e a number like 1)then the Last Name shows up in the Recipient field. ... when I check the table the value that appears is the correct one for locationID 1...
I don't know if that helps... but I thought I'd mention it

 

[SidLancing]

PHV or ScriverB

If you're still available today ... can you please take a look again at what I had posted... I've been trying to figure this out all last night.... Can you explain what the relationship of the boundcolumn should be...I don't understand why you ask that question...I tried to compare the type of fields and produce a new combobox based on the previous example you helped me get done ... but we've been going back and forth and honestly I'm not clear on certain things..such as should there be a bound column and why? should I have the select statement in the rowsource the same as in the event procedure. I tried to look this up but found no example created and explained. So if you can, please send me more info.

thanks

 

[scriverb]

SidLancing: Yes, I am here for a short while. I have been watching the posting by PHV and yourself as you guys seem to have gotten the two combox to work. I am a little confused at this point without seeing the form because the combobox names and field names seem out of sync. This is confusing. But, let me try to explain the combobox thing.

First, the RowSource of a combobox is either a table, SQL, or a saved query in most cases. There also a value list or field list but we won't deal with them here.

The rows returned from the RowSource can have many columns. The column that you designate that will be the value of the combobox upon the user making a pick is referred to as the Bound Column. The column count tells the combo how many columns are in the RowSource recordset. And, the column widths tell the combo how to display the columns.

Now if you have three columns ID, Name, Address being returned from the RowSource and we want the user only to see and pick according to Name then we set the column widths to 0;3;0. This will show only the name in alpha order if that is designated in the RowSource query. Now to use the values we can refer to the combobox name itself. The value of the combobox in this example is dependent upon the Bound Column setting. If we choose to select column 2 as the Bound Column then the value after the pick will be the name that was selected. But, that is not a very good choice if we are wanting to use a definitive value to make a comparison in another combobox query due to duplication possibilities. Normally you pick a column that is unqique. So, if we pick column 1 then the ID value which is unique to each name will be the value of the combobox and if the combobox is a Bound Column to an underlying table/query then the record will be updated with this ID value.

Now in your second combobox the rowsource was making a comparison of a field in a table back to the value of this form control(combobox1). Comparing a fields [ID] = FORMS![currentformname]![combobox1] should select the correct records. Conversely if we don't set the Bound Column to 1 but rather set it to 2 in the first combobox then this comparison would compare the [ID] field to the Name field that was choosen. Thus now records to be found.

One last issue. References to a combobox can be made to all of the columns in the combobox no matter what Bound Column is selected. This is done by use of the .column(x) property. If you have 3 columns ID, Name, Address you may through code refer to them as FORMS![formname]![combobox1].column(0)...column(1).. ..and .column(2) respectively. The numeration of the column property starts at 0 through N-1 number of columns.

I hope this posting has helped you understand a little better some of things PHV and I were working with when we were asking questions concerning Bound Columns and RowSource recordsets.

Post back if you have any further questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SidLancing]

Thanks for the explanation... This is now very clear. I've since then fixed my problem and got all my combo boxes to correctly display what they should. I needed to fully understand how the Bound column relates to the combobox and now that I do... I'm able to fix my forms.

Thanks again for all your help and Thanks also to PHV...
Your patience and explanations were greatly appreciated.
I will now move on to other sections of this project.