Recordset not updateable (1 Viewer)

[Isskint]

Hi everyone,

I am trying to 'fix' a Supply database, author unknown. I have created a query to provide a goods in diary, but the resulting recordset can not be updated.

I have provided a screen shot of the relevant relationships. There are 5 tables;
Purchase_Orders_List - Stores the PO ref and the Supplier
OrderBook_List - Stores details of the products ordered with a FK to Purchase_Orders_List
Shipping_List - Stores details of the shipping including container number, arrival date etc with a FK to Purchase_Orders_List.
GoodsIn_Diary - stores details of date and time delivery due with a FK to Purchase_Orders_List
Invoice_List - Stores details of various invoices against the PO with a FK to Purchase_Orders_List

Background to relationship.
For any single Purchase Order, there could be multiple order lines (OrderBook_List), multiple shipping/transport requirements (Shipping_List) and multiple invoices (Invoice_List). The GoodsIn_Diary table is separate as bookings can be postponed/amended so the history of multiple bookings for the same PO is important (costs can be incurred after set times, but if a booking has been postponed the time period is reset).

The query
So the query is based on GoodsIn_Diary table, adding other relevant data from Purchase_Orders_List and Shipping_List. SQL below may help;

SELECT GoodsIn_Diary.giDate, GoodsIn_Diary.giTime, GoodsIn_Diary.giID, GoodsIn_Diary.poID, GoodsIn_Diary.giDay, GoodsIn_Diary.giRef, GoodsIn_Diary.giBW, GoodsIn_Diary.giBB, GoodsIn_Diary.giStatus, GoodsIn_Diary.giNote, GoodsIn_Diary.giLock, Purchase_Orders_List.suppID, Purchase_Orders_List.poWC, Shipping_List.shpCont, Shipping_List.shpArr, Purchase_Orders_List.poDesc, Purchase_Orders_List.poDest, Shipping_List.shpID
FROM (Purchase_Orders_List INNER JOIN GoodsIn_Diary ON Purchase_Orders_List.poID = GoodsIn_Diary.poID) INNER JOIN Shipping_List ON Purchase_Orders_List.poID = Shipping_List.poID
WHERE (((Purchase_Orders_List.suppID) Like IIf([Forms]![Purchase_Orders_List]![cmbSupp].[listindex]=-1,"*",[Forms]![Purchase_Orders_List]![cmbSupp])) AND ((Purchase_Orders_List.poWC) Like IIf([Forms]![Purchase_Orders_List]![cmbWC].[listindex]=-1,"*",[Forms]![Purchase_Orders_List]![cmbWC])))
ORDER BY GoodsIn_Diary.giDate, GoodsIn_Diary.giTime;

I have even tried creating a sub query to return the details of the Purchase_Orders_List and Shipping_List but this still leaves me with a recordset can not be updated.

What am i missing?

 

[arnelgp]

is it possible not to use Inner Join, but instead Left Outer Join.

 

[Isskint]

Thank you for the suggestion.

Out of desperation (though not truly a correct approach) i have tried every combination of join in the query:banghead:. Either i can not run the query (which is understandable due to ambiguous joins) OR the resulting record set is not updateable.

 

[Rabbie]

This link gives you the possible reasons why a query may not be updateable. Hope this helps

 

[Isskint]

Thanks Rabbie.

I think i have already exhausted that list. The only one that could have applied is down to indexing, but the joins use the PK.

 

[Minty]

I'm not sure but would you not be better displaying sub records in sub forms for editing purposes, then you don't have the issue of trying to gather everything into one massive query?

They don't need to behave or look like sub forms after all.

 

[Isskint]

Hi Minty

I currently have a PurchaseOrder form that contains subforms for each of the other tables and you can update everything fine - just one PO at a time so slow and no overview. But the purpose of this query is to present all the relevant data (everything from GoodsIn_Diary and specific data from the other tables) on either a form where updating is possible or a report for printing.

 

[Minty]

If you can restrict the form to only need to update two tables where one is using a left outer join you can do it.
If not your going to have to use either an unbound form or unbound controls to process your updates.

 

[Isskint]

Hi Minty

I only need to be able to amend data on the GoodsIn_Diary table and the Shipping_List table. But i need the details from a couple of the Purchase_Orders_List table.

I have just tried creating a query only using Diary and Shipping and Dlookup() the other fields but even that is not updateable. But this has made me wonder now. Should i change the FK on the Diary to the Shipping PK? after all it is the shipping that will be received?

 

[Minty]

The Dlookup Fields will probably mess things up, if it's in the query.
Create a sub form with the data you don't need to edit linked back to the simpler 2 table query you do need to edit. I can't tell how easy that will be as I can't see your entire relationship links but it should be possible.

 

[mjdemaris]

Do you have the "Totals" option enable in your query? This would create an un-updateble recordset.