Hello everyone. I can't believe that this problem is kicking me like it is, as this seems like something that ought to have taken about 10 minutes to complete. It seems Access always finds a way to make something simple complicated.
I'm trying to update a field in a table with values from a query. The SQL code is currently as follows:
UPDATE [Master Parts Data] INNER JOIN CGT3_11_MinServiceStockByPN ON [Master Parts Data].[P/N]=CGT3_11_MinServiceStockByPN.ServicePart SET [Master Parts Data].MinStock = CGT3_11_MinServiceStockByPN.SumOfMinStock
WHERE [Master Parts Data].AutoMinStockOverride=No;
With this, I'm getting an "Operation must use an updateable query" error. I attempted to use a DLookup version of this, something like:
UPDATE [Master Parts Data] SET MinStock = DLookUp("SumOfMinStock","CGT3_11_MinServiceStockByPN","ServicePart='Me.[P/N]'")
WHERE AutoMinStockOverride = No;
...but this doesn't work. Specifically, it just doesn't update anything (but throws no errors). I'm pretty sure the problem lies in the "WHERE" clause of the DLookUp itself, but I'm not sure how else to state it. I've tried it as [Master Parts Data]![P/N] and simply [P/N] with the same results. I've also left out the statement entirely, which of course resulted in every record being updated with the first record returned from the DLookUp statement.
Any help would be greatly appreciated. Thanks!