Updating one combo box from another using select query

[retro]

I am trying to make a combo box that is controlled by the value in a previous combo box. The first combo box lists genres for games (e.g. RPG, Sports, Action) and the second, when necessary, will expand on this. For example, if Sports is selected in the first, the second would display a list of sports (Football, Hockey etc.).

The following items from my database are relevant to this question:

tblGames

GameGenre
GameSubGenre​

tblGenres

Genre​

tblSubgenres

Subgenre
SubgenreGenre​

qrySubgenres

tblGames uses a lookup wizard to assign the tblGenres list to the GameSubGenre field. Also, tblSubgenres uses a lookup wizard to get the value for SubGenreGenre from tblGenres.

I built the following select query using the expression builder:

SELECT tblSubgenres.Subgenre, tblSubgenres.SubgenreGenre
FROM tblSubgenres
WHERE (([SubgenreGenre]=Games!GameGenre));

When I run the query, it asks me to enter a value for Games!GameGenre. If I enter a correct value, it lists just the sub-items I want. However, if I set GameSubGenre in tblGames as a combo box that looks up from qrySubgenres, the combo box will remain blank always.

What should I do to get this working properly?

Thanks in advance for any assistance!

 

[neileg]

I assume Games is the name of your form and GameGenre is the first combo box. The syntax required is:

WHERE (([SubgenreGenre]=Forms!Games!GameGenre));

 

[retro]

Ahh, whoops! That should say tblGames

Now that I've corrected that, when I try clicking the drop-down on the combo box (on the table), it asks for a value.

Am I able to assign the query to a combo box directly on the table, as I am trying to do? The value should be stored as GameSubGenre in tblGames.

 

[neileg]

tblGames uses a lookup wizard to assign the tblGenres list to the GameSubGenre field. Also, tblSubgenres uses a lookup wizard to get the value for SubGenreGenre from tblGenres.

I hope you're not using the table level lookups. These are a nightmare and cause more trouble than they are worth.

Ahh, whoops! That should say tblGames

Now that I've corrected that, when I try clicking the drop-down on the combo box (on the table), it asks for a value.

Am I able to assign the query to a combo box directly on the table, as I am trying to do? The value should be stored as GameSubGenre in tblGames.

I'm confused.

Combo box on a table? What? Is this not in a form?

You should have a combo box on a form. This combo should be bound to the field you want to update. The row source for the combo should be the query.

It would be worth you doing a search in these forums for cascading combos. You find plenty of examples and discussion.

 

[retro]

Sorry! That's me being a combination of thick, half awake and trying to do several things at once! What I meant was that I have a LOOKUP WIZARD on the table, which gets its data from the query. Is that what you mean by table level lookups?

I also have a form based on tblGames. Of course, the end result I want is for the SubGenre combo box on the form to be limited by the Genre combo box.

Thanks for the search tip - I was trying various things with no joy. Hopefully I just needed the correct jargon!

 

[neileg]

Sorry! That's me being a combination of thick, half awake and trying to do several things at once! What I meant was that I have a LOOKUP WIZARD on the table, which gets its data from the query. Is that what you mean by table level lookups?

Yup. Don't use them. They work after a fashion when your needs are simple. However they just complicate matters when you want to move on. You shouldn't be using tables to enter data, anyway, that's what forms are for.

 

[boblarson]

You shouldn't be using tables to enter data, anyway, that's what forms are for.

A big AMEN to that!