VBA Access Error 2447 when adding control values from a form to a String-CodePudding

I'm new to Access and working with an Access database that was designed by someone else. It is throwing an error 2447 for a button that looks like it reads all the control boxes and adds them to a dictionary (not sure if this is the right word - I'm used to Python), then adds them to the database through a query.

This is the code for the button:

Private Sub btn_add_response_Click()

Dim argstr As String
Dim ctrl As Control

argstr = "dummy|123"
    
For Each ctrl In Me.Form
    If ctrl.ControlType = acTextBox Or ctrl.ControlType = acCheckBox Or ctrl.ControlType = acComboBox Then
        If Not ctrl.Value = "" Then
            argstr = argstr & ";" & ctrl.Name & "|" & ctrl.Value
        End If
    End If
Next ctrl

DoCmd.OpenForm "My_Form", acNormal, , , acFormAdd, acDialog, argstr
Me.My_Query.Requery

End Sub

The debugger is highlighting the line If Not ctrl.Value = "" Then

I have tried changing this to

If Not IsNull(ctrl) Or Len(ctrl) = 0 Then

However, I still got the same error, so my guess is that this line isn't necessarily the problem. I'm wondering if it could be something in the design of one of my control boxes, but I'm not completely sure what to look at.

Let me know if you have any ideas!

CodePudding user response：

A checkbox can't hold a string.

This will normally work as an empty textbox typically is Null:

If Not IsNull(ctrl.Value) Then

CodePudding user response：

I'm not completely sure what happened here, but when I opened the database today, the form no longer throws the error 2447 (instead it found another bug, but at least users can enter and save information now!). Maybe I just needed to close and re-open the application for it to work. I used @TimWilliams' code, though my guess is that @Gustav's would have worked too! Thanks everyone!