Would anyone be able to tell me 1

[Syerston]

Would anyone be able to tell me why I keep getting a "type mismatch" error with the following code.

Current_Form is passed to the sub as the current form.

Public Sub Clear_Details(Current_Form As Form)
   Dim ctl As TextBox
    For Each ctl In Current_Form
        ctl.Value = ""
    Next ctl
End Sub

John

 

[jebry]

Hi John!

Each control is not a textbox so you can't set cntl to each control. Try this:

Public Sub Clear_Details(Current_Form As Form)
Dim ctl As Control
For Each ctl In Current_Form.Controls
If ctl.ControlType = acTextBox Then
ctl.Value = ""
End If
Next ctl
End Sub

Another thing to note, if your textboxes are bound you may not be able to set them to "" if the underlying field doesn't allow zero length strings.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Syerston]

Jeff,
It comes back with the error message "You cant assign a value to this object" on ctl.Value = ""

The textboxes are not bound.

On ctl.ControlType if I remove the .Controltype and retry it doesn't come back with any properties or methods. Does this have something to do with it. John

 

[jebry]

Hi!

Sorry John, try this instead:

Public Sub Clear_Details(Current_Form As Form)
Dim ctl As Control
For Each ctl In Current_Form.Controls
If ctl.ControlType = acTextBox Then
Current_Form.Controls(ctl.Name).Value = ""
End If
Next ctl
End Sub

Needed to use a more explicit reference.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Syerston]

Sorry Jeff
It now comes back with the error "can't assign a value to this object". John

 

[Syerston]

Jeff
Ignore my last reply

One of the text boxes source was a function. It works perfectly.

Many Thanks John

 

[Syerston]

Jeff

Could I ask one more thing.

How could I set it up in the For Next loop to exclude two text boxes. John