Yet another Combo Box problem

[Matt Greatorex]

Firstly, apologies for the lengthy description but I wanted to include all information.

I have two combo boxes on the same sub form (Training Records Sub Form). This form is a sub for my Employee Training Main form.
The first box (Control source and name = Job) is populated by the following query, in the Rowsource field:

SELECT [Stations].[Station_Name]
FROM Stations
WHERE ((([Stations].[Department])=[Forms]![Employee Training
Main]![Department]))
GROUP BY [Stations].[Station_Name]
BY [Stations].[Station_Name];

The second box (Control source and name = Group) is populated by the following query:

SELECT [Stations].[Line]
FROM Stations
GROUP BY [Stations].[Line]
ORDER BY [Stations].[Line];

At present, both work fine.

I’m trying to ensure that only the relevant Line values are available, once the user has selected a Station Name. I’ve tried altering the second query as follows:

SELECT [Stations].[Line]
FROM Stations
WHERE [Stations].[Department] = [Forms]![Training Records Sub Form]![Job]
GROUP BY [Stations].[Line]
ORDER BY [Stations].[Line];

When I test the application, I get prompted to enter a value for [Job]. If I type in exactly what I’ve just selected, from the Job list, the Group list works fine. I get the same message if the WHERE clause is any of the following:

WHERE [Stations].[Department] = [Forms]![Employee Training Main]![Training
Records Sub Form]![Job]

WHERE [Stations].[Department] = Me![Job]

WHERE [Stations].[Department] = [Me]![Job]

If anyone has any ideas, they would be greatly appreciated.

 

[ThreeCrow]

Try this and make sure that First ComboBox has been Named in the name property "Job".

First ComboBox
SELECT [Stations].[Station_Name] "add here" [Stations].[Department]
FROM Stations
WHERE ((([Stations].[Department])=[Forms]![Employee Training
Main]![Department]))
GROUP BY [Stations].[Station_Name]
BY [Stations].[Station_Name];


Second ComboBox
SELECT [Stations].[Line]"add here" [Stations].[Department]
FROM Stations
WHERE [Stations].[Department] = [Forms]![Training Records Sub Form]![Job]
GROUP BY [Stations].[Line]
ORDER BY [Stations].[Line];

 

[Matt Greatorex]

Thanks, but no luck

The first and second ComboBoxes were both correctly entered in the 'Name' property.

Queries were then altered, as you suggested. I needed to add the new 'Department' field to the grouping conditions, other than that I copied your idea exactly.

I can see the logic behind it, so I was quite hopeful it would work. However, I get the same prompt when I try to select a value from the second ComboBox.

Out of curiosity, I tried adding the name of the main form before the sub form, on the second box's query, but the same thing happened. For some reason, it can't seem to recognise that the first box exists.

 

[dbnewbie]

Try this...

If your sub form is on the main form, then try this for the second combo box query.

SELECT [Stations].[Line],[Stations].[Department]
FROM Stations
WHERE [Stations].[Department] = [Forms]![Employee Training Main]![Training Records Sub Form]![Job]
GROUP BY [Stations].[Line]
ORDER BY [Stations].[Line];

And this might just be a typing error, but the first combo box query should be the following:

SELECT [Stations].[Station_Name],[Stations].[Department]
FROM Stations
WHERE ((([Stations].[Department])=[Forms]![Employee Training
Main]![Department]))
GROUP BY [Stations].[Station_Name]
ORDER BY [Stations].[Station_Name];

I hope it works for you...


dbnewbie

 

[Rich]

Open the main form in design view and use the Code builder to get the correct syntax for your combo boxes

 

[Matt Greatorex]

Thanks for the ideas.

However, I've tried including the name of the main form (didn't help) and I used the Code builder to construct the initial statement (didn't help, either).