Can an updateable query be made?

[NXMold]

I have a Bill of Materials (tblBOM), and a list of items from purchase orders (tblPOItems). Each PO Item has an BOM Detail Number, Ordered Quantity, and a Received check box.

Now I can build queries that show a sum total ordered quantity for each detail number, and a sum total received quantity. These queries are not updateable.

Next I make a query to join these totals with the bom table, giving me the BOM with Specified quantity, Ordered quantity, and Received quantity. This way I can color-code the bom Form showing items that have pending orders, un-ordered, etc. But this query (and thus, form) is not updateable. Is there any way to avoid that?

I think I could use dsum, but the performance is not acceptable. I could make the form unbound and handle all the data in code... but I don't want such a complex solution. Right now I have a toggle on the form that switches to a stripped down query to allow edits.

 

[Lost500]

NXMold,
You can set your form's record set type to Dynaset (Inconsistent Updates) in the form's properties which will allow you to change the query results on your form. be careful though your query isn't updateable now because there is a one to many relationship in the tables in your query. make sure you are actually changing the data you intend to, test it first!

HTH

 

[NXMold]

Hmm, I can't even edit the query when I open it directly.

Changing the form to inconsistent updates did not make the form updateable. I also tried changing it in the query properties, but still cannot edit the query. Maybe its because of my two-field joins?

 

[PHV]

An aggregate query would never be updateable ...