How to update a table field with data of another table field? 1

[Fekri]

Hi,

to change and update the data of a field in a table with a data which stored in a field in another table, I usually useing updtae query. but this time always the error message make me crazt: "operation must use an updatable query"

the query SQl in like below:

UPDATE [Inventory items], [Find final quantity] SET [Inventory items].[Quantity in Stock] = [expr3]
WHERE ((([Inventory items].[Item Number])=[Forms]![Inventory Review]![Item Number]) AND (([Inventory items].Location)=[Forms]![Inventory Review]![Location]));

where is problem?
or any other sugggestion except update query?

thanks
Ali

 

[PWise]

UPDATE [Inventory items], [Find final quantity] SET [Inventory items].[Quantity in Stock] = [expr3]
WHERE ((([Inventory items].[Item Number])=[Forms]![Inventory Review]![Item Number]) AND (([Inventory items].Location)=[Forms]![Inventory Review]![Location]));

try

UPDATE [Inventory items] SET [Inventory items].[Quantity in Stock] = [expr3]
WHERE ((([Inventory items].[Item Number])=[Forms]![Inventory Review]![Item Number]) AND (([Inventory items].Location)=[Forms]![Inventory Review]![Location]));

 

[Fekri]

thanks,

but "[expr3]" is a field of "find final quantity"

and if I delete it, it will ask "[expr3]" value!!!

Ali

 

[PHV]

How should [Inventory items] and [Find final quantity] be JOINED ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

It seems likely that you are lacking a unique index. You may wish to read:

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp

 

[Fekri]

Yes Remou,

That's right but unfortunately one of this table is made by make-table query and I can not make a field unique in like this query.

but you light up my mind with other way,

if I have to remove the table and keep only "Inventory items", I have to use Dlookup.
But I don't know how to use it in my way:

I need the value of "[expr3]" in the record which the "[lineN]" has maximum value in all the record.
how should I write the Dlookup command??

Thanks
Ali

 

[Remou]

How about:

DlookUp("Expr3","Name of Table","LineN=" & DMax("LineN","Name of Table"))

 

[Fekri]

thanks Remou

You are perfect


Ali