Cascading Combo Box - Loses Value

[CMD]

Hello all,

Searched and searched and can't figure this one out. I have built several combo boxes in other DB's but haven't had this happen before. I am adapting a template DB to work for me. The form is continuous and I added a field [CategoryID], I want the [ProductID] to filter based on CategoryID choice. Pretty straight forward and it works, but the [ProductID] field will not hold the text value. It's a number field to hold the ID value, and bound column is 1(Select query is ID,ProductName,StandardCost) with ID and StandardCost column widths set to 0cm. I can choose CategoryID, the ProductID combo filters correctly, then when I proceed to the next record, the ProductID goes blank.
Any ideas???

 

[spikepl]

You only have one combo in a continuous form - not one per record. If you filter the combo then in the other records the values required for display would be filtered away too. There exist some fiddles for a continuous form, involving some trickery with a textbox - I might be able to find a link in a while

Update: http://www.accessmvp.com/kdsnell/SampleDBs.htm

 

[CMD]

Thanks so much!! You hit the nail on the head! I will try the fix and see if it works. It seems the more I learn about Access the less I know!!

 

[CMD]

Can I resurrect this question? I don't know what is going on. I have set up several cascading combo's in my database and now I am "cleaning" up and changing some references. When I created my DB I wasn't using ID's as I should have, so now I am changing some formatting so that I am storing ID's instead of full descriptions and the like.
Now the cascading combo's I had set up and working will not hold the initial value when I change the parameter box.
ie. Combo one has CategoryID, mtlCategory (bound column 1)
Combo two: ProductID,Description,Unit, Cost where CategoryID is same.
Works just ducky, until I change the CategoryID, then the values disappear from the form. They are recorded in the table, but not visible on the form. Why would it quit working???

 

[Beetle]

Are we still talking about a continuous form? Spike already covered that in #2. Is this a new problem not related to a continuous form?

 

[CMD]

No, this is a single form. The database is designed for estimating. I had it set up so that I would choose the Material Category, then the next combo box would be filtered by the Category. And everything worked just lovely. Now that I changed so that it is pulling ID's instead of Text it just won't hold that CategoryID (shows as text, but bound column is the ID).

 

[CMD]

I re-created my situation in a simplified DB with just the relevant tables. Could someone please show me where I have gone sideways. As I stated, it was working fine when I was pulling and storing text, but as I am learning, it is better to store as little information in tables as possible and use queries instead.
Thanks so much!

 

[Beetle]

Add a requery of the Product combo box in the Current event of your form as well;

Private Sub Form_Current()

    cboProductID.Requery
    
End Sub

 

[CMD]

Thank you so much! I thought I had enough requery's! Could I ask why that made all the difference?

 

[Beetle]

Since the Product combo box query is limited to only products that match the CategoryID of the current record, when you move to a new record it needs a requery so it can pick up the correct information for the CategoryID of the record you just moved to.