Problem with a combo box cascade

[uny]

Hi people! I'm having some trouble when I try to update a combo box cascading. I'll simplify a bit mentioning only where the problems are.

I have a table with 3 fields: Product, Storage and quantity. The first two are the primary key. I made a form with two combo boxes, the first one is a simple combo box with this as data origin:

SELECT Product FROM Distribution GROUP BY Product ORDER BY Product;

Works fine. In the second one I want to show only the storages where the selected product is, so I create the next query:

SELECT Distribution.Storage
FROM Distribution
WHERE (((Distribution.Product)=[Forms]![Loans]![ProductList]));



I put this query as the data source of the second combo box and then I added in the afterupdate event of the first combo box:

Private Sub ProductList_AfterUpdate()

Me.StorageList.Requery ' StorageList is the dependant combo box

End Sub

The problem: it only works the first time. If I choose an option the combo box is updated, but if I change what I have selected it doesn't change, I must update the entire form manually. I'm not sure if it is a programing problem or something in my access database, because I downloaded some examples of cascading combo boxes that should be working and they don't work.

Any ideas? Oh, and thanks for your help!

[June7]

What table is this being saved into?

Try simplifying the second combobox RowSource:

SELECT Storage FROM Distribution WHERE Product=[ProductList];

If you want to provide your db for analysis, follow instructions at bottom of my post.

[uny]

Sure, no problem providing the db. Only a warning, the database names are in spanish, I translated the names for a better comprehension.
Oh, I tried the simplified solution and nothing, in fact on the previous version of the database I was using "id"s but I remove them trying to simplify it. Anyway, a little explanation with the spanish terms:

Query "ConsultaArmarios": the now more simplified query.
Table "Distribucion": Producto, Armario, Cantidad (the mentioned "Distribution")
Table "Prestamos": where the loans are saved.
The form "Prestamos" is where the 'problem' is.

The form is for "Prestamos", but these data come from "Distribucion". If you have any question about the db just ask it.
PFC_2007_v3.01.zip

[June7]

I had assumed SQL statement was in the second combobox RowSource, not reference to a query object. Cannot simplify as suggested if using a query object.

Why does the posted query reference Form!Loans when the form is Prestamos?

Set the RowSource to:

SELECT Armario FROM Distribucion WHERE Producto=
[listaProducto];

[uny]

No, sorry... I changed it from spanish to english and english to spanish (I only explained with english words). My fault, I tried it with the correct names. Sometimes you try to make something more simple and you complicate more.

The non dependant combo box use the first SQL and the dependant combo box the second one.

The thing is making me loose my mind is why it doesn't update. I mean, if I execute the query alone it works, if I select for the first time one product from "producto" the dependant combo box is updated and show correctly the info, but if I choose another product from "producto" the dependant combo box is not updated.

[June7]

Did you fix the second combobox as I suggested? It works.

[uny]

Weird, it doen't work in my computer. Second combo box now have "SELECT Armario FROM Distribucion WHERE Producto=
[listaProducto];"

At the beggining empty: OK


Then I choose for example "Producto2" and I can see the corresponding info in the second combo box: OK


I choose now "Producto3" instead "Producto2", but the records didn't change, there should be another row.


Could be something wrong or different with my Access configuration?

[June7]

You set the RowSource to SQL statement?

I didn't change anything else and it works.

Delete the combobox and create new one. Sometimes they corrupt.

[uny]

I tried:
- Directly in RowSource (Origen de la fila) put the SQL select:


- Change the query to simplify as you suggested and then at RowSource call for it:


And also try all of this with the old combobox and a new one just in case. I should be able to manage this but this is beating me, it should work but it doesn't.

[June7]

The query object cannot be simplified version.

The query object is irrelevant if the SQL is directly in the RowSource property.

As I said, all I did was change the RowSource property.

If you want to provide revised db, I will test again.

[uny]

I think I made what you told me, still not working at least for me but maybe I have made something wrong.

[June7]

I downloaded this latest version. Opened Prestamos form and each time I select item in Producto, the Armario list changes. Works perfect.

[uny]

Crap, in my computer doesn't work... I have 2007 and you 2010, could be that?

[June7]

Should not be difference. I used to use 2007.

I also tested this as RowSource and it also works:

SELECT Armario FROM Distribucion WHERE Producto=Forms!Prestamos!
[listaProducto];

[uny]

Nope, nothing... I'll try at work tomorrow, there is one computer with Access too but looks like it's the Access on my computer which is not working properly or is something configured different..