Connecting Combo Boxes

[Myriad_Rocker]

I've searched on this and I found something that worked but it didn't put the actual text in the combo box automatically. I have a combo box that looks at an ID...but I want to also have another combo box that will display the name associated with that ID without me having to choose it too. It's a one to one relationship so there aren't multiple records with that particular ID.

Here is the Row Source I was using...but it just populated the drop down...

SELECT tblDepartmentTask.DepartmentID, tblDepartmentTask.Department, tblDepartmentTask.Task, tblDepartmentTask.TaskID FROM tblDepartmentTask WHERE (((tblDepartmentTask.DepartmentID)=Forms!Form1!Combo0));

 

[DES]

I hope I understand what you are doing?

The thought I have on this is that a combo box can contail multiple columns. One combo box can contain both the ID and the name in it. So a second combo may not be necessary to what you are trying to do.

Also though a combo can have multiple columns each column can have their own widths set. As such a combo can have for example two columns - ID and Name but the user may not know the ID for each person. So you could set the column width for the ID to 0 and the user would only see the Name column.

The other property that goes along with this is the Bound Column. In the above example if the bound column is column 1 (ID) then the value of the combo box will equal the ID even though the user will only see names.

Now if this is just too darned confusing for you send me a sample of what you are trying to do and I will see what I can come up with for you and get it back to you with an explaination of what I have done.

 

[Myriad_Rocker]

Yes, I know a combo box can have multiple columns...but only on the drop down. Once the bound value is selected, it is ONLY the ID that shows. They, the users, also want to see the name after they have selected in another combo box. I know this can be done because I have seen it...I just do not have this example to reference.

 

[DES]

Yup your right it will only show the one value. I believe you are looking to next the second combo and that is fine. Basically you make the second combo with both the ID and the Name fields in it. Go into design view for the second combo's Row Source and under the ID field put the first combo as the criteria for the ID. Then go back to the first combo and on the After Update event requery the second combo.

That will give you what you want but it always seemed a little bit of a waste to me to create a second combo that only has one value in it as in this case. To me it would be simpler to throw a text box on the form whose control source does a dlookup and supplies the Name based upon the ID in the combo box or not show them the ID at all but that is up you.

 

[Myriad_Rocker]

I don't show them the ID in the combo box...but when you select the record, the ID is in the combo box because that's the value it needs to write to the table. I would love to not have to put the name on there since it's in the drop down but THE USERS want to see it even after they have selected.

And it's not simply just updating a text box....I also need to see it when viewing the records on the form....

 

[Rich]

Set the number of columns to display to two, set the width of the first to 0cm

 

[Myriad_Rocker]

I guess I'm not explaining things right.

I want one main combo box to drive the values of the others. I don't want to populate lists, I don't want it to do anything but put a value in there where it equals the main combo box.

 

[Myriad_Rocker]

I can get the combo box to update the list but I can't get it to automatically select the first record in the list every time I change records on the form.

 

[Myriad_Rocker]

I figured it out...