Combo Box Value Disappears on Update

Question

The following is the design of my tables:

<o:p></o:p>

<o:p> </o:p>

 

I have a form frmObjectiveInput with a subform on it sfrmArea.  Here are the details on the forms:<o:p></o:p>

frmObjectiveInput:<o:p></o:p>

Record Source – tblObjective<o:p></o:p>

Includes a place for the ObjectiveID and Objective description.<o:p></o:p>

<o:p> </o:p>

sfrmArea<o:p></o:p>

Record Source - SELECT tblAreaObjMap.AreaIDFK, tblAreaObjMap.SectionIDFK, tblAreaObjMap.ObjectiveIDFK FROM tblAreaObjMap;<o:p></o:p>

The sbuform is a continuous form.<o:p></o:p>

<o:p> </o:p>

On the sfrmArea, I have a set of cascading combo boxes.  They are called cboSection and cboArea.  Both are combo boxes.<o:p></o:p>

<o:p> </o:p>

cboSection:<o:p></o:p>

Control Source – SectionIDFK<o:p></o:p>

RowSource - SELECT tblSection.SectionName, tblSection.SectionID FROM tblSection ORDER BY tblSection.SectionID;<o:p></o:p>

Bound column – 2<o:p></o:p>

<o:p> </o:p>

cboArea:<o:p></o:p>

Control Source – AreaIDFK<o:p></o:p>

Row Source - SELECT tblArea.AreaName, tblArea.AreaID, tblArea.SectionIDFK FROM tblArea WHERE (((tblArea.SectionIDFK)=[forms]![frmObjectiveInput]![sfrmArea]!cboSection.Value));<o:p></o:p>

Bound column – 2<o:p></o:p>

<o:p> </o:p>

The combo boxes appear to be functioning as intended.  They display the values I expect and they populate the underlying table.  The problem is that the values in cboArea disappear after cboSection is updated for the next record on the subform.  The values only show for cboArea if it has the same sectionIDFK in cboSection as the current selection.<o:p></o:p>

<o:p> </o:p>

Any ideas on how to get the combo choices to remain after I select them.  I know it something to do with me using a continuous form for the subform, but I can't quite figure out how to get this one fixed.<o:p></o:p>

Thanks.

Answer 1

if the combo boxes in the sub form are in the sub form's header - then the criteria of cboArea will not be valid in attempting to call a field in the detail area because there is 1 field name, but many field records due to it being a continuous form type.....

Answer 2

All of the combo boxes in the subform are in the detail section of the form.

Answer 3

I do believe that the criteria is too generic:

=[forms]![frmObjectiveInput]![sfrmArea]!cboSection.Value

it is not able to be record specific

off hand I can't remember if cascading combo boxes is feasible in a continuous form; you might try criteria of just

=cboSection

so that it can only look within its own record

or try switching to a single form and adapting to that

Answer 4

​Hi jdmcleod,

>> The problem is that the values in cboArea disappear after cboSection is updated for the next record on the subform.  The values only show for cboArea if it has the same sectionIDFK in cboSection as the current selection

Do you mean that the cboArea only shows the value if it has the same sectionIDFK in cboSection, if cboArea did not have, the value of cboArea was disappear? If so, could you share us why did you use the Row Source below for cboArea.

SELECT tblArea.AreaName, tblArea.AreaID, tblArea.SectionIDFK FROM tblArea WHERE (((tblArea.SectionIDFK)=[forms]![frmObjectiveInput]![sfrmArea]!cboSection.Value));

Based on your code above, the cboArea only shows the value which equals cboSection.Value. I think it is expected behavior if you use the code above. In my option, if there is no specific requirement, you could try to remove the where statement in the code above.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 5

Hi Edward,

Your assessment of the problem is correct.  The reason I used the WHERE statement was because I wanted to limit the items in the cboArea dropdown (cascading) to the section chosen in cboSection.  The list of areas is somewhat lengthy and I thought it would make the form more user friendly to filter the list.  My problem appears to be one of display only.  The values are updating in the underlying table properly.  I did implement the following work around, although I am not very fond of it, it seems to work for the moment:

I put a text box on top of the combo box making it a little narrower so that the only part of the combo box showing is the drop down arrow.  In the text box's got focus event, I put in some code to set the focus to the combo box.  I then used a Dlookup function in the text box to lookup the area name and display it for each record.  Again, not what i was looking for, but it seems to work so far.  What are your thoughts?

Thanks.

Answer 6

Hi jdmcleod,

I am glad you have found a workaround for your issue. I do not have any advice about this work around so far. If you keep on discuss your workaround, I will recommend you start a general discussion for this. There would be more community members to discuss the question.

Thanks for your understanding.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 7

Curious,

If the value can be correctly displayed in the text box based on the Table values and the values are input by the cbo...and the Bound Column is set to 2...

What are you format settings for

1. Column Count
2. Column Width
3. What Column in your RowSource is the column that should be displayed?

Let's say you have 3 Columns in the RowSource. ID, Specific Value, & Descriptor. In this example the bound column is 1, while you want to display 2 & 3 to the user. So you set the Column Count to 3 and set the Column Widths to 0";1";1"

I have not come across an example of Column 2 being the bound column before but maybe that is just because of the way I list the columns in the RowSource but if I did, then I would need to change the following

Column Count = 3

Bound Column = 2

Column Width = 1";0";1"

I wouldn't be surprised if there is an issue in this method.

Hth

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Answer 8

Thanks! I also did the lookup field, but the user was upset because it kept "disappearing" from one of the boxes. Your fix will help me too. Thanks for posting. Have you found a better solution?