Update Query modifying records it shouldn't.

[akeller]

My update query is modifying the IncomingJobID_FK on previous records.


It is taking the IncomingJobID_FK from a comobox on a form and updating that field on my first row when it shouldn't.
It should ONLY update empty fields within rows that contain the IncomingJobID_FK .

Any Ideas?

UPDATE tblIncomingJobJoin SET tblIncomingJobJoin.Claimed = "-1", tblIncomingJobJoin.InspectorID_FK = [Forms]![tblIncomingJobJoin1]![cboInspectorID_FK], tblIncomingJobJoin.MachineID_FK = [Forms]![tblIncomingJobJoin1]![cboMachineID_FK], tblIncomingJobJoin.CalSphereID_FK = [Forms]![tblIncomingJobJoin1]![cboCalSphereID_FK], tblIncomingJobJoin.Comments = [Forms]![tblIncomingJobJoin1]![txtComments]
WHERE (((tblIncomingJobJoin.InspectorID_FK) Is Null) AND ((tblIncomingJobJoin.MachineID_FK) Is Null) AND ((tblIncomingJobJoin.CalSphereID_FK) Is Null) AND ((tblIncomingJobJoin.IncomingJobID_FK)=[Forms]![tblIncomingJobJoin1]![cboIncomingJobID_FK]));

[Minty]

Is claimed really a text field ? You're setting it (or attempting to) to a text value "-1"

I've reformatted your query to make it easier to see what's happening;

UPDATE tblIncomingJobJoin SET tblIncomingJobJoin.Claimed = "-1", 
tblIncomingJobJoin.InspectorID_FK = [Forms]![tblIncomingJobJoin1]![cboInspectorID_FK], 
tblIncomingJobJoin.MachineID_FK = [Forms]![tblIncomingJobJoin1]![cboMachineID_FK], 
tblIncomingJobJoin.CalSphereID_FK = [Forms]![tblIncomingJobJoin1]![cboCalSphereID_FK], 
tblIncomingJobJoin.Comments = [Forms]![tblIncomingJobJoin1]![txtComments]
WHERE (((tblIncomingJobJoin.InspectorID_FK) Is Null) 
AND ((tblIncomingJobJoin.MachineID_FK) Is Null) 
AND ((tblIncomingJobJoin.CalSphereID_FK) Is Null) 
AND ((tblIncomingJobJoin.IncomingJobID_FK)=[Forms]![tblIncomingJobJoin1]![cboIncomingJobID_FK]));

I'm not sure this makes sense, or at least your form names aren't helping.
Having a form called tblIncomingJobJoin1 is at the very least confusing... Especially to outsiders looking in.

[akeller]

Is claimed really a text field ? You're setting it (or attempting to) to a text value "-1"

I've reformatted your query to make it easier to see what's happening;

UPDATE tblIncomingJobJoin SET tblIncomingJobJoin.Claimed = "-1", 
tblIncomingJobJoin.InspectorID_FK = [Forms]![tblIncomingJobJoin1]![cboInspectorID_FK], 
tblIncomingJobJoin.MachineID_FK = [Forms]![tblIncomingJobJoin1]![cboMachineID_FK], 
tblIncomingJobJoin.CalSphereID_FK = [Forms]![tblIncomingJobJoin1]![cboCalSphereID_FK], 
tblIncomingJobJoin.Comments = [Forms]![tblIncomingJobJoin1]![txtComments]
WHERE (((tblIncomingJobJoin.InspectorID_FK) Is Null) 
AND ((tblIncomingJobJoin.MachineID_FK) Is Null) 
AND ((tblIncomingJobJoin.CalSphereID_FK) Is Null) 
AND ((tblIncomingJobJoin.IncomingJobID_FK)=[Forms]![tblIncomingJobJoin1]![cboIncomingJobID_FK]));

I'm not sure this makes sense, or at least your form names aren't helping.
Having a form called tblIncomingJobJoin1 is at the very least confusing... Especially to outsiders looking in.

The Claimed field is a Yes/No field. The "-1" actually does what it's supposed to. Yeah, I know the naming sucks, I plan on updating once I get this issue figured out.

[Minty]

Okay - so if the query looks right , change it to a select and see if it pulls the right records?

[akeller]

Figured it out. It had nothing to due with my query but was because of a bound combox on a form. I just changed it to unbound and set the row source as the value from a parent forms combo.

Thanks for you help

[Minty]

Glad you sorted it - obviously me just being near the thread steered you in the right direction !