cascading combos: changing which field is displayed once selected

[dragginthru]

I have set up a form which has 2 combos.

Combo A pulls from a query which gets surgery address1, address2, and surgeryID. It displays only the first 2 fields.

Combo B gets info from a query getting staff firstname, surname, title, staffID and surgeryID, and shows the first 3 fields.

I have the following code in the afterupdate of box A (surgery_combo)

Private Sub surgery_combo_AfterUpdate()
staff_combo = 0
staff_combo.Requery
End Sub



HERE IS THE PROBLEM:

The queries seem to work fine, as box B displays the staff that work at each surgery. However on selecting a staff member, you can only SELECT the ones who have a salutation (mr/mrs etc) in the first column. And when you DO select this - the combo shows a most unhelpful "Mr" whereas I want the name there.

How do I change the column that the combo displays once selected?

 

[RuralGuy]

Hi dragginthru,
As you have already discovered, a combobox displays the 1st visible column when it is closed. Why not modify your underlying query and create a new field called FullName that concatenates the Title & " " & FirstName & " " & SurName and make it the 1st visible column in place of the title that is there now.

 

[dragginthru]

Cool idea thanks

I understand what you mean there but dont quite know how to put it in and into what box though!

I have the query in design view and have created the column.

I tried:

="salutation" & " " & "first_name" & " " & "last_name"
in most boxes and didnt work!

 

[RuralGuy]

FullName: [salutation] & " " & [first_name] & " " & [last_name]

 

[dragginthru]

OK the syntax is working now, but...

ive done that but now a new problem presents itself:

Once you have selected a value in the first combo box (the GP surgery), a parameter box comes up asking for get_surgery_staff_details!Salutation

 

[RuralGuy]

Make that the first column but set the column width to 0";

 

[dragginthru]

How do I change the width of an individual field in the combo box? I havent done that yet, I set it up in the wizard...

Ive attached a screenshot of what is happening

 

[dragginthru]

OK the first error was that I had accidentally deleted the SALUTATION field in the query.

However I still dont know how to make that part of the query show in the box...

 

[RuralGuy]

On the Format tab of the property sheet for the ComboBox there is a Column Widths property. It is in inches usually and can look like 0";1";1";1";1". That parameter request probably has something to do with other code in the ComboBox events and you may have to put the original fields back in but just set the column width to 0".

 

[dragginthru]

OK ive got the two boxes working perfectly, with the concatenated results being the only thing displayed in them, great thanks

 

[RuralGuy]

Glad to help.