Cascading Combos with Disappearing Data

[nschroeder]

Greetings. I have searched the posts and seen this issue brought up several times, but have not found a clear answer. It appeared that the respondents possibly didn't clearly understand the issue, so no offense, but I'm going to try again.

In a form with cascading combo boxes, why does the selected value in the 2nd box disappear on (other) records where the selected value in the 1st box does not match the selected value in the current record? The cascading combos work fine, and the other records still contain the correct value. It's just that they appear blank on the screen until you select that record, and then the value for the record you just left goes blank. For the row source of the 2nd box, I'm using a query with a selection criteria of the value from the 1st box on the form.

The problem only occurs when the lookup table for the 2nd box has an autonumber PK, and the box column width for the first column is 0 (0";1"). If I change the column width to something like .1";1" then the problem goes away, but of course then you just see numbers in the control instead of the values you want.

I have attached a sample db to illustrate the issue. If you open the MainData1 form and select different records, you'll see the values in SubCat appear and disappear accordingly. In the MainData2 form, the problem doesn't occur, but you only see the autonumber values. The only difference between the two forms is the columnwidth on the SubCat control. Note that I have the requery command on both the Current and AfterUpdate events.

Thanks for your help

 

[MarkK]

It's not magic that when you look in a mirror your eyes are never closed.
MainData2 requeries the second combo in the Current event, so whenever you drop it down to check for data, the Current event has just put data there. It is the act of looking that ensures you'll see something.

 

[nschroeder]

Dude, I know that. I wrote the code (see the last sentence in my post). (Also, please reread the 1st paragraph). I would really appreciate some help on this. Thanks!

 

[MarkK]

- Ok, note that there is only one detail section on a form. In a continuous form perhaps it appears that there are many, but if you look at the form in design view there is only one. The same is true then for any control in that detail section.
- What happens in a continuous form is that if you set any property of a control in the detail section of the form, that property is set for every instance of that control and not just the control in the current record. What hides the data in a combo then is that the rowsource of that combo is updated such that the Value of the combo does not appear in the RowSource of the same combo. In this case the combo has no multi-columnar data to disply for the given Value and you see blank.
- But it only shows blank, as you've noted, if the width of the bound column is zero. The combo can always display a value in its bound column.
- And the reason your second form always shows the multi-columnar data is as noted above. In that case the blank columns are always the ones you are not looking at.
- Does this satisfy you?

 

[nschroeder]

Yes, that makes sense. The requery causes the control value to be blank in records where the query (record source) doesn't return a value based on current record. But still, if this is true and there is no solution to the problem, why would anyone ever use cascading combo boxes? I guess the answer is that they work fine, they just don't look right on a continuous form. Thanks.

 

[nschroeder]

I HAVE A SOLUTION!

In the example I had provided, in the MainData1 form the row source for SubCatID is qryCat_SubCats. I did the following:

1. Made a copy of qryCat_SubCats & called it qryAllCat_SubCats
2. In the new query, deleted CatID (which was being used for the cascading criteria value)
3. Saved & closed the query
4. In MainData1, copied the SubCatID control, naming the new control SubCatIDAll, and placed it directly over SubCatID
5. Changed the Row Source of SubCatIDAll to qryAllCat_SubCats
6. In the SubCatIDAll_GotFocus event, placed the following code:

SubCatID.SetFocus
SubCatID.Dropdown

The purpose of the Dropdown method is so they don't have to click the control twice (once to change the focus and again to open the list).
The only other thing I did was changed the tab order of the controls to put SubCatIDAll ahead of SubCatID. Otherwise you won't be able to tab away from it.
As far as I can tell, it now works exactly like it should.

I have attached the updated database in case anyone wants to look at it. I deleted the MainData2 form as it was no longer needed.

 

[MarkK]

Well done! Excellent tenacity. Excellent cleverness. Thanks for posting back with your success.

 

[Cwwrig]

Many thanks to posting your database. I'm not an VBA expert, but I was able to take your database and then copy the code to make it fit what I was doing (updating one box based on another in a continuous form). You saved me a ton of time.

 

[nschroeder]

Glad to hear it! Thanks for letting me know.