Error 3101 on optional field (1 Viewer)

Andreas71

New member
Local time
Today, 13:56
Joined
Dec 1, 2021
Messages
1
I'm updating a linked SharePoint list (name: "Business Supports") through an Access Form via a query.
The SharePoint list has a lookup to another linked SharePoint list (name: "Business Capabilities") on the site and this is a combo box on the form.

When I try to add a new record without selecting the optional "Business Capability" I get the error:
Run-time error '3101':
The Microsoft Access database engine cannot find a record in the table 'Business Capabilities' with key matching field(s) 'Business Capability'.

When debugging, I can see that the field is null, which makes perfect sense to me.

If I try to update an existing record by clearing the combo box value, I get the following (before even trying to save the record):
You tried to assign the Null value to a variable that is not a Variant data type.

Since the value is optional, I need to be able to leave it blank (or null)...
Note: This works fine when I make the insert or update directly in the linked Access table!

Have I made a design error in the form? Any ideas?

UPDATE:
I found my stupid mistake 5 minutes after my (first) post: The query had an inner join instead of a left outer between the linked tables.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:56
Joined
Feb 28, 2001
Messages
27,001
Glad you found your error. But thanks even more for posting the solution, since this might help others to learn more about their use of SharePoint.
 

Chazmatoc

New member
Local time
Tomorrow, 01:56
Joined
Jul 5, 2022
Messages
1
This was exactly my problem as well. I had added a link to a table to provide a description and made the join wrong. Thanks for the fix.
 

Users who are viewing this thread

Top Bottom