This recordset is not updateable (1 Viewer)

[Mist]

The following query is used as the record source for a form. Why is it not possible to edit the data on the form? If I try to update certain fields using VBA it returns "this recordset is not updateable" What am I doing wrong? Thanx for any assistance.

SELECT IT77C.RefID, IT77C.PoCArCod, IT77C.PocTovr, IT77C.PoCRegYYYY, IT77C.PoCRegMM, IT77C.PoCRegDD, Common.RefID, Common.NoP, Common.PoCITRef, Common.Init, Common.PoCRegNam1, Common.PoCRegNam2, Common.PoCRegNam3, Common.PoCTradnam1, Common.PoCTradnam2, Common.PoCTradnam3, Common.PoCPOAddr1, Common.PoCPOAddr2, Common.PoCPOAddr3, Common.PoCPOAdPcod, Common.PoCRegAddr1, Common.RegUnit, Common.RegCplx, Common.PoCRegAddr2, Common.RegStno, Common.RegStr, Common.PoCRegAddr3, Common.RegSubrb, Common.RegCity, Common.PoCRegAdPcod, Common.PoCMagDist, Common.PoCCoCcRegno, Common.PoCNatBus, Common.PoCLang, Common.BusTelCod, Common.BusTel, Common.BusFax, Common.Cell, Common.BusEml, Common.BusWeb
FROM IT77C INNER JOIN Common ON IT77C.RefID = Common.RefID
WHERE (((IT77C.RefID)=[TempVars]![tempref]));

 

[boblarson]

You are trying to use more than one table which normally shouldn't be done. If the one side is a many it should be in a subform and the other table as the main form.

But you can go into the query in design view and click in the gray area where the tables are located and select PROPERTIES and then under DataSet Type select DYNASET (INCONSISTENT UPDATES).

 

[Mist]

Thanks Bob, In in the gray area where the tables are located I selected PROPERTIES and then under Recordset I selected DYNASET (INCONSISTENT UPDATES). Still the same problem. The tables have a 1-1 relationship and the query returns the correct data. I did notice that the PK in both tables is NOT the field used for joining but this is unique, being 1-1

 

[boblarson]

The PK needs to be the join.

Also read this for reasons why a recordset may not be able to be updated.
http://allenbrowne.com/ser-61.html

 

[Mist]

Thanks again Bob - I changed the PK to the join field and it works perfectly! Thanks also for the link, I will checkit out.