Update query baffler???

[Sam Summers]

I have been working on this for a week and cant get my head round it at all even though i have successfully managed it before.
Here goes

I am opening a form (ViewbyKit) which displays a whole kits items.
From this the user clicks on a button to open another form (KitMove) on this the user selects a new location from a combobox (JobNo) and clicks on a button that runs the update query (JobChange).

This is the SQL for it:

UPDATE Equipment SET Equipment.LocationID = [Forms]![KitMove]![JobNo]
WHERE (((Equipment.KitRef)=[Forms]![ViewbyKit]![KitNo]) AND ((Equipment.Scrapped)=False) AND ((Equipment.Lost)=False));

I am trying to ensure that only items in the selected kit are relocated and not all items in the Equipment Table.

But when i run the whole thing, nothing is happening and the items are staying where thy are.

Many thanks in advance

Please help me!!

 

[ColinEssex]

Should it be an append query?

Col

 

[neileg]

Are LocationID and JobNo the same datatype?
Why are you surrounding your field names in () brackets? If you need brackets they should be [].
Are both forms open at the point of running the query?

 

[Sam Summers]

Cheers guys.

It is definately an update query.

Brackets were only to show you the name here.

They appear to be the same data type but i will check this.

Both forms are open.

 

[neileg]

Have you by any chance set up a table level lookup on Equipment.LocationID? This can really mess things up.

 

[Sam Summers]

Not on the LocationID but i do have on the KitRef.
It looks up values in a related Lookup table?
Whereas LocationID is a query based Combobox.

Thanks again

 

[Sam Summers]

I have just cracked it i think!!
I looked back at my old posts and tried DFitz's idea in this post:

http://www.access-programmers.co.uk/forums/showthread.php?t=57814