Clear form ?

[SpookiePower]

I have a lot of Textboxes and comboboxes on my form.
I can clear each box in this way -

Me!nameofbox = null

But what can I do if I don't want to write this line for each box I
have on my form ?

 

[SpookiePower]

I found this code to clear the form -

Dim ctl As Control
For each ctl in me.controls
ctl.value = null
next

and it works just fine. But there is one textbox that i don't want
to be cleared.

I have tryed to fix it this way - but it dose not work.

Dim ctl As Control
For each ctl in me.controls
If ctl <> me.NameOfTheTextbox then
ctl.value = null
else
next

 

[RoyVidar]

I have a lot of Textboxes and comboboxes on my form.
I can clear each box in this way -

Me!nameofbox = null

But what can I do if I don't want to write this line for each box I
have on my form ?

If you are working with a bound form, clearing this way, would remove
the contents of the current record. If it is alredy saved, you will
probably get some errors due to trying to alter the primary key to
Null, same with controls bound to required fields etc.

So, for bound forms, I'd perhaps be more inclined to try to undo
unsaved changes, alternatively, run a delete query to delete the
current record (air code).

if me.newrecord then
Me.Undo
else
currentdb.execute "delete from mytable where PK = " & _
me!txtPK, dbfailonerror
end if

Or, just go to a new record with something like

DoCmd.GotoRecord , , acNewRec

If you're working with an unbound form, then you could probably loop
the controls collection (air code)

dim ctl as control

for each ctl in me.controls
select case ctl.controltype
case actextbox, accombobox ' other relevant control types
ctl.value = Null
end select
next ctl

set ctl = nothing

You would propably need some errorhandling, and perhaps also exclude
some controls from the handling

 

[RoyVidar]

I found this code to clear the form -

Dim ctl As Control
For each ctl in me.controls
ctl.value = null
next

and it works just fine. But there is one textbox that i don't want
to be cleared.

I have tryed to fix it this way - but it dose not work.

Dim ctl As Control
For each ctl in me.controls
If ctl <> me.NameOfTheTextbox then
ctl.value = null
else
next

Try
If ctl.Name <> "NameOfTheTextbox" then

 

[Arvin Meyer [MVP]]

Try
If ctl.Name <> "NameOfTheTextbox" then

Also, an "End If" needs to replace the "Else"

 

[Douglas J. Steele]

I wouldn't have expected your first, simpler code to work: you can't set the
value of all controls in that way (Labels, for example, don't have a Value
property, but they are in the Controls collection.)

In addition to everything else you've been told, you probably want to check
the ControlType property of the control:

For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox
If ctl.Name <> "NameOfTheTextBox" Then
ctl.Value = Null
End If
Case acComboBox, acListBox
ctl.Value = Null
Case Else
' Do nothing
End Select
Next ctl