SPO list lookup value cannot be edited in linked Access table

%3CLINGO-SUB%20id%3D%22lingo-sub-1623422%22%20slang%3D%22en-US%22%3ESPO%20list%20lookup%20value%20cannot%20be%20edited%20in%20linked%20Access%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1623422%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20in%20SharePoint%20Online%20with%20a%20lookup%20table.%20The%20list%2C%20including%20the%20lookup%20table%2C%20works%20fine%20in%20SPO.%20The%20list%20is%20also%20linked%20to%20Access%20on%20my%20desktop.%20Access%20links%20not%20only%20to%20the%20list%20containing%20the%20lookup%20field%20but%20also%20to%20the%20list%20containing%20the%20values.%20The%20values%20in%20the%20lookup%20field%20cannot%20be%20edited%20in%20Access.%20The%20Design%20View%20option%20to%20edit%20the%20values%20in%20a%20lookup%20field%20is%20not%20available%3B%20a%20help%20popup%20notes%20that%20the%20design%20of%20the%20lookup%20field%20cannot%20be%20changed%20(to%20allow%20or%20disallow%20editing)%20in%20a%20linked%20table.%20Is%20that%20the%20end%20of%20the%20story%3F%20Is%20there%20no%20way%20to%20be%20able%20to%20change%20values%20from%20the%20choices%20in%20the%20dropdown%20menu%2C%20which%20exist%20in%20Access%20as%20in%20SPO%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(Cross-filed%20in%20the%20SharePoint%20community)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1623422%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1690035%22%20slang%3D%22en-US%22%3ERe%3A%20SPO%20list%20lookup%20value%20cannot%20be%20edited%20in%20linked%20Access%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1690035%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F243160%22%20target%3D%22_blank%22%3E%40Joseph%20Nierenberg%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can't%20change%20the%20design%20of%20linked%20tables%20from%20Access%2C%20but%20you%20should%20be%20able%20to%20change%20the%20values%20in%20a%20linked%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELets%20look%20at%20an%20Access%20application%20that's%20linked%20to%20two%20SharePoint%20lists%3A%20%3CSTRONG%3EProduct%3C%2FSTRONG%3E%20and%20%3CSTRONG%3EProductGroup%3C%2FSTRONG%3E.%20ProductGroup%20is%20a%20lookup%20table%20for%20Product%20in%20SharePoint.%20In%20Access%20you%20can%20add%20a%20new%20ProductGroup%20while%20the%20linked%20table%20is%20opened%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22spo1.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F220073iBE5664A722F1A490%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22spo1.png%22%20alt%3D%22spo1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20adding%2C%20the%20new%20group%20can%20be%20selected%20from%20Product%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22spo2.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F220075iD0D6958FAA5CAB72%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22spo2.png%22%20alt%3D%22spo2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAnd%20when%20you%20selected%20the%20product%20group%20and%20go%20to%20the%20next%20record%2C%20the%20group%20description%20and%20packaging%20instruction%20are%20filled%20in%20automatically%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22spo3.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F220076i331C723EFDB1FF83%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22spo3.png%22%20alt%3D%22spo3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20also%20change%20values%20in%20de%20ProductGroup%20table.%20Lets%20say%20we%20want%20to%20change%20the%20temperature%20instruction%20for%20Ice%20cream%20and%20set%20it%20to%2020%20degrees%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22spo4.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F220080i9E335B72D72C6B28%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22spo4.png%22%20alt%3D%22spo4.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ENow%20when%20you%20enter%20a%20new%20record%20in%20Product%2C%20the%20changed%20instruction%20is%20filled%20in.%20The%20instruction%20for%20records%20that%20where%20already%20in%20Product%20are%20not%20updated.%20If%20you%20want%20these%20to%20update%20as%20well%2C%20you%20just%20reselect%20the%20product%20group%20and%20the%20changed%20instruction%20is%20filled%20in%20(when%20you%20confirm%20the%20change%2C%20for%20instance%20by%20moving%20to%20another%20record).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20opens%20up%20things%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20wishes%2C%3C%2FP%3E%3CP%3ETieme%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1720329%22%20slang%3D%22en-US%22%3ERe%3A%20SPO%20list%20lookup%20value%20cannot%20be%20edited%20in%20linked%20Access%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1720329%22%20slang%3D%22en-US%22%3ETo%20change%20the%20design%20of%20the%20multivalued%20field%2C%20set%20it's%20Lookup%20properties.%3CBR%20%2F%3EOpen%20a%20table%20in%20Design%20View.%3CBR%20%2F%3EClick%20the%20lookup%20field's%20name%20in%20the%20Field%20Name%20column.%3CBR%20%2F%3EUnder%20Field%20Properties%2C%20click%20the%20Lookup%20tab.%3CBR%20%2F%3ESet%20the%20Display%20Control%20property%20to%20Combo%20Box%20to%20see%20all%20available%20properties%20changes%20to%20reflect%20your%20choice.%3C%2FLINGO-BODY%3E
Frequent Contributor

I have a list in SharePoint Online with a lookup table. The list, including the lookup table, works fine in SPO. The list is also linked to Access on my desktop. Access links not only to the list containing the lookup field but also to the list containing the values. The values in the lookup field cannot be edited in Access. The Design View option to edit the values in a lookup field is not available; a help popup notes that the design of the lookup field cannot be changed (to allow or disallow editing) in a linked table. Is that the end of the story? Is there no way to be able to change values from the choices in the dropdown menu, which exist in Access as in SPO?

 

(Cross-filed in the SharePoint community)

2 Replies

@Joseph Nierenberg 

You can't change the design of linked tables from Access, but you should be able to change the values in a linked table.

 

Lets look at an Access application that's linked to two SharePoint lists: Product and ProductGroup. ProductGroup is a lookup table for Product in SharePoint. In Access you can add a new ProductGroup while the linked table is opened:

spo1.png

 

After adding, the new group can be selected from Product:

spo2.png

And when you selected the product group and go to the next record, the group description and packaging instruction are filled in automatically:

spo3.png

 

You can also change values in de ProductGroup table. Lets say we want to change the temperature instruction for Ice cream and set it to 20 degrees:

spo4.png

Now when you enter a new record in Product, the changed instruction is filled in. The instruction for records that where already in Product are not updated. If you want these to update as well, you just reselect the product group and the changed instruction is filled in (when you confirm the change, for instance by moving to another record).

 

Hope this opens up things for you.

 

Best wishes,

Tieme

 

 

To change the design of the multivalued field, set it's Lookup properties.
Open a table in Design View.
Click the lookup field's name in the Field Name column.
Under Field Properties, click the Lookup tab.
Set the Display Control property to Combo Box to see all available properties changes to reflect your choice.