Cascading Combo Boxes with Default Values

[GrandMasterTuck]

Hi folks. I wonder if anyone has some insight that could help me. I've got a form (frmMain) with two combo boxes (cmbCategory and cmbProducts). I have set the combo boxes up to be cascading, so that when you select a value in the cmbCategory box, it influences what is visible and selectable in the cmbProducts box. What I wanted to happen is this:

1. User selects a category
2. cmbProducts defaults to the most recently entered Product within that category, and the form pulls up that product's details.

I have the cascading combo boxes set up so cmbCategory has an AfterUpdate event that runs a macro. In the macro is the command to refresh the cmbProducts box, and the cmbProducts box's RecordSource is set to a query that filters the Products based on the value of the cmbCategory box. I then set the cmbProducts Default Value to a DMax command that pulls the largest ID number from the query. Everything works great, except when I choose one category (the correct product comes up, the latest one entered for that product), then go back and choose a different category without reloading the form. The cmbProducts box stays showing the previous Product, and doesn't automatically update with the latest product for the new category. So it works the first time out of the gate, then just stops working, and won't work until I close and reopen the form again.

I've tried the refresh command, and I've tried adding an AfterChange event to the cmbCategories box. Nothing seems to work! I also tried running a close form/open form command in cmbProducts' After Update to enable the DMax command again, but this blanks out my Categories and Products boxes (which it's supposed to... I don't want anything to come up when you first open the form, both should be blank until you start selecting values).

Any ideas? I hope I wasn't too confusing in my description...

 

[bob fitz]

Can you post a copy in A2003 mdb format for us to play with.

 

[GrandMasterTuck]

ARGH. Okay, I've solved the first problem. I used a SetValue command in the AfterUpdate property of the first combo box (cmbCategories) to set the value of the cmbProducts box to the most recently entered product. It works perfectly... except... it's not updating the form's displayed data. The form is supposed to display the data about that specific product, but when I select a Category, the correct product comes up in the box, but it's not displaying the correct data on the form. How the heck to I get it to update the data on the form when I change that Category?

I've tried a Refresh command, a Requery command, and even did a little RunCode command with a DoCmd Refresh. Still won't update unless I go manually click the cmbProducts combo box control and reselect the value. Then it updates. But I want to just select a value in the cmbCategories box, then have the cmbProducts box update its value to the most recent record within that category (it does) and refresh the displayed data on the form to show that product's data (it doesn't). This is making me crazy!!