Clearing values only from text boxes in Form

[mp3909]

Hi,



I am trying to clear the values in the text boxes in my Access Form.

I am working with Access 2016 and this is the code that I am using:

Private Sub Command55_Click()

Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        ctl.Value = ""
    End If
Next ctl


End Sub

but I am getting an error saying

You can't assign value to this object

I then tried changing

ctl.Value = ""

to

ctl.Value = vbNullString

and no luck.

Any ideas?

[orange]

Tell us about the form. Is it bound to a table or query?
Here is a similar post and response.

[mp3909]

It is bound to a table

[Micron]

One or more of those controls are probably calculated. You can't assign a value to a calculated control. I would use the Tag property and base the selection on that and the type, rather than the type alone.
EDIT
Maybe I should have followed the link first...

[Missinglinq]

...You can't assign a value to a calculated control...

Same thing applies if you have an Autonumber Field that appears on the Form..

Linq ;0)>

[mp3909]

Yes, only one of my fields is an Autonumber Field.
How do I determine this in VBA i.e. is there anything like if clt.DataType = "Autonumber" then .....

This is what I am trying to achieve:

Private Sub Command55_Click()


Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        If ctl.DataType <> Autonumber Then
            ctl.Value = ""
        End If
    End If
Next ctl


End Sub

[davegri]

If any error occurs, you can do this:

Private Sub cmdClear_Click()


Dim ctl As Control
On Error Resume Next
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        ctl.Value = ""
    End If
Next ctl
On Error GoTo 0
End sub

Or trap specific errors like this:

Private Sub cmdClear_Click()
On Error GoTo cmdClear_Click_Error
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        ctl.Value = ""
    End If
Next ctl
cmdClear_Click_EXIT:
    Exit Sub
cmdClear_Click_Error:
    Select Case Err
        Case 2448   'ignore
            Resume Next
        Case Else
            MsgBox Err.Number & ", " & Err.Description
            Resume cmdClear_Click_EXIT
    End Select
End Sub

[mp3909]

Cool, thanks davegri!

[June7]

If you don't want to have to touch every control, another method is to name controls with similar names, like tbx1, tbx2, tbx3, etc. Then looping code can address only those controls:

For x = 1 to 10
Me.Controls("tbx" & x) = Null
Next

How many controls are there? If only a few, maybe just addressing each one directly would be just as efficient.

[Missinglinq]

That'd work, of course, but names like tbx1, tbx2, tbx3 isn't very descriptive, is it?

Linq ;0)>

[June7]

Just an example. I have controls named like:

tbxSetup1, tbxSetup2, tbxSetup3, etc.

and

tbxPost1, tbxPost2, tbxPost3, etc.

etc.

And yes, db violates normalization to some degree. Also, have forms to allow users to input numbers to do calcs and in some cases both raw data and calculated results are committed to record and in other situations only the calculated results are committed. Laboratory testing, results cannot be allowed to change if a procedure or formula is altered - which is extremely rare but not impossible.