Cascade boxes not working

[antimoneylaundering]

I'm an Access newbie (first time user) and am struggling with cascade boxes. I haven't been able to find anything that has helped me.

I am trying to create boxes with dependencies.
I have Stage.
Then Step which is dependent on stage.
Then Task which is dependent on stage.
The primary key for each is ID.



I have a table for Stage, Step, Task, and a full table (which is currently labeled Table 2) which contains information for everything.

When I go to look at it all of the options are available. For instance, if I select stage all steps are available rather than the ones that are only applicable to that stage.

Joins
Full Table to Task (Inner Join)
Full Table to Stage (Inner Join)
Full Table to Step (Inner Join)
Stage to Step (Inner Join)

I have the following query:
SELECT Table2.Stage_ID, Table2.Step_ID, Table2.Task_ID
FROM ((Table2 LEFT JOIN Task ON Table2.[Task_ID] = Task.[ID]) LEFT JOIN Stage ON Table2.[Stage_ID] = Stage.[ID]) LEFT JOIN Step ON Table2.[Step_ID] = Step.[ID]
WHERE (((Table2.Step_ID)=[Forms]![QA Form_Use_Old]![cboStep]));

Where am I going wrong?

[ItsMe]

Why would you create a join on a RowSource for a combo? Granted, there are times where you would want to create a JOIN. However, you are creating Cascading Dependent Combo Boxes.

So, give each combo a Rowsource for its respective table. Use the Query Builder by clicking the ellipses(...) next to the RowSource Property. Once you have added the appropriate table, add the name of the form and the name of the control to the criteria of the appropriate field (the Foreign Key Field).
Forms!NameOfForm!NameOfComboIDependOn

[antimoneylaundering]

I am working off of a previously created database so that's what it was set up as and I didn't know that you didn't have to have joins. I should have probably specified this initially. I have created row sources. On table 2 I have the following row sources:

Lookup to stage: SELECT [ID] AS xyz_ID_xyz, [Stage] AS xyz_ID_xyz, [Stage] FROM Stage ORDER BY [Stage];
Look up to step: SELECT [ID] AS xyz_ID_xyz, [Step] AS xyz_ID_xyz, [Step] FROM Step ORDER BY [Step];
Lookup to task: SELECT Task.[ID] AS xyz_ID_xyz, Task.[Task] AS xyz_ID_xyz, Task.[Task] FROM Task ORDER BY Task.[Task];

Will these particular row sources not work? Should I get rid of the query?

[June7]

Does this help http://datapigtechnologies.com/flash...combobox2.html

[ItsMe]

...Will these particular row sources not work? Should I get rid of the query?

Those don't look bad. In the form, while in design view, you can view a combo's properties. You can place the name of a Query Object in the Rowsource property or you can place an SQL statement. It is up to you. If you click the elipses(...) the Query Builder will launch and the result will be an SQL statement in the RowSource.

You can review the video June linked to. It looks like what you have left is to add the WHERE criteria. You can do this by typing something like the following in the criteria field (within the Design Grid at the bottom of the Query Builder window). Understand that the video tutorial that is linked to is of an older version of Access and newer versions do not require you to type "Where". Instead, type ... Forms! [Enter Key] NameOfForm! [Enter Key] NameOfComboIDependOn [Enter Key]

You can do this from the Query Object or the Form.

Forms!NameOfForm!NameOfComboIDependOn

[antimoneylaundering]

Thank you all for your responses. I have tried adding Forms!NameOfForm!NameOfComboIDependOn to the criteria and it's still not working.
I have tried to set an Afterupdate event and that is not fixing the problem either.

I have been looking more closely at the tables and the row sources previously mentioned are the row sources on the form and are not what is on the table. When I looked more closely at "Master Table" I realized that these were lookups. Stage, Step, and Task have separate tables and they are all combined in "Master Table"


The lookup for stage is:
SELECT [ID] AS xyz_ID_xyz, [Stage] AS xyz_DispExpr_xyz, [Stage] FROM Stage ORDER BY [Stage];

The lookup for step is:
SELECT [ID] AS xyz_ID_xyz, [Step] AS xyz_DispExpr_xyz, [Step] FROM Step ORDER BY [Step];

The lookup for task is:
SELECT [ID] AS xyz_ID_xyz, [Task] AS xyz_DispExpr_xyz, [Task] FROM Task ORDER BY [Task];

Could these lookup fields be my problem?


*Previously called Table2, but updated to make less confusing.

[ItsMe]

I am having a hard time following what you have tried and understanding what your DB looks like. Why don't you create a new Access file and import the relevant forms and tables into the new file. Remove any sensitive/private data, compact and repair, zip it down, and upload here so we can review it.

[June7]

You reviewed the DataPig tutorial referenced in post 4?

I am also confused by your descriptions. As ItsMe suggests, provide db for analysis.

[antimoneylaundering]

Unfortunately I cannot upload the database as is. There is no way for me to remove enough information to upload it directly. I will create a replica and provide it.

[alansidman]

You should also look at this tutorial

http://www.fontstuff.com/access/acctut10.htm