Combo box only allows SOME selections

[davidbodhi]

I have a combo box in a form. The box is linked to a table called Therapy with 3 fields: TherapyID, Therapy_Type and Therapy_Cost.

The table has been populated with 5 records:
1 Physical Therapy $125
2 Occupational Therapy $125
3 Acupuncture $90
4 Swedish Massage $65
5 Raindrop Treatment $65

I have a field on the form that pops up the cost when one of these is selected in the combo box. This seems to be working but for some reason, and it's blowing my mind, the combo box refuses to let me select Occupational Therapy or Raindrop Treatment.

I have other pick lists of other things working perfectly well, allowing all selections and popping the costs into their little text fields quite happily.

Can anyone give me a hint as to why THIS one isn't working right?

 

[davidbodhi]

OK, now I see why the specific selections aren't coming up. Their costs are the same. Wherever the cost is the same, the combo only allows selection of the first of the items.

That is *weird*.

On checking my other lists, I see the same thing is happening and I hadn't noticed it.

This *ought* to be simple to do. Whatever field I bind the combo to I get popping up in my text field. That's what I want and it's easy. But I need to distinguish between items that have the same value in that field. They have autonumbered IDs. Access ought to be able to tell them apart without me telling it to, but apparently it can't.

I can't bind the ID field, because then THAT pops up in my text field for cost.

So, what's an elegant way to get around this? Any assistance?

 

[mresann]

Rather than attempt to answer your question, as I don't have all the data, let me prepare this more correct method of filling out your combobox.

I am wondering how your combo box properties are set up. In addition, do you have a sorting arrangement you want shown? I mean, alphabetical by Therapy_Type or sorted by Therapy_Cost? Or a combination of both?

Let's say you want to sort by Therapy_Type. Before you set up the control, create a query that will be the datasource for your control. In this case, I will sort your query by Therapy_Type. Assuming your underlying table is called tblTherapy, create this query:

SELECT TherapyID, Therapy_Type, Therapy_Cost,
[Therapy_Type] & " " & [Tharapy_Cost] AS Therapy_String
FROM tblTherapy
ORDER BY Therapy_Type;

Change "tblTherapy" to the same table as the name in your database.

When you switch to design view, make sure that you have four fields, and the field marked "Therapy_String" is formatted the same as you wish to see in the combo box control. Make sure that all fields are selected in the design view. Save the query as "qryTherapy".

Next, check the properties for the combo box. On the Data tab, select "qryTherapy" as the Rowsource. Next, click the Format tab. Under Column Count, change from 1 to 4. Name the combo box "cboTherapy".

The next step is KEY to displaying information in the combobox. Remember, the combo box will contain all four fields of the query record. In the Column Widths box, type "0;0;0;1". This will allow ONLY the fourth column to be displayed. You can adjust the width amount to your own requirement.

Keep in mind, the VALUE of your selected item in the combobox will be equal to the FIRST column, which is the Unique ID number of the record. So you can go to the next step, which is to populate a text box with the value of Therapy_Cost. Looking back at the query, we notice that it is in the third column. On the property window for the cboTherapy control, under the Data tab, in the box "Control Source", enter the following: "cboTherapy.Column(2)" without the quotes.

See if this helps. If you need help, click FI under any of the items you need help with.

 

[davidbodhi]

Thank you for the detailed response!

I have changed my combo and text boxes to reflect your suggestions as closely as my table and box names allow. Let me give details.

Table
Name: Therapy
Fields: TherapyID, Therapy_Type, Therapy_Cost

Combo box
Name: Therapy_Type
Control Source: cboTherapy_Type.Column(2)
Row Source (all fields selected):
SELECT Therapy.TherapyID, Therapy.Therapy_Type, Therapy.Therapy_Cost, Therapy.Therapy_Type AS Therapy_String
FROM Therapy
ORDER BY Therapy.Therapy_Type;
Column Count: 4
Column Widths: 0";0";0";1.2917"

Text Box
Name: Therapy_Cost
Control Source: cboTherapy_Type.Column(2)

This configuration allows me to see the therapy types in the pick list, but does not allow me to select any. When I let go of the mouse button, the field stays blank. This is true no matter WHICH column I bind to the combo. Additionally, the text box always shows the #Name? error.

If I change your suggestions as follows:
Combo Control Source: Therapy_Cost
Text box Control Source: Therapy_Cost

I can select items in the pick list and see the cost in the box but STILL have the original problem, which is that items of the same cost all default to the first of that cost in the list.

So far, every variation that lets me pick an item in the combo refuses to let me select the 2nd of two items that cost the same, whether I get a number showing in the text box, or not.

 

[davidbodhi]

Does anyone have another suggestion for this problem???

 

[davidbodhi]

I've resolved it. Thanks to anyone who intended to help.

Bound primary key to the combo and changed how the text box was populated.