I have an Access form bound to linked view called vwBudgetEntries from SQL Server. The form contains a combo box bound to a field called SubcodeID which is short text and has the following properties:
Meanwhile, the row source of the combo box pulls a unique list of all subcode id's, including one where it's an empty string. For some reason, however, selecting this option throws the following error:
For the life of me, I can't figure out why. The value should be empty string, not null. If I change the combo box to a textbox and enter "", the update is accepted without any errors.
I've done what research I can and, so far, I've only found 2 workarounds:
Change the field properties to allow null, then replace null values with an empty string after the control updates.
Trap the error in the form_error event, undo the update and supply the value using VBA.
Neither workaround is ideal. Can someone explain why this error is occurring in the first place and what I might do to fix it?
CodePudding user response：
Storing empty strings should be avoided for anything else than very special cases, or you will meet issues like this. So:
- Set Required to No
- Set Allow Zero Length to No
- Store Null for the choice of unknown value for
SubcodeID
CodePudding user response：
Question: Are you trying to enter data into the VIEW when you experience the error? If 'yes' - using copy/paste? or manually field by field?