Continuous View for Combo box based on another combo box

[bratsche]

I am relatively new to MS Access (1 year) and have learned so much from these forums. I am hoping someone can help me with my current challenge.

I have a form with a combo box that has its Row Source limited by a selection in another combo box.

I have this working (for the most part), after having read faq702-4289, thread702-1040180, an MS Online article ("Basing one combo box on another") and whatever else I was able to find in this forum and on the web.

My dilemma is this: Is there a way to properly display my records if I set the form to Datasheet or Continuous form view? Because the row source of the dependent combo box changes based on the value of primary combo box in the active record, only the active record shows correct data.

Any pointers in the right direction would be much appreciated.

Thanks in advance.

 

[Pampers]

Let me summarize. You have a continous/datasheet form with 2 comboxes on in it (in every record two). You want one combox to sync with the other, in every record.

For that I think you have to use the OnCurrent of the continuous subform/datasheet that will requery the 'child' combox with the 'parent' combox (if you could use those words here).



Pampers
Keeping it simple can be complicated

 

[bratsche]

Pampers,

Thanks for the suggestion, it helped a bit. I still have the problem that any records that do not have the same value in their 'parent' combobox as the active record, display nothing in their 'child' combobox. This makes sense, of course, since each 'parent' value has it's own distinct set of possible 'child' values, and the code is changing the RowSource of the 'child' combobox based on the active record.

I'm starting to think I need to use an unbound combobox somehow....

Thanks again.

 

[Pampers]

Hi there,
I rebuilded the problem. With tblCategory and tblSubCategory. Created a continous form with 2 comboxes, first is CategoryID second combox is SubCategoryID. In the afterupdate event of CategoryID I put:

Private Sub CategoryID_AfterUpdate()
    Me.SubCategoryID.RowSource = "SELECT tblSubCategory.SubCategoryID, tblSubCategory.CategoryName, tblSubCategory.CategoryID FROM tblSubCategory WHERE (((tblSubCategory.CategoryID)=Forms![tblOrder subform]!CategoryID)) ORDER BY tblSubCategory.CategoryName;"
End Sub

Seems like it is working...

Pampers
Keeping it simple can be complicated

 

[bratsche]

Just to wrap up this thread - I ended up adding a second combo box with a row source of all the values for the 'child' field. I used it to display the description of the 'child' filed while keeping the originial combo box to select the 'child' value. The original combo box's row source still being limited by whatever was in the 'parent' combo box.

I locked and disabled the new combo box so it was for display only.

Thanks again for the assist.