Error when clearing form

[turbo910]

Hi guys



Using access 2007

Basically i have created a form (Majority unbound) and wanted the user to be able to save the form (done) and then clear the form in order to input the new data.



I already have a clear button that clears a majority of the controls on the form using this VBA code



Private Sub Clear_form_Click()
Dim ctl As Control

On Error Resume Next

For Each ctl In Me.Controls
ctl.Value = ctl.DefaultValue
Next

Set ctl = Nothing


End Sub



This is all well except one point :



The last text box doesn't clear, instead it says #Error. The Text box is simple i just created it from the MS ribbon and entered the name of it and a simple calculation which is : =[Quantity]-[Quantity_out].



this obviously means that not all the controls are clear and new so How exactly do i over come this??



Anything else i can do please ask



Thankyou!!

[RuralGuy]

How about:

For Each ctl In Me.Controls
If ctl.Name <> "Name of the Calculated control" Then
   ctl.Value = ctl.DefaultValue
End If
Next

[turbo910]

Input this into my current code and if so where? or use this as the only code and input all my control names??

Thanks for fast reply

[RuralGuy]

Just change your current code so it incorporates what I posted; using *your* [Name of the Calculated control] of course. It is just the two lines. One that begins with IF and the other begins with END IF

[turbo910]

I input the code below the set ctl = nothing

and i get this error when i click the clear button on my form but without the code you told me to put in it works fine but i still have the error in that particular text box
the error is:

Compile error: User-defined type not defined

[RuralGuy]

Post all of the code starting with:
Private
.
. to
.
End Sub

[turbo910]

Private Sub Clear_form_Click()
Dim ctl As Control

On Error Resume Next

For Each ctl In Me.Controls
ctl.Value = ctl.DefaultValue
Next
Set ctl = Nothing

If ctl.Name <> New Quantity Then
ctl.Value = ctl.DefaultValue
End If
End Sub

When the error occurs and i debug it it highlights all of the first line: Private Sub Clear_form_Click()

[RuralGuy]

Try this code *INSTEAD* of what you have:

Private Sub Clear_form_Click()
   Dim ctl As Control
   For Each ctl In Me.Controls
      If ctl.Name <> "New Quantity" Then
         ctl.Value = ctl.DefaultValue
      End If
   Next
End Sub

[turbo910]

Nope didnt work my friend :S

[RuralGuy]

Just reread the first post. Are the [Quantity] and [Quantity_out] controls both unbound? What is their DefaultValue? How about putting 0 in them with the Clear routine?

[turbo910]

Excellent all sorted thankyou

[RuralGuy]

That's great. Do you want to follow the link in my sig and mark this thread as Solved?