Update Query will not Update!

[JVermast]

I have two tables, each has a "status" for a project. In the left table there is only one instance of each set, but in the right hand table, each set may be used more than once by different Projects.

I need the Status field of the left table to be set to "Assigned" if ANY of the sets assigned to projects in the right hand table are "Assinged"

Here is the update query I have setup but when it runs, it updates "0" rows.

8 is the id for the status "Assigned" in the SetStatus table
2 is the id for the status "Assigned" in the Status table.

 

[FoFa]

Remove the tblStatus and tblSetStatus (they are not needed). I think the tblStatus inner join is what is causing issues.
One other thing, have you run the query outside the designer window? The designer window when you "test" run the query only shows the value of the rows TO BE UPDATED, not the value it will be UPDATED WITH. THis throws some people off and make thems think it isn't updating.

 

[JVermast]

Okay that worked wonderfully, now I need this query to run anytime a change is made on a certain form. I have added it to before update, but is there any way I can make the 2 confirmation windows not pop up when the query is run?

 

[FoFa]

Use SETWARNINGS either in your amcro or the DOCMD version in VBA, use help for syntax in VBA.

 

[JVermast]

I'm not using a macro, what is DOCMD?

 

[JVermast]

Got it. DoCmd

 

[Pat Hartman]

now I need this query to run anytime a change is made on a certain form.

The status field in the 1-side table isn't necessary since you can derive it from the status in the many-side table. Create a totals query that counts the rows in the assignment table for each set that have status = 8. Add criteria that selects rows where the count >= 1. You can then join to this query whenever you want to know the status. Your current structure violates both second and third normal forms.