Combo Box Requery in SubForm 1

[dusterb]

I have a basic publisher's Content Management database which contains among other things articles and their respective subject categories and subcategories. The categories and subcategories are displayed in a subform. Each article can have multiple category/subcategory assignment and these are stored in a simple table. The SubForm control source is this table.

ArticleID
CategoryID
SubCategoryID

In the SubForm (CategoryIndexSubForm), there are the two fields, category and subcategory. To make these updatable in the subform, I have made them combo boxes.

The Row Source for category is (a query):
SELECT Categories.CategoryName, Categories.CategoryID FROM Categories;
This is bound to the correct field and I use the 1";0" trick in the Format

The Row Source for subcategory is (a query):
SELECT Subcategories.SubCategoryName, Subcategories.SubCategoryID
FROM Subcategories
WHERE (((Subcategories.CategoryID)=[Forms]![Main]![CategoryIndexSubForm]![CategoryID]));
This is bound to the correct field and I use the 1";0" trick in the Format

In the subform, on the subcategory combo box I have the following onGotFocus event:
Private Sub SubCategoryID_GotFocus()
SubCategoryID.Requery
End Sub

That's it. A user enters a new category/subcategory combination in the subform. They click on the category combo box which populates with all the available categories. Then they click on the subcategory combo box, the requery executes and they get a nice list of the available subcategories for that record's category selection. It works.

But here's the problem... When there are multiple records in the subform, as soon as the subcategory combo box is clicked, all the other previous subcategory assignments in the subform disappear! They are still there, I can make them re-appear by clicking on the subcategory field. But basically only the subcategory that is in focus is visible, the rest are blank (even though there is something there). When I change records in the Main form, I get that same behavior... only the first record in the subform is populated with both category and subcategory. Subsequent records in the subform might contain data in the subcategory field, but like I said, it shows blank.

Does anyone have any suggestions? Do I need to place more refreshes and requery events somwehere else in the subform or on the main form? I've tried most everything.

 

[Eupher]

Hi, dusterb,

Could you please clarify your table structure? You describe what sounds like pretty classic cascading one-to-many relationships, i.e. each article can have many categories, each category can have many subcategories. But it *sounds* like your Categories and Subcategories tables are simply lookup tables. I don't see the tables that hold the categories and subcategories that are related to each specific record in the articles table nor any relationships defined between said tables.

Ken S.

 

[dusterb]

Eupher. Thanks for responding.

The table that contains the category number and subcategory number information for each article is called CATEGORYINDEX which is related to ARTICLES by ArticleID.

The names of the categories and subcategories are stored in tables called CATEGORIES and SUBCATEGORIES, related back to CATEGORYINDEX by number. Also, the SUBCATEGORY table has a CategoryID field to associated subcategories with their category.

ARTICLES
ArticleID *

CATEGORYINDEX
ArticleID *
CategoryID *
SubCategoryID *

CATEGORIES
CategoryID *
CategoryName

SUBCATEGORIES
SubCategoryID *
CategoryID
SubCategoryName

The control for the subform is simply the CATEGORYINDEX table, related to the parent form by ArticleID

There are two fields within the subform as described, category and subcategory. The RowSource for each is a query as describe, and the subcategory combo box has a requery event onfocus which does indeed populate the pulldown with the subcategory names associated with the selected category in that record.

So, the requery and populating of the subcategory combo box based on that selected category in the specific subform record is working. The problem is when I add additional category/subcategory records, I start to show blank subcategories in the records that are out of focus. The only way to "show" them again is to focus on them/click. When I advance the main form and come back, all but the first subcategory shows as blank... even though, clearly the data is there in the control source for the subform, CATEGORYINDEX table.

I think the structure is right. I think the requery that writes out the dynamic subcategory pulldown is right. It just seems I need some mechanism whereby Access will rewrite out the entire subform.

 

[Eupher]

Okay, one more thing: is the subform by any chance in datasheet view or a continuous form?

Ken S.

 

[dusterb]

It is datasheet. I changed the two text fields to be combo boxes of course as described, but yes datasheet.

 

[Eupher]

Sorry, dusterb, still trying to sort out your structure. What is the relationship between Categories table and Subcategories table (if any)? And is there a primary key in CategoryIndex? I'm trying to set up a test DB to replicate your problem, but still don't have all the pieces...

Ken S.

 

[dusterb]

Eupher, thanks very much. Here is a screenshot of the relationships:

 

[Eupher]

Hi, dusterb,

Thanks for the image, that helped me visualize your schema much better.

I've been chewing on this on and off for about 3 days now. Maybe it's just me, but this is a prime example of why I don't care much for subforms. I find them a PITA. I *much* prefer cascading combos and/or listboxes. I think they are easier to control and code.

The problem in this case, I think, is that you are asking your combos to do 2 different things: lookup/store data from a lookup table and display data from another table. Seems not to be a problem as long as no "where" criteria is specified. But changes applied to a field in a datasheet or continuous form apply to *all* rows. So when you requery *with a criteria included* naturally the rows that don't match the criteria will not display the data.

To do this we need to resort to a little trickery:
1) Change your form view from datasheet to continuous.
2) Drag the fields around such that it simulates a datasheet view.
3) Change the Record Source of the subform to include the Subcategories table and add the SubCategoryName field to the grid.
4) Create a textbox just a tiny bit smaller in size than the text area of the subcategory combo. Place the textbox directly over the subcategory combo.
5) Set the Control Source of the textbox to SubCategoryName.
6) Set the Special Effect propert of the textbox to Flat and the Border Style property to Transparent.
7) Set the Locked property of the textbox to Yes.
8) Adjust the size and spacing of the textbox so that it displays properly on your form.

Okay, now your subform should display the data like you want it.

HTH,

Ken S.

 

[Eupher]

Oops, should be:
1) Change your subform view from datasheet to continuous.

Ken S.

 

[dusterb]

Ken, this is really outstanding. I can't thank you enough for taking the extra time. I suspected that was what was going on in datasheet view. And what a trick with the transparent text box! I'm happy to report it is working great! I have a quick followup...

When adding a new subform record from the pulldown, if I leave the focus on the newly selected subcategory and say page forward and back in the main form, I'm still seeing a little of that blanked out subcategory behavior. I just need to change the focus out of subcategory and everything is perfect, to the subform/category field, or anywhere on the parent form. Perhaps I should add an afterUpdate event to the subcategory combo box (the one that is now entirely obscured the new text box except for the pulldown character)? Right now, I'm accomplishing the population of the subcategory pulldown with an onEnter event:

Private Sub SubCategoryID_Enter()
SubCategoryID.RowSource = "SELECT Subcategories.SubCategoryName,Subcategories.SubCategoryID FROM Subcategories WHERE CategoryID=" & CategoryID
End Sub

Could you possibly make some suggestions on the best way to lose focus out of the subcategory field? And perhaps there are some other events where I could stick in that RowSource/refresh call?

Other than that, it is a thing of beauty! Thanks again very very much.

 

[Eupher]

Try this in the main form's OnCurrent event:

Me.Recalc
Me![CategoryIndexSubForm].Form.Recalc

Also check in design view that the subform textbox is Locked, is NOT a tab stop, and is stacked on top of the combo (i.e. Format->Bring to front) - if we did this right, the text portion of the combo should *never* be visible, only the drop-down - and the textbox should display the subcategory name without having to move the focus. Let me know if this does not cure it.

Ken S.

 

[Eupher]

p.s.

...transparent text box...

I'm guessing you just misspoke, but if not, the textbox itself (i.e. the back style) should not be transparent, only the border style.

Ken S.

 

[dusterb]

YES! I think it was the tab stop, but I put in the onCurrent code you suggested on the Main form too. We're cookin'. Works great! Thank you so much.

 

[Eupher]

Or if you really want to set the focus elsewhere, in the combo's AfterUpdate event:

Me![txtboxName].SetFocus

And, in fact, my previous .Recalc suggestion *should* be unnecessary.

Ken S.

 

[Eupher]

You're welcome!

Ken S.

 

[Eupher]

Hi, dusterb,

Just to beat this dead horse a little more... My previous statement:

the text portion of the combo should *never* be visible

is incorrect. When the combo gains focus, the text portion does become visible. But since we're requerying on enter (or setting the row source, which automatically requeries) it's not a problem and displays the correct data.

Ken S.