Using an ID value passed to a form from a previous form in a query

[JamesWB]

I have a big table, EquipmentDetails and separately I have job plans.

Job plans can have many pieces of equipment. I store the relationship in EquipmentDetails_JobPlanDetails, but am feeling frustrated as I can't seem to get Access to build the SQL query from this in the way I want.

I am generating a List Box in a form which is populated from a query.

The query calls on a table which lists ItemID from EquipmentDetails and JobID and in the current form (where the list box is) I want to pick up and display the ItemIDs associated with that JobID. The current JobID reaches my form correctly (I've proven this by displaying it in a text box) from the previous form.

The problem I have is that I can't seem to get the SQL query to only get ItemIDs that are linked to the current JobID. No matter what I try, it either gets all of the ones in that table, or I can't compose one.

What kind of SQL do I need to only grab the ones relating to the current JobID please?

I've tried building it in the design view but it says that it can't do it because the outer joins are ambiguous.

Attached is a print screen of the current query design.

 

[plog]

query to only get ItemIDs that are linked to the current JobID.

This implies you need some sort of query, among the things wrong with the query you posted, you use no criteria. Most likely you need to include a Form Control in the criteria section of that query.

However, that may not work, because like I said, you have a few things wrong with that query. The big one is that you have a circular reference. When building queries or relationships you shouldn't have a loop, there should only be one path between tables, all your tables are connected to each other in multiple ways.

The error you are getting (ambigous join) is because you have two different types of JOINS. You have a LEFT JOIN between JobPlanDetails (show all) and EquipmentDetails (show just matching) and your other two links are INNER JOINs (show just matching).

If you can demonstrate with data what you want your query to return possibly I can help. But that's the issue you have with what you have shown.

 

[JamesWB]

This implies you need some sort of query, among the things wrong with the query you posted, you use no criteria. Most likely you need to include a Form Control in the criteria section of that query.

However, that may not work, because like I said, you have a few things wrong with that query. The big one is that you have a circular reference. When building queries or relationships you shouldn't have a loop, there should only be one path between tables, all your tables are connected to each other in multiple ways.

The error you are getting (ambigous join) is because you have two different types of JOINS. You have a LEFT JOIN between JobPlanDetails (show all) and EquipmentDetails (show just matching) and your other two links are INNER JOINs (show just matching).

If you can demonstrate with data what you want your query to return possibly I can help. But that's the issue you have with what you have shown.

Thanks! I managed to solve it myself eventually, I finally twigged what you said above about the JOINs and so figured out that I had to simplify it and then add a criteria and it worked.