cascaded combo boxes not refreshing

[seangibson]

Sorry, I've been posting alot of questions regarding cascaded combo boxes here but each time I solve one problem I seem to be faced with a new. This time here's the problem: I've got the combo boxes more or less working but they don't refresh. I'm only allowed one chance to choose the value in the first combo box. If I try to change it to another value, the second (dependent) combo box only displays the options from my first choice in the first combo box. This repeats itself when I navigate to enter a new record. I attempted to put the following code in the Current Event of the form's property:

Private Sub Form_Current()
Me.Product.Requery
End Sub

But doing this somehow erases the availability of any choices in the second combo box. This is yet another very frustrating problem and any help would be most appreciated.

[nico5038]

In the first combo's AfterUpdate event you need to requiry the second combo like:
Me.cmb2.requery
or when that doesn't work use;
Me.cmb2.recordsource = Me.cmb2.recordsource

Nic;o)

[seangibson]

I'm sorry to report that neither of those solutions worked. Once I make a choice in the first combo box, the second combo box then has an unchangeable set of choices, both in the current record and in the next one. Any more ideas?

[seangibson]

Tell a lie - it works! I misunderstood your post and thought I needed to get rid of the On Current property. It's now functional (finally!0 - I think. I just need to make the first combo box come up blank when I navigate to enter another record. That shouldn't be dificult, right? Only I'm not sure h ow to do it...

[seangibson]

And yet another - it's now populating my table with the ID. I tried changing the bound column to 2, but when I do this the 2nd combo box no longer displays any options. I'm very close to tears now, having been working on this thing for days, only to encounter new problem after old one. ...sigh...

[nico5038]

Is your second combobox "bound" (has a fieldname as controlsource) and your first combobox not ?

Nic;o)

[seangibson]

Both control boxes are bound, each to a different field in the same table.

[nico5038]

Hmm, can you post the recordsource for the second combobox ?
Looks like it references the value in the record and doesn't "see" the changed value in combobox 1.

Nic;o)

[seangibson]

This is what I see in the Row Source property in the second combo box:

SELECT Products.ProductID, Products.ProductName, Products.CategoryID FROM Products WHERE (((Products.CategoryID)=Forms![enter new trade]!Category)) ORDER BY Products.ProductName;

[nico5038]

Hmm, that's the correct code.
But we might have a problem with the fact that both combofields are "bound".
Access will place the value of the record in the combo, and having the above recordsource wouldn't need the OnCurrent to requery the second combo.

There is however a problem when the value of the second combo is removed from the set defined by the first combo. This will cause the combination in the record to be "illegal".
Sample:
You have these cascading values:
MainCat / Subcat
A / 1
A / 2
B / 1
and in a record "A / 2" is stored. Now the combinations are changed into:
MainCat / Subcat
A / 1
B / 1
B / 2
This will cause the stored record to hold "conflicting" information :-(

The solution for this is to record the combinations with a unique ID and use the combo's to store just one field (that unique ID). Now a combination can be changed for that ID, or you'll get a warning when removing such an ID when it's defined with a referential integrety relation.

It will however require quite some additional coding, as the first combobox can't be bound anymore and will have to be filled with the correct "MainCat" in the OnCurrent event, based on the value determined by the ID from the bound combo2.

Getting the idea?

Nic;o)

[seangibson]

As much as I appreciate the help, I'm afraid it's just too early in my Access career to make sense of it yet. I'm devoting time every day to learning this but I guess I'm getting ahead of myself trying to attempt the combo boxes. It's frustrating in that, according to all the expamples, it works but when I attempt to integrate it into my database, it doesn't work. Unless I can see a working example specifically constructed in the manner which I am attempting, I'm afraid I'm going to have to scrap my database project (or at least the combo box part of it). I really do appreciate the advice, though. It's great to have a site such as this for reference. I f there's anyone who's willing to walk me through building this step by painful step, perhaps we can arrange a cash transaction ;-)

[nico5038]

The bottom line is that you would need a table tblMainSub holding:
MainSubID (autonumber)
Maincode (text or number as it is now)
Subcode (text or number as it is now)

In the target table (where you have Main and Sub now as two fields), you replace the Main and Sub by the new MainSubID.
Now you can use one combo showing the combinations and record the MainSubID in the target table.
Using a separate Main and Sub combobox would now require you to fill initially the Main combobox with the value indicated by the MainSubID, as there's no field you can bind the Main combobox to.

Getting the idea better now ?

Nic;o)