Type Mismatch (Error 13) when populating a null field in VBA

Question

I'm trying to populate a control from an external form with a string value on button click - this is working perfectly when there is already existing text, however if there is nothing then it is treated as a null value and therefore returns a Type Mismatch error.

I thought I would be able to work around this by using:

Nz(Forms!frmSpecifications!boxScratchpad.value, "") = "Text string"

But this hasn't worked.

Could it be to do with how I am calling the control, or the properties of the control itself?

UPDATE: After looking into this a bit more I have pinpointed the issue to a SQL query execution earlier in the code which runs an update statement on the same table that boxScratchpad normally gets its data from (although the field which is fed into boxScratchpad is not actually updated). If I comment out this code so that the update statement doesn't run, boxScratchpad populates successfully. It seems like there could be a conflict between the table being updated and boxScratchpad being assigned a new value within the same code?

Answer 1

First of all, understand what you are trying to accomplish and what your code does.

Assuming your control is a textbox, the property that displays its text is value. If your control is a button, the property that displays text is caption.

You are trying to assign a string to this control value or caption, there will be no issues if those properties are currently NULL.
The mistake lies within the nz. If nz evaluates your control's property to NULL, it will return your empty string as the answer, thus ending in this situation:

"" = "Some text"

Which will throw error "It requires an object".
Remove the nz function.
To really test the interference of your SQL update with the control, use the inmediate window in vba editor to execute and test your code.