Join query update field (1 Viewer)

[mowatt55]

This might be hard for me to successfully explain I have a production schedule that uses a field called 'nOrder' to prioritize jobs on a machine. the problem i'm having is the query that its connected to doesn't update that order when the current job is completed. so jobs are 1,2,3,4 etc for a specific machine. when job #1 is complete the order is still showing as 2,3,4 and it has to manually get moved.

this is alot, but this the join query behind that process:
SELECT IIf(Mid([Draw_Machine],1,1)="D",Mid([Draw_Machine],1,2) & IIf([norder]<10,"0" & [norder],[norder]) & Mid([Draw_Machine],4,1),[Draw_Machine]) AS SortBy, wo.Draw_Machine, wo.WO, wo.Part_Number, tblPartSpec.COATING, qryPartPrimaryLbsHr.Productnbr, qlkpSupplyWire.StepPN AS compPart, wo.Description, wo.Total, wo.Kilos, wo.Due, wo.norder, wo.State, wo.Completion_Date, wo.Draw_Reels, IIf([SumOfKilos] Is Null,0,[sumofkilos]) AS Expr1, IIf((wo!Total-qrySELbsComp!SumOfKilos) Is Null,wo!total,wo!Total-qrySELbsComp!SumOfKilos) AS Remaining, IIf(Nz([tlkpPlateAwGlbsHr].[lbsHr],0)=0 And Nz([qryPartPrimaryLbsHr].[LbsHr],0)=0,Null,RoundC(IIf(Nz([tlkpPlateAwGlbsHr].[lbsHr],0)=0 And Nz([qryPartPrimaryLbsHr].[LbsHr],0)<>0,[Remaining]/([qryPartPrimaryLbsHr].[LbsHr]),[Remaining]/([tlkpPlateAWGLbsHr].[lbshr]*[wires])))) AS HrsRmng, wo.SoNo, PartMaster.Rate, wo.DueC, Left(wo!Draw_Machine,2) AS Mach, wo.TagNote, wo.effprc, qryPartPrimaryLbsHr.WC, qryPartPrimaryLbsHr.machine, wo.Draw_Multiples1, wo.Draw_Multiples1_Kilos, tblPartSpec.Wires
FROM ((tblPartSpec INNER JOIN (((PartMaster INNER JOIN wo ON PartMaster.Part = wo.Part_Number) LEFT JOIN qrySELbsComp ON wo.WO = qrySELbsComp.WO) LEFT JOIN qlkpSupplyWire ON wo.Part_Number = qlkpSupplyWire.LWIPN) ON tblPartSpec.LWIPN = wo.Part_Number) LEFT JOIN qryPartPrimaryLbsHr ON wo.Part_Number = qryPartPrimaryLbsHr.LWIPN) LEFT JOIN tlkpPlateAWGLbsHr ON (tblPartSpec.AWG = tlkpPlateAWGLbsHr.AWG) AND (tblPartSpec.COATING = tlkpPlateAWGLbsHr.Plate)
WHERE (((wo.State)<>"completed"))
ORDER BY IIf(Mid([Draw_Machine],1,1)="D",Mid([Draw_Machine],1,2) & IIf([norder]<10,"0" & [norder],[norder]) & Mid([Draw_Machine],4,1),[Draw_Machine]), wo.Draw_Machine, wo.norder
UNION SELECT IIf(Mid([Draw_Machine],1,1)="D",Mid([Draw_Machine],1,2) & IIf([norder]<10,"0" & [norder],[norder]) & Mid([Draw_Machine],4,1),[Draw_Machine]) AS SortBy,wo.Draw_Machine, wo.WO, wo.Part_Number,"","","" as compPart, wo.Description, wo.Total, wo.Kilos, wo.due, wo.norder, wo.State, wo.Completion_Date, wo.Draw_Reels, 0 AS Expr1, 0 AS Remaining, 0 AS HrsRmng, wo.SoNo,0, wo.DueC, Left(wo!Draw_Machine,2) AS Mach, wo.TagNote, wo.effprc,"",0, wo.Draw_Multiples1, wo.Draw_Multiples1_Kilos,0
FROM tblMaintCodes INNER JOIN WO ON tblMaintCodes.Part = WO.Part_Number
WHERE (((wo.State)<>"completed"));

was hoping someone could offer some assistance ? please

 

[arnelgp]

you can create Another Query from the Query you have and filter it again:

maybe change the Criteria to:

WHERE ((Nz(WO.State, "@$")) <> "completed")

 

[Pat Hartman]

If you have a a recordset open, it is loaded into memory so although over time, it will "see" updates to field values, it will never see newly added records. You would need to requery the form or report in order for it to see new records or changes in status that would remove the records from satisfying the query's where clause.