Combo Box Pulling Data from Empty Query

[dancole42]

I have a combo box that's based on a query which uses as its criteria the value of another combo box on the same form. Basically, a user selects a product from the first combo box, and the second combo box populates with any product releases associated with that product:

Combo Box1: Product (bound to product_id, row source is a table of products). The AfterUpdate event calls for the Release combo box to be requeried.

ActiveRelease query selects all releases WHERE product_id = [Forms]![NewBug]![Product]

Combo Box2: Release (bound to release_id, row source is the ActiveRelease query)

Now here's the thing. It WORKS. Until I select a Product, the release dropdown is blank. If I select Product1, the Release combo box shows me releases associated with that product. Same with Product2, Product3, etc.

If I open the ActiveRelease query, it shows results matching the combo box.

Great!

However, if I close and then open the form again and do not select a Product, the Release combo box now shows the first value in the Release table. I don't know how it's pulling this. When I open the ActiveRelease query it, as expected, is blank. It's just the combo box that's supposedly pulling from that query that shows information.

Any ideas?

Thanks!

 

[JHB]

Try a "Combo Box2" requery on the form open event.

Me.YourCombo Box2 Name.Requery

 

[dancole42]

Try a "Combo Box2" requery on the form open event.

Me.YourCombo Box2 Name.Requery

Tried that. I even tried the same thing for the OnClick event of the combo box itself.

This is driving me nuts! How is the combo box pulling data from an empty query??? :banghead:

 

[bob fitz]

Can you show us the SQL statement of the ActiveRelease query that is used to populate the second combo box.

 

[dancole42]

Can you show us the SQL statement of the ActiveRelease query that is used to populate the second combo box.

SELECT t_Release.ReleaseName, l_Product.ProductName, t_Release.Release_id, t_Release.isReleased, l_Product.Product_id
FROM t_Release INNER_JOIN l_Product ON t._Release.Product = l_Product.Product_id
WHERE (((t_Release.isReleased)=False) And ((l_Product.Product_id)=Forms!NewBug!Product))
ORDER BY t_Release.ReleaseName;

The Release combo box is bound to column 3 (the Release_id).

The thing is, when I open the query, it shows up blank when Forms!NewBug!Product is blank. It's ONLY the combo box that has data in it. I don't get where the combo box is getting data from if the query it pulls from is blank.

 

[bob fitz]

Why not remove the Row Source property of the second combo box and set it in the After Update event of the first combo box.

 

[dancole42]

Why not remove the Row Source property of the second combo box and set it in the After Update event of the first combo box.

So to the After Update event of the first, I put:

Me.ProductRelease.RowSource = "ActiveRelease"
Me.ProductRelease.Requery

That works, but it still seems weird to me that the first way wouldn't.

Thanks!

 

[bob fitz]

Can you post a copy of the db in A2003 mdb format.

 

[dancole42]

Okay, I don't remember why I included the Products table in the query, and it really shouldn't matter, but removing it seems to have solved the problem. Weird!