Inventory Form Not update-able w/ Subquery

[RFigaro]

I have a daily inventory on hand entry form. I want to get the last inventory on hand for each item to use as the opening inventory. In my query for the record source of the form I used a subquery with select max:

SELECT tblInventoryLineItems.ItemID, tblInventoryLineItems.ItemSales, tblInventoryLineItems.ItemPurchases, tblInventoryLineItems.ItemAdjustment, (select max ([inventory date]) 
From tblinventory as old inner join tblinventorylineitems as oldline on old.[inventory id] = oldline.dailyid 
Where old.[inventory date] < tblinventory.[inventory date]
And oldline.itemid = tblinventorylineitems.itemid) AS InvOpen, tblInventoryLineItems.HardCount, ([OpeningInventory]+[ItemPurchases])-([ItemSales]+[ItemAdjustment]+[HardCount]) AS Deviation
FROM tblinventory INNER JOIN tblInventoryLineItems ON tblinventory.[Inventory ID] = tblInventoryLineItems.DailyID;

to obtain the last inventory date for the item (I still need to figure out how to use that date to get the hard count from that record) this works fine however I am left with an un-updateable query and I need to be able to enter the new inventory on hand in the form. I thought dlookup with dmax would be a good solution but I have been unable to get that to work. We often correct old inventory amounts so I wanted to avoid a static or stored opening inventory. Any help or suggestions would be greatly appreciated.

 

[CJ_London]

in your form properties, data tab, try changing the recordset type to dynaset (inconsistent updates)

however you also have an inner join which makes me suspect that your form is not designed properly.

You should have a main form with a recordset of tblinventory and a subform with a recordset of tblInventoryLineItems - you then set the subform linkmaster and linkchild fields to inventoryID and DailyID respectively

 

[RFigaro]

Thanks for the suggestion I will try the dynaset setting. I figured if the query results were not update-able due to the sub-query then then the records on form would not be either. The inner -join in the sql was just part of an attempt to get at the last inventory on hand date. My form is set up as you stated with master and child set and the form works fine with the inventory table/inner join removed.

 

[Galaxiom]

Any query that contains an aggregate fuction anywhere will not be updateable.